[
https://issues.apache.org/jira/browse/DRILL-2168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14307854#comment-14307854
]
Jinfeng Ni commented on DRILL-2168:
-----------------------------------
This seems to be related to function resolution when handling case sensitivity.
I could re-produce similarly wrong plan using simplify query:
{code}
explain plan for select substr(n_name, 1, 2), sum(n_nationkey) from
cp.`tpch/nation.parquet` group by substr(n_name, 1,2);
00-01 Project(EXPR$0=[$0], EXPR$1=[$1])
00-02 Project(EXPR$0=[substr($1, 1, 2)], EXPR$1=[$1])
00-03 HashAgg(group=[{0}], EXPR$1=[SUM($1)])
00-04 Project($f0=[SUBSTR($1, 1, 2)], n_nationkey=[$0])
00-05 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=/tpch/nation.parquet]], selectionRoot=/tpch/nation.parquet, numFiles=1,
columns=[`n_name`, `n_nationkey`]]])
{code}
Notice that the 02 PROJECT has substr, while 04 PROJECT has SUBSTR. The
correct plan should recognize the two expressions are identical, such that the
02 PROJECT should reference the output from HashAgg directly.
Need to investigate two issues : 1) why the expression in select clause and GB
clause are treated differently, 2) if the two expressions are not identically,
why validator will let such query pass?
> Wrong result on grouping by expression involving CONCAT function
> ----------------------------------------------------------------
>
> Key: DRILL-2168
> URL: https://issues.apache.org/jira/browse/DRILL-2168
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 0.8.0
> Reporter: Victoria Markman
> Assignee: Jinfeng Ni
> Priority: Critical
> Attachments: 0_0_0.parquet
>
>
> {code}
> 0: jdbc:drill:schema=dfs> select * from test;
> +------------+------------+
> | a1 | b1 |
> +------------+------------+
> | 51237400 | 2014-03-16 03:55:21.0 |
> | -691523338 | 2014-02-13 15:47:22.0 |
> | -1843395360 | 2014-01-14 03:31:27.0 |
> | 1095015454 | 2014-09-27 17:35:21.0 |
> | -48817354 | 2014-01-17 16:14:25.0 |
> | 59892266 | 2014-04-23 05:08:34.0 |
> | -827965492 | 2014-06-21 07:29:12.0 |
> | -1281245980 | 2014-10-25 15:49:46.0 |
> | -1778510302 | 2014-07-31 04:11:53.0 |
> | 1346460386 | 2014-07-31 04:11:53.0 |
> +------------+------------+
> 10 rows selected (0.072 seconds)
> 0: jdbc:drill:schema=dfs> select
> . . . . . . . . . . . . > concat(cast(extract(day from b1) as
> varchar(10)), '-', cast(extract(month from b1) as
> varchar(10)),'-',cast(extract(year from b1) as varchar(10))),
> . . . . . . . . . . . . > sum(a1)
> . . . . . . . . . . . . > from
> . . . . . . . . . . . . > test
> . . . . . . . . . . . . > group by
> . . . . . . . . . . . . > concat(cast(extract(day from b1) as
> varchar(10)), '-', cast(extract(month from b1) as
> varchar(10)),'-',cast(extract(year from b1) as varchar(10)));
> +------------+------------+
> | EXPR$0 | EXPR$1 |
> +------------+------------+
> | 1-1-1970 | 51237400 |
> | 23-12-1969 | -691523338 |
> | 10-12-1969 | -1843395360 |
> | 13-1-1970 | 1095015454 |
> | 31-12-1969 | -48817354 |
> | 1-1-1970 | 59892266 |
> | 22-12-1969 | -827965492 |
> | 17-12-1969 | -1281245980 |
> | 26-12-1969 | -432049916 |
> +------------+------------+
> 9 rows selected (0.103 seconds)
> {code}
> Query plan:
> {code}
> 00-01 Project(EXPR$0=[$0], EXPR$1=[$1])
> 00-02 Project(EXPR$0=[concat(CAST(EXTRACT(FLAG(DAY), $1)):VARCHAR(10)
> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", '-',
> CAST(EXTRACT(FLAG(MONTH), $1)):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE
> "ISO-8859-1$en_US$primary", '-', CAST(EXTRACT(FLAG(YEAR), $1)):VARCHAR(10)
> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")], EXPR$1=[$1])
> 00-03 HashAgg(group=[{0}], EXPR$1=[SUM($1)])
> 00-04 Project($f0=[CONCAT(CAST(EXTRACT(FLAG(DAY), $0)):VARCHAR(10)
> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", '-',
> CAST(EXTRACT(FLAG(MONTH), $0)):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE
> "ISO-8859-1$en_US$primary", '-', CAST(EXTRACT(FLAG(YEAR), $0)):VARCHAR(10)
> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")], a1=[$1])
> 00-05 Scan(groupscan=[ParquetGroupScan
> [entries=[ReadEntryWithPath [path=maprfs:/aggregation/test]],
> selectionRoot=/aggregation/test, numFiles=1, columns=[`b1`, `a1`]]])
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
