Fix compilation hang for queries with mixed scalar and non-scalar subqueries

Details

Detail name Value
Changelog Number 32058
Type Bug
Status Resolved
Affected Versions Exasol 2025.1.11, Exasol 2026.1.1
Fix Versions Exasol 2026.1.2, Exasol 2025.1.15
Resolution Date 2026-08-13

Description

In rare cases, complex queries that include both scalar subqueries (e.g., “where 1 = (select ...)”) and non-scalar subqueries (e.g., “where 1 in (select ...)” ) can result in a hang during compilation.

Example

-- Setup
CREATE SCHEMA s;

CREATE TABLE a (a1 INT);
CREATE TABLE b (b1 INT);
CREATE TABLE c (c1 INT);
CREATE TABLE d (d1 INT);
CREATE TABLE e (e1 INT, e2 INT, e3 INT);
CREATE TABLE f (f1 INT, f2 INT, f3 INT, f4 INT);
CREATE TABLE g (g1 INT, g2 INT);

CREATE VIEW v1 AS
    SELECT *
    FROM (SELECT *
          FROM d
          ORDER BY FALSE);

CREATE VIEW v2 AS
    SELECT g1,
           g2,
           (SELECT d1 FROM d ORDER BY FALSE) AS c3,
           (SELECT c1 FROM c ORDER BY FALSE) AS c4
    FROM g
    WHERE g1 NOT IN (SELECT a1 FROM a);

-- Expected: Query completes successfully
-- Observed: Query does not complete
CREATE VIEW v3 AS
    SELECT 1 AS c1
    FROM v2 LEFT JOIN f ON v2.g1 = f1
                           AND v2.c3 = f2
                           AND v2.c4 = f3
            LEFT JOIN e ON v2.g2 = e1
            LEFT JOIN b ON e3 = b1
            LEFT JOIN v1 ON v2.c3 = d1
    WHERE e2 NOT IN (111, 222)
          AND f4 = 3;

-- Expected: 0 rows
SELECT * FROM v3;

Workaround

  1. Add ORDER BY FALSE in a subquery at an appropriate point to separate the predicates that cause the problem. You might also need to create a subselect to split them, as in this example.
  2. Change a DB parameter. However, this option has some drawbacks. Please speak to the support team for more details.
-- Workaround: the query completes
CREATE VIEW v2 AS
    SELECT *
    FROM (SELECT g1,
                 g2,
                 (SELECT d1 FROM d ORDER BY FALSE) AS c3,
                 (SELECT c1 FROM c ORDER BY FALSE) AS c4
          FROM g
          ORDER BY FALSE)
    WHERE g1 NOT IN (SELECT a1 FROM a);

Fix

The query will complete successfully.