This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 7d4135eb17 [CALCITE-6759] RelToSqlConverter returns the wrong result
when Aggregate is on Sort
7d4135eb17 is described below
commit 7d4135eb17e2a70ac608ab1dd3e5a7f4d03706da
Author: suibianwanwan <[email protected]>
AuthorDate: Tue Jan 7 11:16:02 2025 +0800
[CALCITE-6759] RelToSqlConverter returns the wrong result when Aggregate is
on Sort
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 7 +++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 22 ++++++++++++++++++++++
.../java/org/apache/calcite/test/PigRelOpTest.java | 11 +++++------
3 files changed, 34 insertions(+), 6 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index e9a7f88e74..c4fa3897ef 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -1965,6 +1965,13 @@ public abstract class SqlImplementor {
return true;
}
+ if (rel instanceof Aggregate
+ && (clauses.contains(Clause.ORDER_BY)
+ || clauses.contains(Clause.FETCH)
+ || clauses.contains(Clause.OFFSET))) {
+ return true;
+ }
+
// Cannot merge two window functions
boolean containsOver = containsOver(node);
if (rel instanceof Project
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index f74cab5f16..628d29db79 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -8640,6 +8640,28 @@ class RelToSqlConverterTest {
.withSpark().ok(sparkExpected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6748";>[CALCITE-6748]
+ * RelToSqlConverter returns the wrong result when Aggregate is on Sort</a>.
*/
+ @Test void testAggregateOnSort() {
+ final String query0 = "select max(\"product_class_id\") "
+ + "from (select * from \"product\" order by \"brand_name\" asc limit
10) t";
+ final String expected0 = "SELECT MAX(\"product_class_id\")\n"
+ + "FROM (SELECT \"product_class_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\"\n"
+ + "FETCH NEXT 10 ROWS ONLY) AS \"t1\"";
+ sql(query0).ok(expected0);
+
+ final String query1 = "select max(\"product_class_id\") "
+ + "from (select * from \"product\" offset 10 ) t";
+ final String expected1 = "SELECT MAX(\"product_class_id\")\n"
+ + "FROM (SELECT \"product_class_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "OFFSET 10 ROWS) AS \"t1\"";
+ sql(query1).ok(expected1);
+ }
+
@Test void testNullCollationAscNullFirst() {
final String query = "select * from \"product\" order by \"brand_name\"
asc nulls first";
final String expected = "SELECT *\n"
diff --git a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
index ed6301ee0c..26905b3fe8 100644
--- a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
+++ b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
@@ -479,17 +479,16 @@ class PigRelOpTest extends PigRelTestBase {
+ "HIREDATE, SAL, COMM, DEPTNO)) AS A\n"
+ " FROM scott.EMP\n"
+ " GROUP BY DEPTNO) AS $cor4,\n"
- + " LATERAL (SELECT X\n"
- + " FROM (SELECT 'all' AS $f0, COLLECT(ROW(ENAME, JOB, DEPTNO,
SAL)) AS X\n"
+ + " LATERAL (SELECT COLLECT($f1) AS X\n"
+ + " FROM (SELECT 'all' AS $f0, ROW(ENAME, JOB, DEPTNO, SAL) AS
$f1\n"
+ " FROM UNNEST (SELECT $cor4.A AS $f0\n"
+ " FROM (VALUES (0)) AS t (ZERO)) "
+ "AS t2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)\n"
+ " WHERE JOB <> 'CLERK'\n"
- + " GROUP BY 'all'\n"
- + " ORDER BY SAL) AS t7) AS t8) AS $cor5,\n"
+ + " ORDER BY SAL) AS t6\n"
+ + " GROUP BY $f0) AS t8) AS $cor5,\n"
+ " LATERAL UNNEST (SELECT $cor5.X AS $f0\n"
- + " FROM (VALUES (0)) AS t (ZERO)) "
- + "AS t11 (ENAME, JOB, DEPTNO, SAL) AS t110\n"
+ + " FROM (VALUES (0)) AS t (ZERO)) AS t11 (ENAME, JOB, DEPTNO, SAL)
AS t110\n"
+ "ORDER BY $cor5.group";
pig(script).assertRel(hasTree(plan))
.assertResult(is(result))
