[ 
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)

Reply via email to