xuzifu666 commented on code in PR #5052: URL: https://github.com/apache/calcite/pull/5052#discussion_r3485292656
########## core/src/test/resources/sql/agg.iq: ########## @@ -4301,4 +4301,128 @@ GROUP BY GROUPING SETS ((deptno), ()); !ok +# [CALCITE-6104] Aggregate function that references outer column should be evaluated in outer query +# The expected results below have been verified in PostgreSQL by running Review Comment: Yes, this is indeed a confusing point. These correlated-aggregate queries are not standard SQL, and CALCITE-6104 should not implement a SQL standard feature. Instead, it makes Calcite support a non-standard extension that some databases already implement. The SQL standard does not allow aggregate functions to reference outer columns In standard SQL, an aggregate function like sum(a) can only reference columns from the SELECT level where it appears. So a query like: ``` SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; ``` will fail on PostgreSQL, Oracle, and MySQL/MariaDB because a belongs to the outer table aa, not the sub-query xx. But PostgreSQL and others support this extension. These databases allow an aggregate to "climb up" to the nearest SELECT that contains its free variables. Calcite previously handled this pattern incorrectly (it aggregated inside the sub-query, grouped by the outer rows). The fix makes Calcite behave consistently with those databases. This test is provided in Jira (https://github.com/julianhyde/calcite/commit/3f4cab57ea9969522199af3e9d448f5fba61482f) is supported—at least with the current PR applied; these are not standard SQL either. Additionally, I tested converting these SQL statements to standard SQL; they passed even without this PR, which is why I hold the view mentioned above. -- 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]
