Submitted by: Alice F. Bird (firebirds)

Assigned to: @ArnoBrinkman

SFID: 414420#⁠
Submitted By: nobody

InterBase will not sort correctly the result set of a table joined with a stored procedure (inner join),
if an ascending sort on an indexed field is requested.

To expose the bug we need to join a table with a stored procedure; here's the test metadata
(tested with dialect 1):

CREATE TABLE MY_TABLE
(
K VARCHAR(10) NOT NULL,
D1 INTEGER,
D2 INTEGER,
V1 VARCHAR(10),
PRIMARY KEY (K)
);

CREATE PROCEDURE SELECT_ME
RETURNS
(
DATA VARCHAR(10)
)
AS
begin
data = 'one';
suspend;
data = 'two';
suspend;
data = 'three';
suspend;
end

Let's populate my_table with some test data:

insert into my_table values ('one', 1, 99, 'zz');
insert into my_table values ('two', 2, 98, 'yy');
insert into my_table values ('three', 3, 97, 'xx');

The following query exposes the bug:

select *
from my_table t join select_me p
on (t.k = p.data)
order by t.d1

The ordering is correct on every field we put in the ORDER BY clause, UNLESS that field is also
indexed. Here are the correct results of the query:

K D1 D2 V1 DATA
========== ============ ============ ========== ==========
one 1 99 zz one
two 2 98 yy two
three 3 97 xx three

If we now do:

CREATE INDEX I1 ON MY_TABLE(D1);

the same query returns the following results:

K D1 D2 V1 DATA
========== ============ ============ ========== ==========
one 1 99 zz one
three 3 97 xx three
two 2 98 yy two

In short, if an inner join between a table and a stored procedure is performed, ordering will work
only on non-indexed fields. Please note that:
- if we do a left join instead the problem disappears.
- if we remove the index the problem also disappears.
- if we order by t.k (the table's primary key) the results are always correct.
- if we request a descending order the problem also disappears.