[ https://issues.apache.org/jira/browse/DRILL-6330?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi resolved DRILL-6330. ---------------------------------------- Resolution: Won't Fix According to the SQL spec. 6.1 <data type> valid values for the month is [01, 12]. Therefore to avoid this issue month in date literals should be changed according to the requirements of SQL spec: '2000-04-01' or Drill UDF should be used: to_date('2000-4-01', 'yyyy-M-dd') or sql_to_date('2000-4-02', 'yyyy-mm-dd'). > 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 > Priority: Major > > 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)