[
https://issues.apache.org/jira/browse/DRILL-4469?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15183069#comment-15183069
]
Deneche A. Hakim commented on DRILL-4469:
-----------------------------------------
Looking at the query plan:
{noformat}
00-00 Screen
00-01 Project(EXPR$0=[$0])
00-02 Project(w0$o0=[$2])
00-03 Window(window#0=[window(partition {0} order by [0] range between
UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1)])])
00-04 SelectionVectorRemover
00-05 Sort(sort0=[$0], sort1=[$0], dir0=[ASC], dir1=[ASC])
00-06 Project(T1¦¦*=[$0], $1=[ITEM($0, 'c1')])
00-07 Project(T1¦¦*=[$0])
00-08 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=file:/Users/hakim/MapR/data/t_alltype.parquet]],
selectionRoot=file:/Users/hakim/MapR/data/t_alltype.parquet, numFiles=1,
usedMetadataFile=false, columns=[`*`]]])
{noformat}
Something's wrong, we are only expanding the 'c1' column and never expand 'c8
column. Also, although sort is sorting using 2 column they both reference the
same column 'T1||*'
For instance, the following query gives correct results, you will find it's
plan right below:
{noformat}
SELECT SUM(c1) OVER(PARTITION BY c8 ORDER BY c1 RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) FROM (select * from `t_alltype.parquet`);
{noformat}
{noformat}
00-00 Screen
00-01 Project(EXPR$0=[$0])
00-02 Project($0=[$2])
00-03 Window(window#0=[window(partition {1} order by [0] range between
UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($0)])])
00-04 SelectionVectorRemover
00-05 Sort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
00-06 Project($0=[ITEM($0, 'c1')], $1=[ITEM($0, 'c8')])
00-07 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=file:/Users/hakim/MapR/data/t_alltype.parquet]],
selectionRoot=file:/Users/hakim/MapR/data/t_alltype.parquet, numFiles=1,
usedMetadataFile=false, columns=[`*`]]])
{noformat}
> SUM window query returns incorrect results over integer data
> ------------------------------------------------------------
>
> Key: DRILL-4469
> URL: https://issues.apache.org/jira/browse/DRILL-4469
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.6.0
> Environment: 4 node CentOS cluster
> Reporter: Khurram Faraaz
> Priority: Critical
> Labels: window_function
> Attachments: t_alltype.csv, t_alltype.parquet
>
>
> SUM window query returns incorrect results as compared to Postgres, with or
> without the frame clause in the window definition. Note that there is a sub
> query involved and data in column c1 is sorted integer data with no nulls.
> Drill 1.6.0 commit ID: 6d5f4983
> Results from Drill 1.6.0
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT SUM(c1) OVER w FROM (select * from
> dfs.tmp.`t_alltype`) subQry WINDOW w AS (PARTITION BY c8 ORDER BY c1 RANGE
> BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
> +---------+
> | EXPR$0 |
> +---------+
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> ...
> | 10585 |
> | 10585 |
> | 10585 |
> +--------+
> 145 rows selected (0.257 seconds)
> {noformat}
> results from Postgres 9.3
> {noformat}
> postgres=# SELECT SUM(c1) OVER w FROM (select * from t_alltype) subQry WINDOW
> w AS (PARTITION BY c8 ORDER BY c1 RANGE BETWEEN UNBOUNDED PRECEDING AND
> UNBOUNDED FOLLOWING);
> sum
> ------
> 4499
> 4499
> 4499
> 4499
> 4499
> 4499
> ...
> 5613
> 5613
> 5613
> 473
> 473
> 473
> 473
> 473
> (145 rows)
> {noformat}
> Removing the frame clause from window definition, still results in completely
> different results on Postgres vs Drill
> Results from Drill 1.6.0
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT SUM(c1) OVER w FROM (select * from
> t_alltype) subQry WINDOW w AS (PARTITION BY c8 ORDER BY c1);
> +---------+
> | EXPR$0 |
> +---------+
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> ...
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> | 10585 |
> +--------+
> 145 rows selected (0.28 seconds)
> {noformat}
> Results from Postgres
> {noformat}
> postgres=# SELECT SUM(c1) OVER w FROM (select * from t_alltype) subQry WINDOW
> w AS (PARTITION BY c8 ORDER BY c1);
> sum
> ------
> 5
> 12
> 21
> 33
> 47
> 62
> 78
> 96
> 115
> 135
> 158
> 182
> 207
> 233
> 260
> 289
> ...
> 4914
> 5051
> 5189
> 5328
> 5470
> 5613
> 8
> 70
> 198
> 332
> 473
> (145 rows)
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
