Issue Details (XML | Word | Printable)

Key: CORE-5676
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Dmitry Yemanov
Reporter: Dmitry Yemanov
Votes: 1
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Consider equivalence classes for index navigation

Created: 05/Dec/17 04:57 PM   Updated: 12/Dec/17 07:05 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 3.0.3, 4.0 Beta 1

QA Status: Done successfully


 Description  « Hide
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 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 document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by 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 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.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski added a comment - 05/Dec/17 06:13 PM
Great catch about optimization rule.

Minor question.
Can you tell me why "order by id" do not throw exeption about Ambiguous field names?

Dmitry Yemanov added a comment - 05/Dec/17 07:27 PM
ID is treated as an alias and thus resolved using the select list, not all the tables involved.