[SPARK-28386] Cannot resolve ORDER BY columns with GROUP BY and HAVING

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Cheng Pan
• Reporter: Yuming Wang
• Created: 14/Jul/19 15:04
• Updated: 20/Dec/23 08:49
• Resolved: 20/Dec/23 08:49

Description

How to reproduce:

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

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

But it works when setting an alias:

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

Issue Links

• links to: GitHub Pull Request #44352