Submitted by: @pavel-zotov

DDL:

recreate table thuge(id int, x int);
insert into thuge
select row_number()over(), rand()*10000
from rdb$types,rdb$types,rdb$types;
commit;
create index thuge_x on thuge(x);

recreate table ttiny(id int, v int, u int, w int);
insert into ttiny values(1, 100, 200, 300);
create index ttiny_id on ttiny(id);

commit;

Query-1:

select <http://h.id>, t\.u, t\.v, t\.w
from \(
    select <http://h.id>
    from thuge h
    order by x
    rows 1
\) h
cross join ttiny t;

Its plan:

Select Expression
-> Nested Loop Join (inner)
-> First N Records
-> Table "THUGE" as "H H" Access By ID
-> Index "THUGE_X" Full Scan
-> Table "RDB$DATABASE" Full Scan

This query returns one row instantly (<15ms, Fetches from cache = 18).

Query-2:

select <http://h.id>, t\.u, t\.v, t\.w
from thuge h
cross join ttiny t
order by h\.x
rows 1;

It has totally inefficient plan:

Select Expression
-> First N Records
-> Sort (record length: 76, key length: 8)
-> Nested Loop Join (inner)
-> Filter
-> Table "TTINY" as "T" Access By ID
-> Bitmap
-> Index "TTINY_ID" Range Scan (lower bound: 1/1, upper bound: 1/1)
-> Table "THUGE" as "H" Full Scan

Statistics:
Execute : 21 859,00 ms
Fetches: 32 991 255
NR for THuge = 16387064.

But both queries always will return the same data because ORDER BY clause does not contain any fields from TTINY table.