[ https://issues.apache.org/jira/browse/DRILL-7348?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16947512#comment-16947512 ]
benj edited comment on DRILL-7348 at 10/9/19 10:01 AM: ------------------------------------------------------- I have tested in 1.15 and 1.16. I think thata the problem of [~snapdoodle] is that date is a reserved keyword {code:sql} SELECT date, COUNT(1) FROM ( SELECT DISTINCT id, date, status FROM (select 1 id, 'b' date, 3 status UNION SELECT 2, 'c', 4 UNION SELECT 1, 'c', 4) AS r1 ) AS r2 GROUP BY 1 Error: PARSE ERROR: Encountered "date ," {code} So you need quote around date: {code:sql} SELECT `date`, COUNT(1) FROM ( SELECT DISTINCT id, `date`, status FROM (select 1 id, 'b' `date`, 3 status UNION SELECT 2, 'c', 4 UNION SELECT 1, 'c', 4) AS r1 ) AS r2 GROUP BY 1; +-------+---------+ | date | EXPR$1 | +-------+---------+ | b | 1 | | c | 2 | +-------+---------+ {code} The result is correct here. Maybe there is also a real problem, but without the content of the file dfs.`path` it will be difficult to conclude. was (Author: benj641): I have tested in 1.15 and 1.16. I think thata the problem of [~snapdoodle] is that date is a reserved keyword {code:code} SELECT date, COUNT(1) FROM ( SELECT DISTINCT id, date, status FROM (select 1 id, 'b' date, 3 status UNION SELECT 2, 'c', 4 UNION SELECT 1, 'c', 4) AS r1 ) AS r2 GROUP BY 1 Error: PARSE ERROR: Encountered "date ," {code} So you need quote around date: {code:sql} SELECT `date`, COUNT(1) FROM ( SELECT DISTINCT id, `date`, status FROM (select 1 id, 'b' `date`, 3 status UNION SELECT 2, 'c', 4 UNION SELECT 1, 'c', 4) AS r1 ) AS r2 GROUP BY 1; +-------+---------+ | date | EXPR$1 | +-------+---------+ | b | 1 | | c | 2 | +-------+---------+ {code} The result is correct here. Maybe there is also a real problem, but without the content of the file dfs.`path` it will be difficult to conclude. > Aggregate on Subquery with Select Distinct or UNION fails to Group By > --------------------------------------------------------------------- > > Key: DRILL-7348 > URL: https://issues.apache.org/jira/browse/DRILL-7348 > Project: Apache Drill > Issue Type: Bug > Components: Functions - Drill > Affects Versions: 1.15.0 > Reporter: Keith G Yu > Priority: Major > > The following query fails to group properly. > {code:java} > SELECT date, COUNT(1) > FROM ( > SELECT DISTINCT > id, > date, > status > FROM table(dfs.`path`(type => 'text', fieldDelimiter => ',', > extractHeader => TRUE)) > ) > GROUP BY 1{code} > This also fails to group properly. > {code:java} > SELECT date, COUNT(1) > FROM ( > SELECT > id, > date, > status > FROM table(dfs.`path1`(type => 'text', fieldDelimiter => ',', > extractHeader => TRUE)) > UNION > SELECT > id, > date, > status > FROM table(dfs.`path2`(type => 'text', fieldDelimiter => ',', > extractHeader => TRUE)) > ) > GROUP BY 1 > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)