#1617 CTE cannot be used in some scalar subqueries

[Lukas Eder]

There are some scalar subqueries that cannot use WITH, others can. Here it doesn't work:

SELECT (
  WITH t(a) AS (
    SELECT 1
    FROM (VALUES(1))
  )
  SELECT a FROM t
)
FROM (VALUES (1));

SELECT 1 = (
  WITH t(a) AS (
    SELECT 1
    FROM (VALUES(1))
  )
  SELECT a FROM t
)
FROM (VALUES (1));

SELECT 1 IS NOT DISTINCT FROM (
  WITH t(a) AS (
    SELECT 1
    FROM (VALUES(1))
  )
  SELECT a FROM t
)
FROM (VALUES (1));

Here it works:

SELECT 1 IN (
  WITH t(a) AS (
    SELECT 1
    FROM (VALUES(1))
  )
  SELECT a FROM t
)
FROM (VALUES (1));

Derived tables also work, as a generic workaround:

SELECT (SELECT * FROM (
  WITH t(a) AS (
    SELECT 1
    FROM (VALUES(1))
  )
  SELECT a FROM t
))
FROM (VALUES (1));

Given that the distinction seems arbitrary (especially in the 1 IN (WITH ...) vs 1 = (WITH ...) case, this might just be a parser bug? If it's by design, then consider this to be a feature request.

[Fred Toussi]

Thanks for reporting. Fixed. New RC6 jar is in http://hsqldb.org/download/hsqldb_260_jdk11_rc/