[
https://issues.apache.org/jira/browse/SPARK-28386?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kent Yao resolved SPARK-28386.
------------------------------
Assignee: Cheng Pan
Resolution: Fixed
Issue resolved by https://github.com/apache/spark/pull/44352
> 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
>
> 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
