Prepared statements in IN lists cause invalid exception

Detail name	Value
Changelog Number	29562
Type	Bug
Status	Resolved
Affected Versions	Exasol 2025.1.10
Fix Versions	Exasol 2026.1.0, Exasol 2025.1.11
Resolution Date	2026-05-15

Description

Valid queries using prepared parameters inside IN lists may lead to the incorrect error “All expressions in the IN(...) list should be constants”. This only happens if the following conditions apply:

• An IN list is within a WITH clause.
• The IN list contains at least two prepared parameters.
• The WITH clause is used at least twice in the query.
• At least two occurrences of the WITH clause are inlined and the inlined versions are left unmodified by the compiler.
• The subquery cache within one query is active.
• The subquery cache within one query identifies the two identical WITH clauses as equal.

Example:

CREATE OR REPLACE TABLE T(x INT, y VARCHAR(100));
INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'d');

-- Expected (with 'a', 'b',' c'): 2 rows
-- Observed: Error: All expressions in the IN(...) list should be constants
WITH t AS 
  (SELECT x FROM t WHERE (y) IN (?, ?) ORDER BY x),
t2 AS 
  (SELECT 'c' AS q FROM t
   UNION ALL
   SELECT 'd' AS q FROM t
  )
SELECT * FROM t2 WHERE (q) IN (?);

Workaround

Remove one of the above conditions. For example, allow other compiler optimizations by removing the ORDER BY:

-- Observed (with 'a', 'b',' c'): 2 rows
WITH t AS 
  (SELECT x FROM t WHERE (y) IN (?, ?)),
t2 AS 
  (SELECT 'c' AS q FROM t
   UNION ALL
   SELECT 'd' AS q FROM t
  )
SELECT * FROM t2 WHERE (q) IN (?);

Fix

The queries work as intended.