MULTISET equality is ORDER dependent in dialects without support for correlated derived tables #16297
Description
There's a "regression" in the Db2 integration tests for this test:
public void testMultisetInPredicates_EQ() throws Exception {
testMultisetEmulations(ctx -> {
assertEquals(1, ctx.fetchValue(
selectCount()
.from(TAuthor())
.where(
multiset(
select(TBook_ID()).from(TBook()).where(TBook_AUTHOR_ID().eq(TAuthor_ID()))
)
.eq(multiset(
select(val(2).as(TBook_ID()))
.union(
select(val(1).as(TBook_ID())))
))
)
));
});
}The produced query is:
select count(*)
from "DB2INST1"."T_AUTHOR"
where (
select coalesce(
cast((('[' || listagg(
cast(json_object(
key 'v0' value "DB2INST1"."T_BOOK"."ID"
returning blob
) as varchar(32672)),
','
)) || ']') as blob), -- No explicit ordering here
json_array(returning blob)
)
from "DB2INST1"."T_BOOK"
where "DB2INST1"."T_BOOK"."AUTHOR_ID" = "DB2INST1"."T_AUTHOR"."ID"
) = (
select coalesce(
cast((('[' || listagg(
cast(json_object(
key 'v0' value "v0"
returning blob
) as varchar(32672)),
','
) within group (order by t."v0")) || ']') as blob),
json_array(returning blob)
)
from (
select 2 "v0"
from SYSIBM.DUAL
union
select 1 "ID"
from SYSIBM.DUAL
) t
)The problem is that the first LISTAGG isn't being ordered, only the second. This means that the result is non-deterministic. The test didn't fail so far because there's little entropy in our test data, the data is always inserted in order and it's thus quite unlikely that any different order is produced randomly. The test actually passes when run individually. It fails only in the context of the entire test suite. Probably caused by a recently added integration test that deletes and restores a row in a table.
The same happens with the XML emulation:
select count(*)
from "DB2INST1"."T_AUTHOR"
where xmlserialize(content (
select xmlelement(
name result,
xmlagg(xmlelement(
name record,
xmlelement(name "v0", "DB2INST1"."T_BOOK"."ID")
)) -- No explicit ordering here
)
from "DB2INST1"."T_BOOK"
where "DB2INST1"."T_BOOK"."AUTHOR_ID" = "DB2INST1"."T_AUTHOR"."ID"
) as varchar(32672)) = xmlserialize(content (
select xmlelement(
name result,
xmlagg(xmlelement(
name record,
xmlelement(name "v0", "v0")
) order by t."v0")
)
from (
select 2 "v0"
from SYSIBM.DUAL
union
select 1 "ID"
from SYSIBM.DUAL
) t
) as varchar(32672))