Wrong results caused by WITH clause or View inlining with Virtual Schema or LIMIT

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

Description

Queries that contain a WITH clause or inline a view may cause invalid results. There are two distinctive cases that have the same origin.

Variant 1:
A query with a WITH clause or a view may cause the subquery inside the WITH clause or view to produce empty results:

• The WITH clause or view is used at least once inside the query.
• The subquery in the WITH clause or view contains a table from a virtual schema.
• The virtual schema pushdown contains a GROUP BY (this can be caused by a DISTINCT).
• The virtual schema pushdown happens on the top layer subquery of the WITH clause or view (i.e., not in the FROM or in nested subqueries).

Variant 2:
A query with a WITH clause or view may cause the subquery inside the WITH clause or view to not apply LIMIT:

• The WITH clause or view is used at least once inside the query.
• The subquery in the WITH clause or view contains a LIMIT.
• The subquery in the WITH clause or view contains an analytic function.
• The subquery in the WITH clause or view does not contain an ORDER BY, which makes the results non deterministic.
• The LIMIT and analytic function appears on the top layer subquery of the WITH clause  or view (i.e., not in the FROM or in nested subqueries).

Examples
Variant 1:

-- Setup
create schema ADAPTER;
--/
CREATE or replace JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER_SCRIPT AS
    %scriptclass com.exasol.adapter.RequestDispatcher;
    %jvmoption -Xms64m -Xmx64m;
    %jar /buckets/testing/jdbc-adapter/virtualschema-jdbc-adapter.jar;
/
CREATE OR REPLACE CONNECTION EXA
    to '...' user '...' identified by '...';

create schema S;
create or replace table S.A(A1 int, A2 int, A3 int, A4 int);
insert into S.A values (3333, 3333, 2, 0),
                       (1007, 3333, 1, 1),
                       (2222, 1008, 1, 1),
                       (2222, 2222, 1, 0),
                       (2222, 1009, 1, 2);

create schema VS_BASE;
create or replace table VS_BASE.B(B1 VARCHAR(50) UTF8, B2 int);
insert into VS_BASE.B values ('--', 3333),
                             ('SSSSSSSSSSSSSSSSSSSSS', 2222);

create virtual schema VS using ADAPTER.JDBC_ADAPTER_SCRIPT
with connection_name = 'EXA' schema_name = 'VS_BASE';

-- Expected: 2 rows returned
-- Observed: 0 rows returned
with W1 as (select distinct B1 from VS.B),
     W2 as (select A.A1, A.A2, A.A3, B.B1, B.B2
            from S.A
                 join VS.B on B.B2 = A.A1
                 join W1 on W1.B1 = B.B1
            where A.A4 = 0 or A.A4 = 1
            group by A.A1, A.A2, A.A3, B.B1, B.B2)
select *
from W2 LHS left join W2 RHS
     on LHS.A2 = RHS.A1 and RHS.A3 = 2
where LHS.A3 = 1;

Variant 2:

create table a (a1 int);

-- Expected: query returns 1 row
-- Observed: query returns 16 rows
insert into a values 1,2,3,4;

with w as (
      select row_number() over () rn from A
      limit 1
  )
  select a.rn ar, b.rn br
  from w a, w b;

Workaround

Workaround ideas are to avoid one of mandatory bug conditions.
Also please reach out to support for a system wide solution.

Variant 1:

with W1 as (select distinct B1 from (select * from VS_LOCAL.B order by false)),
     W2 as (select A.A1, A.A2, A.A3, B.B1, B.B2
            from S.A
                 join VS_LOCAL.B on B.B2 = A.A1
                 join W1 on W1.B1 = B.B1
            where A.A4 = 0 or A.A4 = 1
            group by A.A1, A.A2, A.A3, B.B1, B.B2)
select *
from W2 LHS left join W2 RHS
     on LHS.A2 = RHS.A1 and RHS.A3 = 2
where LHS.A3 = 1;

Variant 2:

with w as (
      select *
      from (
          select row_number() over () rn from a
          order by false)
      limit 1
  )
  select a.rn ar, b.rn br
  from w a, w b;

Fix

The queries return the correct results.