[jira] [Commented] (DRILL-6330) TPC-DS query 95 failed to plan

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-6330?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16441145#comment-16441145 ] 

Khurram Faraaz commented on DRILL-6330:
---------------------------------------

[~vvysotskyi] thanks for the details. Changing the month to use values in [01,...12] in the date value fixed the problem. I have updated TPC-DS query 95.

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