Fix listagg-collation.sql test in Spark 4.0.0

[andygrove]

Describe the bug

In SQLQueryTestSutie, the following test is ignored for Spark 4.0.0 and needs to be investigated.

2025-06-27T01:43:32.3912463Z [info] - listagg-collations.sql *** FAILED *** (808 milliseconds)
2025-06-27T01:43:32.3913372Z [info]   listagg-collations.sql
2025-06-27T01:43:32.3914185Z [info]   Expected "a[]b", but got "a[ab]b" Result did not match for query #5
2025-06-27T01:43:32.3916171Z [info]   SELECT lower(listagg(DISTINCT c1 COLLATE utf8_lcase) WITHIN GROUP (ORDER BY c1 COLLATE utf8_lcase)) FROM (VALUES ('a'), ('B'), ('b'), ('A')) AS t(c1) (SQLQueryTestSuite.scala:683)

Steps to reproduce

No response

Expected behavior

No response

Additional context

No response

[andygrove]

Debug output showing the plans:

AdaptiveSparkPlan isFinalPlan=false
+- ObjectHashAggregate(keys=[], functions=[listagg(c1#97, null, collate(c1#97, utf8_binary) ASC NULLS FIRST, 0, 0)], output=[listagg(c1, NULL) WITHIN GROUP (ORDER BY collate(c1, utf8_binary) ASC NULLS FIRST)#99])
   +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=199]
      +- ObjectHashAggregate(keys=[], functions=[partial_listagg(c1#97, null, collate(c1#97, utf8_binary) ASC NULLS FIRST, 0, 0)], output=[buf#101])
         +- Project [col1#96 AS c1#97]
            +- LocalTableScan [col1#96]
AdaptiveSparkPlan isFinalPlan=false
+- Project [len(c1#119) AS len(c1)#120, size(regexp_extract_all(c1#119, a, 0), false) AS regexp_count(c1, a)#121, size(regexp_extract_all(c1#119, b, 0), false) AS regexp_count(c1, b)#122, size(regexp_extract_all(c1#119, A, 0), false) AS regexp_count(c1, A)#123, size(regexp_extract_all(c1#119, B, 0), false) AS regexp_count(c1, B)#124]
   +- ObjectHashAggregate(keys=[], functions=[listagg(distinct collate(col1#116, utf8_binary)#125, null, 0, 0)], output=[c1#119])
      +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=293]
         +- ObjectHashAggregate(keys=[], functions=[partial_listagg(distinct collate(col1#116, utf8_binary)#125, null, 0, 0)], output=[buf#128])
            +- CometHashAggregate [collate(col1#116, utf8_binary)#125], [collate(col1#116, utf8_binary)#125]
               +- CometColumnarExchange hashpartitioning(collate(col1#116, utf8_binary)#125, 4), ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=276]
                  +- HashAggregate(keys=[collate(col1#116, utf8_binary) AS collate(col1#116, utf8_binary)#125], functions=[], output=[collate(col1#116, utf8_binary)#125])
                     +- LocalTableScan [col1#116]
AdaptiveSparkPlan isFinalPlan=false
+- ObjectHashAggregate(keys=[], functions=[listagg(col1#141, null, col1#141 ASC NULLS FIRST, 0, 0)], output=[replace(replace(c1,  , ), 
, $)#145])
   +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=469]
      +- ObjectHashAggregate(keys=[], functions=[partial_listagg(col1#141, null, col1#141 ASC NULLS FIRST, 0, 0)], output=[buf#147])
         +- LocalTableScan [col1#141]
AdaptiveSparkPlan isFinalPlan=false
+- ObjectHashAggregate(keys=[], functions=[listagg(c1#162, null, collate(c1#162, utf8_lcase) ASC NULLS FIRST, 0, 0)], output=[lower(listagg(c1, NULL) WITHIN GROUP (ORDER BY collate(c1, utf8_lcase) ASC NULLS FIRST))#164])
   +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=524]
      +- ObjectHashAggregate(keys=[], functions=[partial_listagg(c1#162, null, collate(c1#162, utf8_lcase) ASC NULLS FIRST, 0, 0)], output=[buf#166])
         +- Project [col1#161 AS c1#162]
            +- LocalTableScan [col1#161]
AdaptiveSparkPlan isFinalPlan=false
+- Project [len(c1#184) AS len(c1)#185, size(regexp_extract_all(c1#184, a, 0), false) AS regexp_count(c1, a)#186, size(regexp_extract_all(c1#184, b, 0), false) AS regexp_count(c1, b)#187]
   +- ObjectHashAggregate(keys=[], functions=[listagg(distinct collate(col1#181, utf8_lcase)#188, null, 0, 0)], output=[c1#184])
      +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=625]
         +- ObjectHashAggregate(keys=[], functions=[partial_listagg(distinct collate(col1#181, utf8_lcase)#188, null, 0, 0)], output=[buf#191])
            +- SortAggregate(key=[collate(col1#181, utf8_lcase)#188], functions=[], output=[collate(col1#181, utf8_lcase)#188])
               +- CometSort [collate(col1#181, utf8_lcase)#188], [collate(col1#181, utf8_lcase)#188 ASC NULLS FIRST]
                  +- CometColumnarExchange hashpartitioning(collate(col1#181, utf8_lcase)#188, 4), ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=605]
                     +- SortAggregate(key=[collate(col1#181, utf8_lcase) AS collate(col1#181, utf8_lcase)#188], functions=[], output=[collate(col1#181, utf8_lcase)#188])
                        +- Sort [collate(col1#181, utf8_lcase) AS collate(col1#181, utf8_lcase)#188 ASC NULLS FIRST], false, 0
                           +- LocalTableScan [col1#181]
AdaptiveSparkPlan isFinalPlan=false
+- ObjectHashAggregate(keys=[], functions=[listagg(distinct collate(c1#205, utf8_lcase)#209, null, collate(c1#205, utf8_lcase)#209 ASC NULLS FIRST, 0, 0)], output=[lower(listagg(DISTINCT collate(c1, utf8_lcase), NULL) WITHIN GROUP (ORDER BY collate(c1, utf8_lcase) ASC NULLS FIRST))#207])
   +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=862]
      +- ObjectHashAggregate(keys=[], functions=[partial_listagg(distinct collate(c1#205, utf8_lcase)#209, null, collate(c1#205, utf8_lcase)#209 ASC NULLS FIRST, 0, 0)], output=[buf#212])
         +- SortAggregate(key=[collate(c1#205, utf8_lcase)#208, collate(c1#205, utf8_lcase)#209], functions=[], output=[collate(c1#205, utf8_lcase)#208, collate(c1#205, utf8_lcase)#209])
            +- CometSort [collate(c1#205, utf8_lcase)#208, collate(c1#205, utf8_lcase)#209], [collate(c1#205, utf8_lcase)#208 ASC NULLS FIRST, collate(c1#205, utf8_lcase)#209 ASC NULLS FIRST]
               +- CometColumnarExchange hashpartitioning(collate(c1#205, utf8_lcase)#208, collate(c1#205, utf8_lcase)#209, 4), ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=844]
                  +- SortAggregate(key=[collate(c1#205, utf8_lcase) AS collate(c1#205, utf8_lcase)#208, collate(c1#205, utf8_lcase) AS collate(c1#205, utf8_lcase)#209], functions=[], output=[collate(c1#205, utf8_lcase)#208, collate(c1#205, utf8_lcase)#209])
                     +- Sort [collate(c1#205, utf8_lcase) AS collate(c1#205, utf8_lcase)#208 ASC NULLS FIRST, collate(c1#205, utf8_lcase) AS collate(c1#205, utf8_lcase)#209 ASC NULLS FIRST], false, 0
                        +- Project [col1#204 AS c1#205]
                           +- LocalTableScan [col1#204]
AdaptiveSparkPlan isFinalPlan=false
+- Project [len(c1#228) AS len(c1)#229, size(regexp_extract_all(c1#228, a, 0), false) AS regexp_count(c1, a)#230, size(regexp_extract_all(c1#228, xbc, 0), false) AS regexp_count(c1, xbc)#231]
   +- ObjectHashAggregate(keys=[], functions=[listagg(distinct collate(col1#225, unicode_rtrim)#232, null, 0, 0)], output=[c1#228])
      +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=1074]
         +- ObjectHashAggregate(keys=[], functions=[partial_listagg(distinct collate(col1#225, unicode_rtrim)#232, null, 0, 0)], output=[buf#235])
            +- SortAggregate(key=[collate(col1#225, unicode_rtrim)#232], functions=[], output=[collate(col1#225, unicode_rtrim)#232])
               +- CometSort [collate(col1#225, unicode_rtrim)#232], [collate(col1#225, unicode_rtrim)#232 ASC NULLS FIRST]
                  +- CometColumnarExchange hashpartitioning(collate(col1#225, unicode_rtrim)#232, 4), ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=1054]
                     +- SortAggregate(key=[collate(col1#225, unicode_rtrim) AS collate(col1#225, unicode_rtrim)#232], functions=[], output=[collate(col1#225, unicode_rtrim)#232])
                        +- Sort [collate(col1#225, unicode_rtrim) AS collate(col1#225, unicode_rtrim)#232 ASC NULLS FIRST], false, 0
                           +- LocalTableScan [col1#225]
AdaptiveSparkPlan isFinalPlan=false
+- ObjectHashAggregate(keys=[], functions=[listagg(col1#248, null, collate(col1#248, unicode_rtrim) ASC NULLS FIRST, 0, 0)], output=[replace(replace(c1,  , ), 
, $)#252])
   +- CometColumnarExchange SinglePartition, ENSURE_REQUIREMENTS, CometColumnarShuffle, [plan_id=1274]
      +- ObjectHashAggregate(keys=[], functions=[partial_listagg(col1#248, null, collate(col1#248, unicode_rtrim) ASC NULLS FIRST, 0, 0)], output=[buf#254])
         +- LocalTableScan [col1#248]

[andygrove]

We should fall back to Spark for any query involving string collation. This is partially implemented in #1975