[
https://issues.apache.org/jira/browse/DRILL-3288?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14600527#comment-14600527
]
Deneche A. Hakim commented on DRILL-3288:
-----------------------------------------
I tried all queries on master and I am not seeing any error, do I need to set
some specific session options ?
{noformat}
select avg(j7.c_integer) from j6, j7 where j6.c_integer = j7.c_integer group by
j6.c_date, j6.c_time, j6.c_integer;
+----------------+
| EXPR$0 |
+----------------+
| -2.04255069E8 |
| -2.69354326E8 |
| 5.667392E7 |
| -2.36940634E8 |
| -2.05855432E8 |
| -4.9987162E8 |
| -3.31520972E8 |
| 3.4258174E8 |
| -4.20352453E8 |
| -2.3065633E8 |
| -4.91097813E8 |
| -2.4586822E8 |
| 8.857024E7 |
| -4.03538478E8 |
| -1.52837704E8 |
| -3.63086546E8 |
+----------------+
16 rows selected (2.653 seconds)
{noformat}
{noformat}
select j6.c_integer, avg(j7.c_integer), sum(j6.c_integer) over(partition by
j6.c_date order by j6.c_time) from j6, j7 where j6.c_integer = j7.c_integer
group by j6.c_date, j6.c_time, j6.c_integer;
+-------------+----------------+-------------+
| c_integer | EXPR$1 | EXPR$2 |
+-------------+----------------+-------------+
| -499871620 | -4.9987162E8 | -499871620 |
| -491097813 | -4.91097813E8 | -491097813 |
| -420352453 | -4.20352453E8 | -420352453 |
| -403538478 | -4.03538478E8 | -403538478 |
| -363086546 | -3.63086546E8 | -363086546 |
| -331520972 | -3.31520972E8 | -331520972 |
| -269354326 | -2.69354326E8 | -269354326 |
| -245868220 | -2.4586822E8 | -245868220 |
| -236940634 | -2.36940634E8 | -236940634 |
| -230656330 | -2.3065633E8 | -230656330 |
| -205855432 | -2.05855432E8 | -205855432 |
| -204255069 | -2.04255069E8 | -204255069 |
| -152837704 | -1.52837704E8 | -152837704 |
| 56673920 | 5.667392E7 | 56673920 |
| 88570240 | 8.857024E7 | 88570240 |
| 342581740 | 3.4258174E8 | 342581740 |
+-------------+----------------+-------------+
16 rows selected (1.322 seconds)
{noformat}
{noformat}
select j6.c_integer, sum(j6.c_integer) over(partition by j6.c_date order by
j6.c_time) from j6, j7 where j6.c_integer = j7.c_integer group by j6.c_date,
j6.c_time, j6.c_integer having avg(j7.c_integer) > 0;
+------------+------------+
| c_integer | EXPR$1 |
+------------+------------+
| 56673920 | 56673920 |
| 88570240 | 88570240 |
| 342581740 | 342581740 |
+------------+------------+
3 rows selected (0.81 seconds)
{noformat}
> False "Hash aggregate does not support schema changes" error message in a
> query with merge join and hash aggregation
> --------------------------------------------------------------------------------------------------------------------
>
> Key: DRILL-3288
> URL: https://issues.apache.org/jira/browse/DRILL-3288
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Relational Operators
> Affects Versions: 1.0.0
> Reporter: Victoria Markman
> Assignee: Deneche A. Hakim
> Fix For: 1.1.0
>
> Attachments: j6.parquet, j7.parquet
>
>
> This error seems to be happening only when you have both window and regular
> aggregate function in a query. You will need to disable hash join to
> reproduce this error: "alter session set `planner.enable_hashjoin` = false"
> Columns in table j6 are all of 'optional' type, columns in j7 are all
> "required" type. (attached sample for each)
> Here are two queries that are failing for me:
> Query 1 (aggregate function in the having clause):
> {code}
> 0: jdbc:drill:schema=dfs> select
> . . . . . . . . . . . . > j6.c_integer,
> . . . . . . . . . . . . > sum(j6.c_integer) over(partition by
> j6.c_date order by j6.c_time)
> . . . . . . . . . . . . > from
> . . . . . . . . . . . . > j6, j7
> . . . . . . . . . . . . > where j6.c_integer = j7.c_integer
> . . . . . . . . . . . . > group by
> . . . . . . . . . . . . > j6.c_date, j6.c_time, j6.c_integer
> . . . . . . . . . . . . > having
> . . . . . . . . . . . . > avg(j7.c_integer) > 0;
> java.lang.RuntimeException: java.sql.SQLException: UNSUPPORTED_OPERATION
> ERROR: Hash aggregate does not support schema changes
> Fragment 0:0
> [Error Id: ed0140d4-244c-4895-bf65-6ea1d085382e on atsqa4-133.qa.lab:31010]
> at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)
> at
> sqlline.TableOutputFormat$ResizingRowsProvider.next(TableOutputFormat.java:85)
> at sqlline.TableOutputFormat.print(TableOutputFormat.java:116)
> at sqlline.SqlLine.print(SqlLine.java:1583)
> at sqlline.Commands.execute(Commands.java:852)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:738)
> at sqlline.SqlLine.begin(SqlLine.java:612)
> at sqlline.SqlLine.start(SqlLine.java:366)
> at sqlline.SqlLine.main(SqlLine.java:259)
> {code}
> Query 2: (window function and aggregate function in projection list):
> {code}
> 0: jdbc:drill:schema=dfs> select
> . . . . . . . . . . . . > j6.c_integer,
> . . . . . . . . . . . . > avg(j7.c_integer),
> . . . . . . . . . . . . > sum(j6.c_integer) over(partition by
> j6.c_date order by j6.c_time)
> . . . . . . . . . . . . > from
> . . . . . . . . . . . . > j6, j7
> . . . . . . . . . . . . > where j6.c_integer = j7.c_integer
> . . . . . . . . . . . . > group by
> . . . . . . . . . . . . > j6.c_date, j6.c_time, j6.c_integer;
> java.lang.RuntimeException: java.sql.SQLException: UNSUPPORTED_OPERATION
> ERROR: Hash aggregate does not support schema changes
> Fragment 0:0
> [Error Id: 370188bd-012d-4fc2-a365-fe9e482aaa0f on atsqa4-133.qa.lab:31010]
> at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)
> at
> sqlline.TableOutputFormat$ResizingRowsProvider.next(TableOutputFormat.java:85)
> at sqlline.TableOutputFormat.print(TableOutputFormat.java:116)
> at sqlline.SqlLine.print(SqlLine.java:1583)
> at sqlline.Commands.execute(Commands.java:852)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:738)
> at sqlline.SqlLine.begin(SqlLine.java:612)
> at sqlline.SqlLine.start(SqlLine.java:366)
> at sqlline.SqlLine.main(SqlLine.java:259)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
