Db2 error "An expression in the ORDER BY clause .. is not valid." when using LISTAGG(DISTINCT ..)

Question

This query seems to be valid in Db2 v11.5.7.0:

SELECT listagg(DISTINCT x, ',') WITHIN GROUP (ORDER BY x)
FROM (VALUES (1), (1), (2)) t (x)

But it fails with:

SQL Error [42822]: An expression in the ORDER BY clause in the following position, or starting with "" in the "ORDER BY" clause is not valid. Reason code = "2".. SQLCODE=-214, SQLSTATE=42822, DRIVER=4.29.24

What possible other thing could I use in the ORDER BY clause, other than x itself? Note that the problem is related to DISTINCT. This works:

SELECT listagg(x, ',') WITHIN GROUP (ORDER BY x)
FROM (VALUES (1), (1), (2)) t (x)

And produces the expected output:

1,1,2

Answer 1

This is documented behaviour. According to the docs:

If DISTINCT is specified for LISTAGG, the sort-key of the ORDER BY specification must match string-expression (SQLSTATE 42822). If string-expression is implicitly cast, the sort-key must explicitly include a corresponding matching cast specification.

So a possible workaround is:

SELECT listagg(DISTINCT x, ',') WITHIN GROUP (ORDER BY CAST(x as VARCHAR))
FROM (VALUES (1), (1), (2)) t (x)

Of course, that ordering might not be the same as the intended one, so this workaround may not apply to all input data!