Changeset: 7a73534e954d for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/7a73534e954d Added Files: sql/test/group-concat/Tests/listagg01.test sql/test/group-concat/Tests/listagg02.test sql/test/group-concat/Tests/listagg03.test Modified Files: sql/test/group-concat/Tests/All Branch: Mar2025 Log Message:
Duplicated all GROUP_CONCAT tests for LISTAGG diffs (truncated from 614 to 300 lines): diff --git a/sql/test/group-concat/Tests/All b/sql/test/group-concat/Tests/All --- a/sql/test/group-concat/Tests/All +++ b/sql/test/group-concat/Tests/All @@ -1,3 +1,9 @@ +# NB: GROUP_CONCAT and LISTAGG are expected to work exactly the same way. +# Hence, for every GROUP_CONCAT test, the same test should be added for +# LISTAGG, and vice versa groupconcat01 groupconcat02 groupconcat03 +listagg01 +listagg02 +listagg03 diff --git a/sql/test/group-concat/Tests/listagg01.test b/sql/test/group-concat/Tests/listagg01.test new file mode 100644 --- /dev/null +++ b/sql/test/group-concat/Tests/listagg01.test @@ -0,0 +1,353 @@ +statement ok +start transaction + +statement ok +create table testme (a int, b clob) + +# test supported syntax _without_ ordering _in_ group_concat +# use the same string for `b` per `a` group to make the query output +# independent of the row-order of `testme` +statement ok +INSERT INTO testme VALUES (1, 'foo'), (1, 'foo'), (1, 'foo'), (2, 'bar'), (2, 'bar'), (2, 'bar') + +query IT +SELECT a, LISTAGG(b) FROM testme GROUP BY a ORDER BY a +---- +1 +foo,foo,foo +2 +bar,bar,bar + +query IT +SELECT a, LISTAGG(ALL b) FROM testme GROUP BY a ORDER BY a +---- +1 +foo,foo,foo +2 +bar,bar,bar + +query IT +SELECT a, LISTAGG(DISTINCT b) FROM testme GROUP BY a ORDER BY a +---- +1 +foo +2 +bar + +query IT +SELECT a, LISTAGG(DISTINCT (b)) FROM testme GROUP BY a ORDER BY a +---- +1 +foo +2 +bar + +query IT +SELECT a, LISTAGG(b, ';') FROM testme GROUP BY a ORDER BY a +---- +1 +foo;foo;foo +2 +bar;bar;bar + +query IT +SELECT a, LISTAGG(b, '#@#') FROM testme GROUP BY a ORDER BY a +---- +1 +foo#@#foo#@#foo +2 +bar#@#bar#@#bar + +query IT +SELECT a, LISTAGG(ALL b, ';') FROM testme GROUP BY a ORDER BY a +---- +1 +foo;foo;foo +2 +bar;bar;bar + +query IT +SELECT a, LISTAGG(ALL b, '#@#') FROM testme GROUP BY a ORDER BY a +---- +1 +foo#@#foo#@#foo +2 +bar#@#bar#@#bar + +# the second column is the separator +query IT +SELECT a, LISTAGG(a, b) FROM testme GROUP BY a ORDER BY a +---- +1 +1foo1foo1 +2 +2bar2bar2 + +# Distinct is applied on (a, b) +query IT +SELECT a, LISTAGG(DISTINCT a, b) FROM testme GROUP BY a ORDER BY a +---- +1 +1 +2 +2 + +# test the syntax for ordering _within_ a group_concat, +# also use it to get deterministic query results +statement ok +DELETE FROM testme + +statement ok +INSERT INTO testme VALUES (1, 'another'), (1, 'testing'), (1, 'todo'), (2, 'lets'), (2, 'get'), (2, 'harder'), (3, 'even'), (2, 'more'), (1, 'serious'), (3, 'even'), (2, 'more'), (1, 'serious') + +query IT +SELECT a, LISTAGG(b ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more +3 +even,even + +query IT +SELECT a, LISTAGG(b ORDER BY b) FROM testme GROUP BY a HAVING COUNT(*) > 2 ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more + +query IT +SELECT a, LISTAGG(b) WITHIN GROUP (ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more +3 +even,even + +query IT +SELECT a, LISTAGG(ALL b ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more +3 +even,even + +query IT +SELECT a, LISTAGG(ALL b) WITHIN GROUP (ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more +3 +even,even + +query IT +SELECT a, LISTAGG(DISTINCT b ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,testing,todo +2 +get,harder,lets,more +3 +even + +query IT +SELECT a, LISTAGG(DISTINCT b) WITHIN GROUP (ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,testing,todo +2 +get,harder,lets,more +3 +even + +query IT +SELECT a, LISTAGG(b, ';' ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another;serious;serious;testing;todo +2 +get;harder;lets;more;more +3 +even;even + +query IT +SELECT a, LISTAGG(b, ';') WITHIN GROUP (ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another;serious;serious;testing;todo +2 +get;harder;lets;more;more +3 +even;even + +query IT +SELECT a, LISTAGG(ALL b, ';' ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another;serious;serious;testing;todo +2 +get;harder;lets;more;more +3 +even;even + +query IT +SELECT a, LISTAGG(ALL b, ';') WITHIN GROUP (ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another;serious;serious;testing;todo +2 +get;harder;lets;more;more +3 +even;even + +query IT +SELECT a, LISTAGG(DISTINCT b, ';' ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another;serious;testing;todo +2 +get;harder;lets;more +3 +even + +query IT +SELECT a, LISTAGG(DISTINCT b, ';') WITHIN GROUP (ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another;serious;testing;todo +2 +get;harder;lets;more +3 +even + +# Test that groun_concat ignores NULLs but not empty strings +statement ok +DELETE FROM testme + +statement ok +INSERT INTO testme VALUES (1, 'another'), (1, 'testing'), (1, 'todo'), + (2, 'lets'), (2, 'get'), (2, 'harder'), + (3, 'even'), (2, 'more'), (1, 'serious'), + (3, 'even'), (2, 'more'), (1, 'serious'), + (3, ''), (3, ''), (3, 'more'), (3, 'stress'), + (4, NULL) + +query IT +SELECT a, LISTAGG(b ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more +3 +,,even,even,more,stress +4 +NULL + +query IT +SELECT a, LISTAGG(DISTINCT b ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,testing,todo +2 +get,harder,lets,more +3 +,even,more,stress +4 +NULL + + +statement ok +INSERT INTO testme VALUES (3, NULL), (4, ''), (4, NULL), (4, ''), (4, NULL), (4, ''), (3, 'nothing'), (4, ' '), (4, ' ') + +query IT +SELECT a, LISTAGG(b ORDER BY b) FROM testme GROUP BY a ORDER BY a +---- +1 +another,serious,serious,testing,todo +2 +get,harder,lets,more,more +3 +,,even,even,more,nothing,stress +4 +,,, , + _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
