drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Victoria Markman (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-2168) Wrong result on grouping by expression involving CONCAT function
Date Thu, 05 Feb 2015 00:47:35 GMT

    [ https://issues.apache.org/jira/browse/DRILL-2168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14306338#comment-14306338
] 

Victoria Markman commented on DRILL-2168:
-----------------------------------------

{code}
0: jdbc:drill:schema=dfs> select b1 from test;
+------------+
|     b1     |
+------------+
| 2014-03-16 03:55:21.0 |
| 2014-02-13 15:47:22.0 |
| 2014-01-14 03:31:27.0 |
| 2014-09-27 17:35:21.0 |
| 2014-01-17 16:14:25.0 |
| 2014-04-23 05:08:34.0 |
| 2014-06-21 07:29:12.0 |
| 2014-10-25 15:49:46.0 |
| 2014-07-31 04:11:53.0 |
| 2014-07-31 04:11:53.0 |
+------------+
10 rows selected (0.05 seconds)

0: jdbc:drill:schema=dfs> select cast(extract(day from b1) as varchar(10)) from test;
+------------+
|   EXPR$0   |
+------------+
| 16         |
| 13         |
| 14         |
| 27         |
| 17         |
| 23         |
| 21         |
| 25         |
| 31         |
| 31         |
+------------+
10 rows selected (0.058 seconds)

0: jdbc:drill:schema=dfs> select cast(extract(month from b1) as varchar(10)) from test;
+------------+
|   EXPR$0   |
+------------+
| 3          |
| 2          |
| 1          |
| 9          |
| 1          |
| 4          |
| 6          |
| 10         |
| 7          |
| 7          |
+------------+
10 rows selected (0.075 seconds)

0: jdbc:drill:schema=dfs> select cast(extract(year from b1) as varchar(10)) from test;
+------------+
|   EXPR$0   |
+------------+
| 2014       |
| 2014       |
| 2014       |
| 2014       |
| 2014       |
| 2014       |
| 2014       |
| 2014       |
| 2014       |
| 2014       |
+------------+
10 rows selected (0.081 seconds)

0: jdbc:drill:schema=dfs> select concat(cast(extract(day from b1) as varchar(10)), '-',
cast(extract(month from b1) as varchar(10))) from test;
+------------+
|   EXPR$0   |
+------------+
| 16-3       |
| 13-2       |
| 14-1       |
| 27-9       |
| 17-1       |
| 23-4       |
| 21-6       |
| 25-10      |
| 31-7       |
| 31-7       |
+------------+
10 rows selected (0.088 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)))
from test;
+------------+
|   EXPR$0   |
+------------+
| 16-3-2014  |
| 13-2-2014  |
| 14-1-2014  |
| 27-9-2014  |
| 17-1-2014  |
| 23-4-2014  |
| 21-6-2014  |
| 25-10-2014 |
| 31-7-2014  |
| 31-7-2014  |
+------------+
10 rows selected (0.063 seconds)

{code}

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

Mime
View raw message