Khurram Faraaz created DRILL-6330: ------------------------------------- Summary: TPC-DS query 95 failed to plan Key: DRILL-6330 URL: https://issues.apache.org/jira/browse/DRILL-6330 Project: Apache Drill Issue Type: Bug Components: Query Planning & Optimization Affects Versions: 1.14.0 Reporter: Khurram Faraaz
TPC-DS query 95 planned and executed successfully on Drill 1.10.0 (git.commit id : bbcf4b76), it fails to plan on 1.14.0 master on 4 node cluster, SF1 parquet data. TPC-DS query 95 fails to plan on Drill 1.14.0 git.commit.id.abbrev=da24113 git.branch=master {noformat} [root@qa102-45 bin]# ./sqlline -u "jdbc:drill:schema=dfs.tpcds_sf1_parquet_views;drillbit=<ip-address>" -f ~/tpcds_drill_1_14_0/query95.sql 1/2 WITH ws_wh AS ( SELECT ws1.ws_order_number, ws1.ws_warehouse_sk wh1, ws2.ws_warehouse_sk wh2 FROM web_sales ws1, web_sales ws2 WHERE ws1.ws_order_number = ws2.ws_order_number AND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) SELECT Count(DISTINCT ws_order_number) AS `order count` , Sum(ws_ext_ship_cost) AS `total shipping cost` , Sum(ws_net_profit) AS `total net profit` FROM web_sales ws1 , date_dim , customer_address , web_site WHERE d_date BETWEEN '2000-4-01' AND ( Cast('2000-4-01' AS DATE) + INTERVAL '60' day) AND ws1.ws_ship_date_sk = d_date_sk AND ws1.ws_ship_addr_sk = ca_address_sk AND ca_state = 'IN' AND ws1.ws_web_site_sk = web_site_sk AND web_company_name = 'pri' AND ws1.ws_order_number IN ( SELECT ws_order_number FROM ws_wh) AND ws1.ws_order_number IN ( SELECT wr_order_number FROM web_returns, ws_wh WHERE wr_order_number = ws_wh.ws_order_number) ORDER BY count(DISTINCT ws_order_number) LIMIT 100; Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join [Error Id: d2ec3faf-1253-492e-bc56-80689e62335c on qa102-45.qa.lab:31010] (org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException) This query cannot be planned possibly due to either a cartesian join or an inequality join org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():450 org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():179 org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():146 org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():84 org.apache.drill.exec.work.foreman.Foreman.runSQL():567 org.apache.drill.exec.work.foreman.Foreman.run():264 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 (state=,code=0) {noformat} >From the profile file under log/profiles directory, for TPC-DS query 95. {noformat} {"id":\{"part1":2679297541583898541,"part2":5048058602276109542},"type":1,"start":1523661076935,"end":1523661077389,"query":"WITH ws_wh AS \n( \nSELECT ws1.ws_order_number, \nws1.ws_warehouse_sk wh1, \nws2.ws_warehouse_sk wh2 \nFROM web_sales ws1, \nweb_sales ws2 \nWHERE ws1.ws_order_number = ws2.ws_order_number \nAND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) \nSELECT \nCount(DISTINCT ws_order_number) AS `order count` , \nSum(ws_ext_ship_cost) AS `total shipping cost` , \nSum(ws_net_profit) AS `total net profit` \nFROM web_sales ws1 , \ndate_dim , \ncustomer_address , \nweb_site \nWHERE d_date BETWEEN '2000-4-01' AND ( \nCast('2000-4-01' AS DATE) + INTERVAL '60' day) \nAND ws1.ws_ship_date_sk = d_date_sk \nAND ws1.ws_ship_addr_sk = ca_address_sk \nAND ca_state = 'IN' \nAND ws1.ws_web_site_sk = web_site_sk \nAND web_company_name = 'pri' \nAND ws1.ws_order_number IN \n( \nSELECT ws_order_number \nFROM ws_wh) \nAND ws1.ws_order_number IN \n( \nSELECT wr_order_number \nFROM web_returns, \nws_wh \nWHERE wr_order_number = ws_wh.ws_order_number) \nORDER BY count(DISTINCT ws_order_number) \nLIMIT 100","foreman":\{"address":"qa102-45.qa.lab","userPort":31010,"controlPort":31011,"dataPort":31012,"version":"1.14.0-SNAPSHOT","state":0},"state":4,"totalFragments":0,"finishedFragments":0,"user":"anonymous","error":"UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join\n\n","verboseError":"UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join\n\n\n\n (org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException) This query cannot be planned possibly due to either a cartesian join or an inequality join\n org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel():450\n org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():179\n org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():146\n org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():84\n org.apache.drill.exec.work.foreman.Foreman.runSQL():567\n org.apache.drill.exec.work.foreman.Foreman.run():264\n java.util.concurrent.ThreadPoolExecutor.runWorker():1149\n java.util.concurrent.ThreadPoolExecutor$Worker.run():624\n java.lang.Thread.run():748\n","errorId":"d2ec3faf-1253-492e-bc56-80689e62335c","errorNode":"qa102-45.qa.lab:31010","optionsJson":"[ ]","planEnd":1523661077363,"queueWaitEnd":0,"totalCost":0.0,"queueName":"-"} {noformat} -- This message was sent by Atlassian JIRA (v7.6.3#76005)