Return-Path: X-Original-To: apmail-drill-issues-archive@minotaur.apache.org Delivered-To: apmail-drill-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 11D6B18C4D for ; Thu, 2 Jul 2015 18:50:05 +0000 (UTC) Received: (qmail 41813 invoked by uid 500); 2 Jul 2015 18:50:05 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 41781 invoked by uid 500); 2 Jul 2015 18:50:05 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 41771 invoked by uid 99); 2 Jul 2015 18:50:04 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Jul 2015 18:50:04 +0000 Date: Thu, 2 Jul 2015 18:50:04 +0000 (UTC) From: "Khurram Faraaz (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DRILL-2694) Correlated subquery can not be planned MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DRILL-2694?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14612363#comment-14612363 ] Khurram Faraaz commented on DRILL-2694: --------------------------------------- Verified on 1.1, query now returns results. {code} 0: jdbc:drill:schema=dfs.tmp> select * from `allTypData.csv` t1 where t1.columns[0] > (select min(columns[0]) from `allTypData2.csv` t2); +---------+ | columns | +---------+ | ["-1321857156","53940112184584704","YYPeVnzH","owR4tJgu Jd5NWDtjqUsbztoRdTEFRgv9FNmaEzCgpLqdrB9PPRh5ZtKbRolT9R2JEBzfPZ66f5jzWluOV0FdCwTO7GciY5GT2M99bgPbj8VddSMSGIFImkoVge DjTB1kWkF0CCbjtZa2oNxhwMxhKiu8f oamFOuaRVGY71oGcNCNsuudv4WxLqMKAjFxoCorMAijK","2015-03-23 18:59:30.912","0.564573254248","1997-06-22","1","1195232915.19"] | ... | ["-620911131","75259372302106128","KTPq34pP","O 1mTco8WNtgea1SuA7sUTkROeloTbcyxPbhpPrL8lXli6lpysPLGZeT4InsMm0k9I6rxBIaFPiAOREHFPS1CNeb0V0shADaoLjC9lqI50lsnJOupAoIzz42KZU4TFbiqFz 60PqpxUmgXjrAe 6xkmaO0BWHnJF1dWvU0hyhL0bb6X44619rfroDzOLQ2buXCwF hD4","2015-03-23 19:00:49.037","0.913177858825","1952-05-04","1","1152725754.0"] | +---------+ 294 rows selected (1.176 seconds) 0: jdbc:drill:schema=dfs.tmp> {code} > Correlated subquery can not be planned > -------------------------------------- > > Key: DRILL-2694 > URL: https://issues.apache.org/jira/browse/DRILL-2694 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 0.9.0 > Environment: 9d92b8e319f2d46e8659d903d355450e15946533 | DRILL-2580: Exit early from HashJoinBatch if build side is empty | 26.03.2015 @ 16:13:53 EDT > Reporter: Khurram Faraaz > Assignee: Khurram Faraaz > Fix For: 1.2.0 > > > Correlated subquery can not be planned. Test was run on 4 node cluster on CentOS. Please note that Physical plan tab and Visualized plan tabs were empty for the below query. > {code} > 0: jdbc:drill:> select * from `allTypData.csv` t1 where t1.columns[0] > (select min(columns[0]) from `allTypData2.csv` t2); > Query failed: UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality join > Error: exception while executing query: Failure while executing query. (state=,code=0) > Table in outer query has 295 rows. > 0: jdbc:drill:> select count(*) from `allTypData.csv`; > +------------+ > | EXPR$0 | > +------------+ > | 295 | > +------------+ > 1 row selected (0.082 seconds) > Table in inner query has 999 rows. > 0: jdbc:drill:> select count(*) from `allTypData2.csv`; > +------------+ > | EXPR$0 | > +------------+ > | 999 | > +------------+ > 1 row selected (0.083 seconds) > sub query is, > 0: jdbc:drill:> select min(columns[0]) from `allTypData2.csv`; > +------------+ > | EXPR$0 | > +------------+ > | -1000041497 | > +------------+ > 1 row selected (0.097 seconds) > Note that when we replace the sub-query with the value that the sub-query returns, the original query returns results. It fails only when there is correlated subquery. > select * from `allTypData.csv` t1 where t1.columns[0] > -1000041497; > +------------+ > | columns | > +------------+ > ... > +------------+ > 214 rows selected (0.162 seconds) > 0: jdbc:drill:> explain plan for select * from `allTypData.csv` t1 where t1.columns[0] > (select min(columns[0]) from `allTypData2.csv` t2); > Query failed: UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality join > Error: exception while executing query: Failure while executing query. (state=,code=0) > {code} > Stack trace from drillbit.log > {code} > 2015-04-04 21:12:40,687 [2adfac37-037b-c692-31b1-41d8004d9b13:foreman] INFO o.a.drill.exec.work.foreman.Foreman - State change requested. PENDING --> FAILED > org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality join > at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToDrel(DefaultSqlHandler.java:217) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:138) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:145) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:773) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:204) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_75] > at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_75] > at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75] > 2015-04-04 21:12:40,695 [2adfac37-037b-c692-31b1-41d8004d9b13:foreman] INFO o.a.drill.exec.work.foreman.Foreman - foreman cleaning up - status: [] > 2015-04-04 21:12:40,696 [2adfac37-037b-c692-31b1-41d8004d9b13:foreman] ERROR o.a.drill.exec.work.foreman.Foreman - Error 59dcbb50-d418-400a-9c0c-7331fcb6b344: UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality join > org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality join > at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToDrel(DefaultSqlHandler.java:217) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:138) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:145) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:773) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:204) ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_75] > at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_75] > at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75] > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)