Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Thu, 22 Sep 2016 11:19:20 +0000 (UTC)
From: "Khurram Faraaz (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.13006697.1474502201000.637074.1474543160432@Atlassian.JIRA>
In-Reply-To: <JIRA.13006697.1474502201000@Atlassian.JIRA>
References: <JIRA.13006697.1474502201000@Atlassian.JIRA> <JIRA.13006697.1474502201141@arcas>
Subject: [jira] [Commented] (DRILL-4900) Query across Sybase and Oracle
 plugins is dropping WHERE clause
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 22 Sep 2016 11:19:22 -0000


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