xuzifu666 commented on code in PR #4983:
URL: https://github.com/apache/calcite/pull/4983#discussion_r3360612675
##########
core/src/test/java/org/apache/calcite/test/enumerable/EnumerableSortedAggregateTest.java:
##########
@@ -133,6 +133,121 @@ public class EnumerableSortedAggregateTest {
"commission=null; num_dept=1");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5101";>[CALCITE-5101]
+ * LISTAGG(DISTINCT ...) WITHIN GROUP fails with
ArrayIndexOutOfBoundsException</a>. */
+ @Test void listAggDistinctWithinGroupOrderByNonGroupColumn() {
+ // FAILING CASE: LISTAGG(DISTINCT ...) WITHIN GROUP (ORDER BY
non-group-column)
+ // Previously threw: ArrayIndexOutOfBoundsException: Index 2 out of bounds
for length 2
+ // Now fixed: ORDER BY salary is properly projected and remapped.
+ // Within each group, names are ordered by salary (ascending).
+ // deptno=10: Sebastian (7000), Bill (10000), Theodore (11500)
+ // deptno=20: Eric (8000)
+ tester(false, new HrSchema())
+ .query("select deptno, "
+ + "LISTAGG(DISTINCT name) WITHIN GROUP (ORDER BY salary) as names "
+ + "from emps group by deptno")
+ .returnsUnordered(
+ "deptno=10; names=Sebastian,Bill,Theodore",
+ "deptno=20; names=Eric");
+ }
+
+ @Test void listAggDistinctWithoutOrderBy() {
+ // WORKING CASE: LISTAGG(DISTINCT ...) without ORDER BY - always worked
+ tester(false, new HrSchema())
+ .query("select deptno, "
+ + "LISTAGG(DISTINCT name) as names "
+ + "from emps group by deptno")
+ .returnsUnordered(
+ "deptno=10; names=Bill,Sebastian,Theodore",
+ "deptno=20; names=Eric");
+ }
+
+ @Test void listAggWithoutDistinctWithinGroupOrderBy() {
+ // WORKING CASE: LISTAGG(...) WITHIN GROUP (ORDER BY non-group-column)
+ // without DISTINCT - always worked
+ tester(false, new HrSchema())
+ .query("select deptno, "
+ + "LISTAGG(name) WITHIN GROUP (ORDER BY salary) as names "
+ + "from emps group by deptno")
+ .returnsUnordered(
+ "deptno=10; names=Sebastian,Bill,Theodore",
+ "deptno=20; names=Eric");
+ }
+
+ @Test void listAggDistinctWithinGroupOrderByAggColumn() {
+ // WORKING CASE: LISTAGG(DISTINCT ...) WITHIN GROUP (ORDER BY
aggregated-column)
+ // - always worked because the aggregated column is in the re-grouped input
+ tester(false, new HrSchema())
+ .query("select deptno, "
+ + "LISTAGG(DISTINCT name) WITHIN GROUP (ORDER BY name) as names "
+ + "from emps group by deptno")
+ .returnsUnordered(
+ "deptno=10; names=Bill,Sebastian,Theodore",
+ "deptno=20; names=Eric");
+ }
+
+ @Test void listAggMultipleDistinctWithinGroupOrderByNonGroupColumn() {
+ // Test case for multiple DISTINCT aggregates with different ORDER BY
columns
+ // Previously threw: ArrayIndexOutOfBoundsException
+ // ORDER BY salary for first agg (not a group key), ORDER BY name for
second
+ // This tests that collation indices are properly remapped in
rewriteUsingGroupingSets
+ tester(false, new HrSchema())
+ .query("select deptno, "
+ + "LISTAGG(DISTINCT name) WITHIN GROUP (ORDER BY salary) as
names_by_salary, "
+ + "LISTAGG(DISTINCT commission) WITHIN GROUP (ORDER BY name) as
commissions_by_name "
+ + "from emps group by deptno")
+ .returnsUnordered(
+ "deptno=10; names_by_salary=Theodore,Sebastian,Bill;
commissions_by_name=250,1000",
+ "deptno=20; names_by_salary=Eric; commissions_by_name=500");
+ }
+
+ @Test void sumDistinctWithinGroupOrderByNonGroupColumn() {
+ // Regression test: Verify DISTINCT semantics are preserved when ORDER BY
+ // is not a GROUP BY column. This ensures SUM(DISTINCT sal) WITHIN GROUP
+ // (ORDER BY bonus) correctly computes the sum, not incorrectly counting
the
+ // same sal value multiple times for different bonus values.
+ // Example: Two rows with sal=10000 but different bonuses count as one.
+ tester(false, new HrSchema())
+ .query("select deptno, SUM(DISTINCT salary) as total_distinct_salary "
+ + "from emps group by deptno")
+ .returnsUnordered(
+ "deptno=10; total_distinct_salary=28500.0", // 10000 + 7000 +
11500
+ "deptno=20; total_distinct_salary=8000.0");
+ }
+
+ @Test void groupingSetsWithCollationReferencingOutsideGroupingSets() {
Review Comment:
I have added order by, and the corresponding PostgreSQL test is here:
https://onecompiler.com/postgresql/44rcggaqf
--
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]
