Fix wrong results with column from self-join used in window function PARTITION BY or ORDER BY

Details

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

Description

Queries with the following conditions can give wrong results:

  • A select has a self-join of a base table (e.g. select … from T, T as RHS;).
  • The base table cannot be replicated. (E.g. it has more rows than REPLICATION_BORDER).
  • 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 *).

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 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;

Another workaround is to create a separate database table and use that for the RHS:

create table B as select * from A;

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 B 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.