HAVING not working correctly between scopes #26915
Description
A query using HAVING fails when used from an outer scope.
The following query demonstrates the failure
WITH T as (
-- inner scope
SELECT a, b, sum(c) AS "sum"
FROM (VALUES (1, 2, 3)) t(a, b, c)
GROUP BY a, b
)
-- outer scope
SELECT a, b, T."sum"
FROM T
HAVING (a IS NOT NULL)
Expected Behavior
The query returns columns "A", "B", "sum" with one row with values 1, 2, 3, respectively.
Actual Behavior
line 15:3: 'a' must be an aggregate expression or appear in GROUP BY clause
Here's a patch for a reproducing test.
diff --git a/core/trino-main/src/test/java/io/trino/sql/analyzer/TestAnalyzer.java b/core/trino-main/src/test/java/io/trino/sql/analyzer/TestAnalyzer.java
index dedb7341b97..cf6e289b954 100644
--- a/core/trino-main/src/test/java/io/trino/sql/analyzer/TestAnalyzer.java
+++ b/core/trino-main/src/test/java/io/trino/sql/analyzer/TestAnalyzer.java
@@ -435,6 +435,22 @@ public class TestAnalyzer
.hasMessage("line 1:8: Column 't.a' cannot be resolved");
}
+ @Test
+ public void testAggregateExpressionInOtherScope() {
+ analyze("WITH T as (\n" +
+ " -- inner scope\n" +
+ " SELECT a, b, sum(c) AS \"sum\"\n" +
+ " FROM (VALUES (1, 2, 3)) t(a, b, c)\n" +
+ " GROUP BY a, b\n" +
+ ")\n" +
+ "\n" +
+ "-- outer scope\n" +
+ "SELECT a, b, T.\"sum\"\n" +
+ "FROM T\n" +
+ "HAVING (a IS NOT NULL) ");
+ }
+