[ https://issues.apache.org/jira/browse/SPARK-28386?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kent Yao updated SPARK-28386: ----------------------------- Fix Version/s: 4.0.0 > Cannot resolve ORDER BY columns with GROUP BY and HAVING > -------------------------------------------------------- > > Key: SPARK-28386 > URL: https://issues.apache.org/jira/browse/SPARK-28386 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 3.0.0 > Reporter: Yuming Wang > Assignee: Cheng Pan > Priority: Major > Labels: pull-request-available > Fix For: 4.0.0 > > > How to reproduce: > {code:sql} > CREATE TABLE test_having (a int, b int, c string, d string) USING parquet; > INSERT INTO test_having VALUES (0, 1, 'XXXX', 'A'); > INSERT INTO test_having VALUES (1, 2, 'AAAA', 'b'); > INSERT INTO test_having VALUES (2, 2, 'AAAA', 'c'); > INSERT INTO test_having VALUES (3, 3, 'BBBB', 'D'); > INSERT INTO test_having VALUES (4, 3, 'BBBB', 'e'); > INSERT INTO test_having VALUES (5, 3, 'bbbb', 'F'); > INSERT INTO test_having VALUES (6, 4, 'cccc', 'g'); > INSERT INTO test_having VALUES (7, 4, 'cccc', 'h'); > INSERT INTO test_having VALUES (8, 4, 'CCCC', 'I'); > INSERT INTO test_having VALUES (9, 4, 'CCCC', 'j'); > SELECT lower(c), count(c) FROM test_having > GROUP BY lower(c) HAVING count(*) > 2 > ORDER BY lower(c); > {code} > {noformat} > spark-sql> SELECT lower(c), count(c) FROM test_having > > GROUP BY lower(c) HAVING count(*) > 2 > > ORDER BY lower(c); > Error in query: cannot resolve '`c`' given input columns: [lower(c), > count(c)]; line 3 pos 19; > 'Sort ['lower('c) ASC NULLS FIRST], true > +- Project [lower(c)#158, count(c)#159L] > +- Filter (count(1)#161L > cast(2 as bigint)) > +- Aggregate [lower(c#7)], [lower(c#7) AS lower(c)#158, count(c#7) AS > count(c)#159L, count(1) AS count(1)#161L] > +- SubqueryAlias test_having > +- Relation[a#5,b#6,c#7,d#8] parquet > {noformat} > But it works when setting an alias: > {noformat} > spark-sql> SELECT lower(c) withAias, count(c) FROM test_having > > GROUP BY lower(c) HAVING count(*) > 2 > > ORDER BY withAias; > bbbb 3 > cccc 4 > {noformat} -- This message was sent by Atlassian Jira (v8.20.10#820010) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org