drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jinfeng Ni (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-2329) TPCDS query 95 and simplified variant fail to execute
Date Thu, 25 Jun 2015 00:23:04 GMT

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

Jinfeng Ni commented on DRILL-2329:
-----------------------------------

Can you try this query on the latest master branch?

I run the query95_stream0_sf1.sql over tpcds sample dataset, and it complains the column 'd_date'
is ambiguous, which seems to be right, since Drill requires to put table qualifier in a join
query, due to the schema-less characteristics. 

{code}

0: jdbc:drill:zk=local> 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;
Jun 24, 2015 5:19:25 PM org.apache.calcite.sql.validate.SqlValidatorException <init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Column 'd_date' is ambiguous
Jun 24, 2015 5:19:25 PM org.apache.calcite.runtime.CalciteException <init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 18, column 10 to line
18, column 15: Column 'd_date' is ambiguous
Error: PARSE ERROR: From line 18, column 10 to line 18, column 15: Column 'd_date' is ambiguous

{code}



> TPCDS query 95 and simplified variant fail to execute
> -----------------------------------------------------
>
>                 Key: DRILL-2329
>                 URL: https://issues.apache.org/jira/browse/DRILL-2329
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 0.8.0
>            Reporter: Abhishek Girish
>            Assignee: Jinfeng Ni
>             Fix For: 1.1.0
>
>         Attachments: drillbit.log, query95_stream0_sf1.sql
>
>
> TPCDS query 95 (attached) fails to validate. 
> *A simplified variant of the query (may not have much semantics) that fails:*
> {code:sql}
> WITH abc AS
> (
>        SELECT *
>        FROM   web_sales)
> SELECT
>          Count(DISTINCT ws_order_number) AS a1 ,
>          Sum(ws_ext_ship_cost)           AS a2 
> FROM     web_sales ws 
> WHERE     ws.ws_ship_addr_sk = 1
> AND      ws.ws_web_site_sk = web_site_sk
> ORDER BY count(DISTINCT ws_order_number)
> LIMIT 100;
> {code}
> *Error:*
> Query failed: SqlValidatorException: Aggregate expression is illegal in ORDER BY clause
of non-aggregating SELECT



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message