Wrong result ordering in LEFT JOIN query

[lukaseder]

I'm using Firebird 4.0.2 from here: https://hub.docker.com/r/jacobalberty/firebird, according to:

select rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;

Take this schema:

create table b (x int primary key);
create table a (x int primary key, y int references b);
insert into b values (1);
insert into b values (2);
insert into b values (3);
insert into b values (4);
insert into a values (1, 1);
insert into a values (2, 1);
insert into a values (3, 4);
insert into a values (4, 2);

Now, run this query:

select a.x, b.x
from a
  left outer join b
    on (
      a.y = b.x
      and a.x = b.x
    )
order by a.x

It produces:

|X  |X  |
|---|---|
|1  |1  |
|2  |   |
|4  |   |
|3  |   |

Clearly, the ordering of the last two rows is wrong. It should be:

|X  |X  |
|---|---|
|1  |1  |
|2  |   |
|3  |   |
|4  |   |

[lukaseder]

This works around the issue:

select a.x, b.x
from a
  left outer join b
    on (
      a.y = b.x
      and a.x = b.x
    )
order by a.x + 1

[mrotteveel]

@dyemanov Are you sure 5.0 was affected as well, because I could not reproduce it on 5.0.0.1073.

[dyemanov]

@dyemanov Are you sure 5.0 was affected as well, because I could not reproduce it on 5.0.0.1073.

The code is the same, just slightly different index comparison logic is used (which index to use for ORDER BY). So this particular test works fine, but some different test may fail the same way.