Cannot use a CTE in a scalar subquery in Db2

Question

I'm trying to use a CTE in a DB2 LUW v11.5.4.0 scalar subquery, but this doesn't work:

SELECT 1
FROM SYSIBM.dual
WHERE 1 IN (
  WITH t (x) AS (SELECT 1 FROM SYSIBM.dual)
  SELECT * FROM t
);

I'm getting this error:

SQL Error [42601]: An unexpected token "AS" was found following "1 IN ( WITH t (x)". Expected tokens may include: "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

Can this be done? Is there a workaround?

(This is similar but not the same as this question, which is about derived tables, not scalar subqueries. Derived tables support CTE in version 11.5.4.0)

Answer 1

It seems that in version 11.5.4.0, it's possible to use CTEs in derived tables, so the query can be emulated as follows:

SELECT 1
FROM SYSIBM.dual
WHERE 1 IN (
  SELECT *
  FROM (
    WITH t (x) AS (SELECT 1 FROM SYSIBM.dual)
    SELECT * FROM t
  ) t
);

However, this workaround also doesn't work in older versions of Db2, e.g. in version v11.1.4.4 as can be seen in this dbfiddle or in this question. The workaround in those versions will be to push the CTE to the top level of the query:

WITH t (x) AS (SELECT 1 FROM SYSIBM.dual)
SELECT 1
FROM SYSIBM.dual
WHERE 1 IN (
  SELECT * FROM t
);

Side note

HSQLDB seems to suffer from a similar limitation / bug, see https://sourceforge.net/p/hsqldb/bugs/1617.