SQLSTATE 58004, Error "invalid qnc assigment" when using NULL in JSON_OBJECT() with LISTAGG in DB2 LUW

There seems to be an interesting combination of features that lead to an error in Db2 LUW v11.5.0.0. To reproduce, write:

CREATE TABLE t (a INT);
INSERT INTO t VALUES (1), (2);

SELECT '[' || listagg(
  json_object(
    KEY 'a' VALUE a,
    KEY 'b' VALUE b
  ), ','
) WITHIN GROUP (ORDER BY a) || ']'
FROM (
  SELECT a, NULL b
  FROM t
  ORDER BY a
);

I'm using LISTAGG to work around Db2 LUW's missing JSON_ARRAYAGG support.

The error I'm getting is this:

The SQL statement or command failed because of a database system error. (Reason "invalid qnc assigment".). SQLCODE=-901, SQLSTATE=58004, DRIVER=4.7.85

Quite probably a bug in the parser. There doesn't seem to be anything obviously wrong. How can this be prevented / worked around?

Solution

I've found a few workarounds which I'd like to document here, in case someone runs into this as well:

Cast the NULL literal to a specific type

SELECT '[' || listagg(
  json_object(
    KEY 'a' VALUE a,
    KEY 'b' VALUE b
  ), ','
) WITHIN GROUP (ORDER BY a) || ']'
FROM (
  SELECT a, CAST(NULL AS VARCHAR(1)) b -- Workaround here
  FROM t
  ORDER BY a
);

Cast the value in JSON_OBJECT

It might not be possible to know the type of b, which could also be numeric, instead of a string.

SELECT '[' || listagg(
  json_object(
    KEY 'a' VALUE a,
    KEY 'b' VALUE CAST(b AS VARCHAR(32672)) -- Workaround here
  ), ','
) WITHIN GROUP (ORDER BY a) || ']'
FROM (
  SELECT a, NULL b
  FROM t
  ORDER BY a
);

Remove the ORDER BY clause, which is not needed in this case (it could be if there was a FETCH FIRST clause)

SELECT '[' || listagg(
  json_object(
    KEY 'a' VALUE a,
    KEY 'b' VALUE b
  ), ','
) WITHIN GROUP (ORDER BY a) || ']'
FROM (
  SELECT a, NULL b
  FROM t
  -- Workaround here
);

Adding an expression that "obfuscates" the NULL value

SELECT '[' || listagg(
  json_object(
    KEY 'a' VALUE a,
    KEY 'b' VALUE COALESCE(b, NULLIF(1, 1)) -- Workaround here
  ), ','
) WITHIN GROUP (ORDER BY a) || ']'
FROM (
  SELECT a, NULL b
  FROM t
  ORDER BY a
);

All of these producing the desired

1                                  |
-----------------------------------|
[{"a":1,"b":null},{"a":2,"b":null}]|