mihaibudiu commented on code in PR #5052:
URL: https://github.com/apache/calcite/pull/5052#discussion_r3483058883
##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4301,4 +4301,29 @@ GROUP BY GROUPING SETS ((deptno), ());
!ok
+# [CALCITE-6104] Aggregate function that references outer column should be
evaluated in outer query
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa
+FROM aa;
++----+
+| SA |
++----+
+| 6 |
++----+
+(1 row)
+
+!ok
+
+SELECT (SELECT sum(sal) FROM dept) AS sum_sal
Review Comment:
The blog post quoted by @julianhyde has more examples, please add all of the
relevant ones
##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4301,4 +4301,29 @@ GROUP BY GROUPING SETS ((deptno), ());
!ok
+# [CALCITE-6104] Aggregate function that references outer column should be
evaluated in outer query
Review Comment:
Please add a comment that these are validated using an independent database.
##########
core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java:
##########
@@ -5658,6 +5665,251 @@ protected RelDataType validateSelectList(final
SqlNodeList selectItems,
return typeFactory.createStructType(fieldList);
}
+ /**
+ * Rewrites scalar sub-queries in the SELECT list whose single select item is
+ * an aggregate function whose arguments reference only outer columns. Per
the
+ * SQL standard, such aggregates belong to the outer query.
+ *
+ * <p>For example,
+ * <blockquote><pre>SELECT (SELECT sum(a) FROM t LIMIT 1) FROM
aa</pre></blockquote>
+ * is rewritten to
+ * <blockquote><pre>SELECT sum(a) * (SELECT 1 FROM t LIMIT 1) FROM
aa</pre></blockquote>
Review Comment:
From this example it's not obvious to me at all what the general rewrite
rule is.
Can you describe the algorithm?
##########
core/src/test/resources/sql/agg.iq:
##########
@@ -4301,4 +4301,29 @@ GROUP BY GROUPING SETS ((deptno), ());
!ok
+# [CALCITE-6104] Aggregate function that references outer column should be
evaluated in outer query
Review Comment:
Can we also have a test with 2 outer aggregates?
Showing the plan could also help.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
