Submitted by: @dyemanov

Votes: 1

Test case:

recreate table document(id int primary key using index pk_document);
recreate table doc_ip_doc(id int primary key using index pk_doc_ip_doc, name varchar(100));

insert into document (id) select row_number() over() from rdb$types, rdb$types;
insert into doc_ip_doc (id) select row_number() over() from rdb$types;
commit;

set planonly;

select http://document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by doc_ip_doc.id;
--PLAN JOIN (DOC_IP_DOC ORDER PK_DOC_IP_DOC, DOCUMENT INDEX (PK_DOCUMENT))

select http://document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by http://document.id;
--PLAN SORT (JOIN (DOC_IP_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT)))

select doc_ip_doc.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by id;
--PLAN JOIN (DOC_IP_DOC ORDER PK_DOC_IP_DOC, DOCUMENT INDEX (PK_DOCUMENT))

select http://document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by id;
--PLAN SORT (JOIN (DOC_IP_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT)))

All queries are semantically the same and should have the same plan (ORDER rather than SORT), but the optimizer understands only explicit reference inside ORDER BY clause and ignores sorts derived from equivalent expressions.

Commits: 31c5a16 183398b