Wrong results with column from self-join used in window function and subquery elimination

Details

Detail name Value
Changelog Number 32228
Type Bug
Status Resolved
Affected Versions Exasol 2026.1.0, Exasol 2025.1.11
Fix Versions Exasol 2026.1.2, Exasol 2025.1.16
Resolution Date 2026-08-28

Description

Queries with the following conditions can give wrong results:

  • A select has one of the following:
  • The base table cannot be replicated. (E.g. it has more rows than REPLICATION_BORDER).
  • The eliminated view/subselect has to be simple (e.g., no GROUP BY, no ORDER BY FALSE, no Analytic Function, no UDF call).
  • The select calculates a window function.
  • The window function uses a column from one of the self-joined tables in its PARTITION BY or ORDER BY clause.
  • The select also projects the same column from the other table in the self join (including select *).
  • A join between a base table and a view or subselect from the same base table that gets eliminated by the optimizer (e.g. select … from (select * from T) as LHS, T;); or
  • A join between two such subselects and/or views from the same base table that the optimizer eliminates (e.g. select … from (select * from T) as LHS, (select * from T) as RHS;).

The Compiler can then use the column from the wrong instance of a base table involved in a self-join in the PARTITION BY and ORDER BY lists in an OVER clause of a window function.

Examples:

-- Setup
create table A (A1 int, A2 int, A3 int, A4 int);
insert into A
  select 1, 2, V1, V1
  from values between 1 and 4 as V(V1),
       values between 1 and 2 as DUP;

-- EITHER (prevent replication):
alter system set replication_border = 1;

-- OR (add more rows to table than replication_border):
insert into A
  select 0, 0, 0, 0
  from values between 1 and 100000;

-- Example with ORDER BY and base tables
--
-- Expected: ROWNUM_WITH_CAST and ROWNUM_WITHOUT_CAST have the same value;
--           CONCAT_CHECK values have increasing lengths.
-- Observed: ROWNUM_WITH_CAST and ROWNUM_WITHOUT_CAST have the different values;
--           CONCAT_CHECK values are all 16 elements long.
select A.A3 as A3_FROM_LHS,
       RHS.A3 as A3_FROM_RHS,
       row_number() over (order by cast(RHS.A3 as int)) as ROWNUM_WITH_CAST,
       row_number() over (order by RHS.A3) as ROWNUM_WITHOUT_CAST,
       group_concat('1') over (order by RHS.A3) as CONCAT_CHECK
from A inner join (select * from A) as RHS on A.A2 = RHS.A2
where A.A2 = 2 and A.A4 = 1
order by CONCAT_CHECK;

Workaround

A workaround is to use ORDER BY FALSE in a subselect in one table:

select A.A3 as A3_FROM_LHS,
       RHS.A3 as A3_FROM_RHS,
       row_number() over (order by cast(RHS.A3 as int)) as ROWNUM_WITH_CAST,
       row_number() over (order by RHS.A3) as ROWNUM_WITHOUT_CAST,
       group_concat('1') over (order by RHS.A3) as CONCAT_CHECK
from A inner join (select * from A ORDER BY FALSE) as RHS on A.A2 = RHS.A2
where A.A2 = 2 and A.A4 = 1
order by CONCAT_CHECK;

Fix

The query returns the correct results.