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

Question

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?

Always in best shape. I admire the way you implement JOOQ to work on multiple RDBMSes/versions and find non-obvious errors on the way. Keep going :) — Lukasz Szozda
You should never use untyped NULL values in Db2. Despite the fact that CALL ADMIN_CMD('DESCRIBE SELECT 1 a, NULL b FROM sysibm.sysdummy1') returns VARCHAR(1) for b, it's only implicit guessing. Db2 should know exact column data type to use the value properly further... — Mark Barinstein
@MarkBarinstein: Sure it's a risk, and it used to be much worse. These days, Db2 can handle untyped NULL values much better. But the workarounds I've documented show that this is just a plain simple bug. There's no valid reason for this error. For example, removing the ORDER BY clause in the derived table "fixes it", or the COALESCE(b, NULLIF(1, 1)) hack, too. — Lukas Eder
This does not reproduce on a newer Db2 level (i.e. the container only v11.5.2.0 release) — Paul Vernon
@LukasEder Sorry for spamming here, but I think I have an answer for JSON null for MySQL: select j, j is null, j = 'null', j = CAST('null' AS JSON) FROM (SELECT json_extract('{"x":null}' ,'$.x') AS j) t demo — Lukasz Szozda

Lukas Eder Lukas Eder · Accepted Answer · 2020-04-18T08:10:47

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}]|

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

1 Answers