[
https://issues.apache.org/jira/browse/DRILL-3783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14746667#comment-14746667
]
Jason Altekruse commented on DRILL-3783:
----------------------------------------
Shouldn't the parenthesis that surround the sub-query with the union all in it
have absolute top precedence?
I tried playing around with mysql, and the behavior is consistent with what is
described in this report as the expected behavior. It does confirm what you
said about the aggregate applying before the union all, in the case of no
parenthesis. Seems to be doing implicit casting on the result of the
aggregation to allow it to co-exist with the varchar values that are introduced
in the union all.
{code}
mysql> select * from users;
+----+-----------+----------+-------+---------------------+
| id | firstname | lastname | email | reg_date |
+----+-----------+----------+-------+---------------------+
| 1 | john | smith | NULL | 2015-09-15 18:51:06 |
| 2 | john | doe | NULL | 2015-09-15 18:51:06 |
| 3 | bill | williams | NULL | 2015-09-15 18:51:06 |
+----+-----------+----------+-------+---------------------+
3 rows in set (0.00 sec)
mysql> select count(firstname) from (select firstname from users union all
select firstname from users) t;
+------------------+
| count(firstname) |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
mysql> select count(firstname) from users union all select firstname from users
t;
+------------------+
| count(firstname) |
+------------------+
| 3 |
| john |
| john |
| bill |
+------------------+
4 rows in set (0.00 sec)
{code}
> Incorrect results : COUNT(<column-name>) over results returned by UNION ALL
> ----------------------------------------------------------------------------
>
> Key: DRILL-3783
> URL: https://issues.apache.org/jira/browse/DRILL-3783
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Relational Operators
> Affects Versions: 1.2.0
> Environment: 4 node cluster on CentOS
> Reporter: Khurram Faraaz
> Assignee: Sean Hsuan-Yi Chu
> Priority: Critical
> Fix For: 1.2.0
>
>
> Count over results returned union all query, returns incorrect results. The
> below query returned an Exception (please se DRILL-2637) that JIRA was marked
> as fixed, however the query returns incorrect results.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select count(c1) from (select cast(columns[0]
> as int) c1 from `testWindow.csv`) union all (select cast(columns[0] as int)
> c2 from `testWindow.csv`);
> +---------+
> | EXPR$0 |
> +---------+
> | 11 |
> | 100 |
> | 10 |
> | 2 |
> | 50 |
> | 55 |
> | 67 |
> | 113 |
> | 119 |
> | 89 |
> | 57 |
> | 61 |
> +---------+
> 12 rows selected (0.753 seconds)
> {code}
> Results returned by the query on LHS and RHS of Union all operator are
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select cast(columns[0] as int) c1 from
> `testWindow.csv`;
> +------+
> | c1 |
> +------+
> | 100 |
> | 10 |
> | 2 |
> | 50 |
> | 55 |
> | 67 |
> | 113 |
> | 119 |
> | 89 |
> | 57 |
> | 61 |
> +------+
> 11 rows selected (0.197 seconds)
> 0: jdbc:drill:schema=dfs.tmp> select cast(columns[0] as int) c2 from
> `testWindow.csv`;
> +------+
> | c2 |
> +------+
> | 100 |
> | 10 |
> | 2 |
> | 50 |
> | 55 |
> | 67 |
> | 113 |
> | 119 |
> | 89 |
> | 57 |
> | 61 |
> +------+
> 11 rows selected (0.173 seconds)
> {code}
> Note that enclosing the queries within correct parentheses returns correct
> results. We do not want to return incorrect results to user when the
> parentheses are missing.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select count(c1) from ((select cast(columns[0]
> as int) c1 from `testWindow.csv`) union all (select cast(columns[0] as int)
> c2 from `testWindow.csv`));
> +---------+
> | EXPR$0 |
> +---------+
> | 22 |
> +---------+
> 1 row selected (0.234 seconds)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
