Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Consider equivalence classes for index navigation [CORE5676] #5942

Closed
firebird-issue-importer opened this issue Dec 5, 2017 · 7 comments
Closed

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Dec 5, 2017

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 5, 2017

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 5, 2017

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 5, 2017

Commented by: @livius2

Great catch about optimization rule.

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 5, 2017

Commented by: @dyemanov

ID is treated as an alias and thus resolved using the select list, not all the tables involved.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 6, 2017

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.3 [ 10810 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 12, 2017

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 12, 2017

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants