You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Leni (JIRA)" <ji...@apache.org> on 2017/02/06 12:07:41 UTC
[jira] [Commented] (DRILL-3802) Throw unsupported error for
ROLLUP/GROUPING
[ https://issues.apache.org/jira/browse/DRILL-3802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15853892#comment-15853892 ]
Leni commented on DRILL-3802:
-----------------------------
I fired a 'with rollup' hive-query from tpcds data- for which Apache drill shell complains (but hive doesn't) that the rollup operation is not supported.
The query is as follows :-
select i_item_id,
ca_country,
ca_state,
ca_county,
avg( cast(cs_quantity as decimal(12,2))) agg1,
avg( cast(cs_list_price as decimal(12,2))) agg2,
avg( cast(cs_coupon_amt as decimal(12,2))) agg3,
avg( cast(cs_sales_price as decimal(12,2))) agg4,
avg( cast(cs_net_profit as decimal(12,2))) agg5,
avg( cast(c_birth_year as decimal(12,2))) agg6,
avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7
from catalog_sales, date_dim, customer_demographics cd1, item, customer, customer_address,
customer_demographics cd2
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and
catalog_sales.cs_item_sk = item.i_item_sk and
catalog_sales.cs_bill_cdemo_sk = cd1.cd_demo_sk and
catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and
cd1.cd_gender = 'M' and
cd1.cd_education_status = 'College' and
customer.c_current_cdemo_sk = cd2.cd_demo_sk and
customer.c_current_addr_sk = customer_address.ca_address_sk and
c_birth_month in (9,5,12,4,1,10) and
d_year = 2001 and
ca_state in ('ND','WI','AL'
,'NC','OK','MS','TN')
group by i_item_id, ca_country, ca_state, ca_county with rollup
order by ca_country,
ca_state,
ca_county,
i_item_id
limit 100;
The error message that I get on firing is as follows:
Error: PARSE ERROR: Encountered "with" at line 26, column 53.
Was expecting one of:
<EOF>
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"," ...
"." ...
"[" ...
"(" ...
"NOT" ...
"IN" ...
"BETWEEN" ...
"LIKE" ...
"SIMILAR" ...
"=" ...
">" ...
"<" ...
"<=" ...
">=" ...
"<>" ...
"+" ...
"-" ...
"*" ...
"/" ...
"||" ...
"AND" ...
"OR" ...
"IS" ...
"MEMBER" ...
"SUBMULTISET" ...
"MULTISET" ...
"HAVING" ...
"WINDOW" ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
SQL Query select i_item_id,
ca_country,
ca_state,
ca_county,
avg( cast(cs_quantity as decimal(12,2))) agg1,
avg( cast(cs_list_price as decimal(12,2))) agg2,
avg( cast(cs_coupon_amt as decimal(12,2))) agg3,
avg( cast(cs_sales_price as decimal(12,2))) agg4,
avg( cast(cs_net_profit as decimal(12,2))) agg5,
avg( cast(c_birth_year as decimal(12,2))) agg6,
avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7
from catalog_sales, date_dim, customer_demographics cd1, item, customer, customer_address,
customer_demographics cd2
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and
catalog_sales.cs_item_sk = item.i_item_sk and
catalog_sales.cs_bill_cdemo_sk = cd1.cd_demo_sk and
catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and
cd1.cd_gender = 'M' and
cd1.cd_education_status = 'College' and
customer.c_current_cdemo_sk = cd2.cd_demo_sk and
customer.c_current_addr_sk = customer_address.ca_address_sk and
c_birth_month in (9,5,12,4,1,10) and
d_year = 2001 and
ca_state in ('ND','WI','AL'
,'NC','OK','MS','TN')
group by i_item_id, ca_country, ca_state, ca_county with rollup
^
order by ca_country,
ca_state,
ca_county,
i_item_id
limit 100
[Error Id: 016389e1-0c86-430c-ace0-8c656d009ec5 on sandbox.hortonworks.com:31010] (state=,code=0)
0: jdbc:drill:>
What do I need to do? Is 'With Rollup' keyword supported?
> Throw unsupported error for ROLLUP/GROUPING
> -------------------------------------------
>
> Key: DRILL-3802
> URL: https://issues.apache.org/jira/browse/DRILL-3802
> Project: Apache Drill
> Issue Type: New Feature
> Components: Query Planning & Optimization
> Reporter: Victoria Markman
> Assignee: Jinfeng Ni
> Fix For: 1.3.0
>
>
> I believe that this is the cause of assertions in TPCDS #36, #67
> {code}
> SELECT Sum(ss_net_profit) / Sum(ss_ext_sales_price) AS
> gross_margin,
> i_category,
> i_class,
> Grouping(i_category) + Grouping(i_class) AS
> lochierarchy,
> Rank()
> OVER (
> partition BY Grouping(i_category)+Grouping(i_class), CASE
> WHEN Grouping(
> i_class) = 0 THEN i_category END
> ORDER BY Sum(ss_net_profit)/Sum(ss_ext_sales_price) ASC) AS
> rank_within_parent
> FROM store_sales,
> date_dim d1,
> item,
> store
> WHERE d1.d_year = 2000
> AND d1.d_date_sk = ss_sold_date_sk
> AND i_item_sk = ss_item_sk
> AND s_store_sk = ss_store_sk
> AND s_state IN ( 'TN', 'TN', 'TN', 'TN',
> 'TN', 'TN', 'TN', 'TN' )
> GROUP BY rollup( i_category, i_class )
> ORDER BY lochierarchy DESC,
> CASE
> WHEN lochierarchy = 0 THEN i_category
> END,
> rank_within_parent
> LIMIT 100;
> Error: SYSTEM ERROR: AssertionError: Internal error: invariant violated: conversion result not null
> [Error Id: 6afae7ce-c426-44f3-a600-aa34ab7632a1 on ucs-node5.perf.lab:31010] (state=,code=0)
> java.sql.SQLException: SYSTEM ERROR: AssertionError: Internal error: invariant violated: conversion result not null
> [Error Id: 6afae7ce-c426-44f3-a600-aa34ab7632a1 on ucs-node5.perf.lab:31010]
> at org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:247)
> at org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:290)
> at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1359)
> at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:74)
> at net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:404)
> at net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(AvaticaStatement.java:351)
> at net.hydromatic.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:338)
> at net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
> at org.apache.drill.jdbc.impl.DrillStatementImpl.execute(DrillStatementImpl.java:86)
> at sqlline.Commands.execute(Commands.java:841)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:738)
> at sqlline.SqlLine.runCommands(SqlLine.java:1641)
> at sqlline.Commands.run(Commands.java:1304)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:36)
> at sqlline.SqlLine.dispatch(SqlLine.java:734)
> at sqlline.SqlLine.initArgs(SqlLine.java:544)
> at sqlline.SqlLine.begin(SqlLine.java:587)
> at sqlline.SqlLine.start(SqlLine.java:366)
> at sqlline.SqlLine.main(SqlLine.java:259)
> Caused by: org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: AssertionError: Internal error: invariant violated: conversion result not null
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)