[
https://issues.apache.org/jira/browse/DRILL-4900?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15513005#comment-15513005
]
Khurram Faraaz commented on DRILL-4900:
---------------------------------------
Tried a similar query on views created over Parquet data on Drill 1.9.0, we do
see the FILTER in the query plan.
{noformat}
0: jdbc:drill:schema=dfs.tmp> explain plan for
. . . . . . . . . . . . . . > SELECT a.col_vchar_52, b.col_vchar_52, a.col_int,
b.col_int, a.col_bigint, b.col_bigint, a.col_dt
. . . . . . . . . . . . . . > FROM
. . . . . . . . . . . . . . > ( SELECT * FROM `vwOnParq_10.view.drill`
. . . . . . . . . . . . . . > WHERE col_char_2 = 'MA'
. . . . . . . . . . . . . . > AND col_booln = true
. . . . . . . . . . . . . . > AND col_dt='1954-07-01'
. . . . . . . . . . . . . . > ) a,
. . . . . . . . . . . . . . > ( SELECT * FROM `vwOnParq_11.view.drill`
. . . . . . . . . . . . . . > WHERE col_char_2 = 'MA'
. . . . . . . . . . . . . . > AND col_booln IN (true,false)
. . . . . . . . . . . . . . > ) b
. . . . . . . . . . . . . . > WHERE a.col_int = b.col_int
. . . . . . . . . . . . . . > AND a.col_bigint = b.col_bigint;
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(col_vchar_52=[$0], col_vchar_520=[$1], col_int=[$2],
col_int0=[$3], col_bigint=[$4], col_bigint0=[$5], col_dt=[$6])
00-02 Project(col_vchar_52=[$2], col_vchar_520=[$6], col_int=[$0],
col_int0=[$4], col_bigint=[$1], col_bigint0=[$5], col_dt=[$3])
00-03 Project(col_int=[$3], col_bigint=[$4], col_vchar_52=[$5],
col_dt=[$6], col_int0=[$0], col_bigint0=[$1], col_vchar_520=[$2])
00-04 HashJoin(condition=[AND(=($3, $0), =($4, $1))],
joinType=[inner])
00-06 Project(col_int=[$0], col_bigint=[$1], col_vchar_52=[$3])
00-08 SelectionVectorRemover
00-10 Filter(condition=[AND(=($2, 'MA'), OR(=($4, true), =($4,
false)))])
00-12 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath [path=maprfs:///tmp/forViewCrn.parquet]],
selectionRoot=maprfs:/tmp/forViewCrn.parquet, numFiles=1,
usedMetadataFile=false, columns=[`col_int`, `col_bigint`, `col_char_2`,
`col_vchar_52`, `col_booln`]]])
00-05 Project(col_int0=[$0], col_bigint0=[$1], col_vchar_520=[$2],
col_dt=[$3])
00-07 Project(col_int=[$0], col_bigint=[$1], col_vchar_52=[$3],
col_dt=[$4])
00-09 SelectionVectorRemover
00-11 Filter(condition=[AND(=($2, 'MA'), =($5, true), =($4,
'1954-07-01'))])
00-13 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath [path=maprfs:///tmp/forViewCrn.parquet]],
selectionRoot=maprfs:/tmp/forViewCrn.parquet, numFiles=1,
usedMetadataFile=false, columns=[`col_int`, `col_bigint`, `col_char_2`,
`col_vchar_52`, `col_dt`, `col_booln`]]])
{noformat}
> Query across Sybase and Oracle plugins is dropping WHERE clause
> ---------------------------------------------------------------
>
> Key: DRILL-4900
> URL: https://issues.apache.org/jira/browse/DRILL-4900
> Project: Apache Drill
> Issue Type: Bug
> Components: Client - JDBC, Storage - JDBC
> Affects Versions: 1.6.0
> Environment: Windows client. Sybase and Oracle hosts on unix
> Reporter: Robert DeVito
>
> Have tried several approaches of joining simple queries with Oracle and
> Sybase. In all cases, we have sufficient WHERE clause on each side to really
> limit data. Each time, the Drill execution plan skips the WHERE clause on one
> side.
> ex:
> select a.f, b.b
> from
> (
> select * from pl1.`owner`.`dbo`.`VIEW1` d
> where d.fid = '0000300769'
> and d.PDate = ('2013-10-31')
> ) a,
> (
> select * from pl2.owner.VIEW2 v
> where v.f = '0000300769'
> and v.d = 'M'
> and v.b IN
> ('UK221','UK222','UK223','UK224','UK225','UK227','08843','BU5552','BU5543','BU5544')
> and v.dk = '20131031'
> ) b
> where a.f = b.f
> and a.S = b.S
> Please ignore the obfuscated column names. Syntax is valid, but Drill keeps
> sending selects with no WHERE clause for one subquery or the other. Can't
> understand why, or how to control it. This is a make or break for us.
> Thanks
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
