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