Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Won't Fix
-
1.14.0
-
None
-
None
Description
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
[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)
From the profile file under log/profiles directory, for TPC-DS query 95.
{"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":"-"}