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

Unnecessary index scan happens when the same index is mapped to both WHERE and ORDER BY clauses [CORE1550] #1967

Closed
firebird-issue-importer opened this issue Oct 29, 2007 · 11 comments

Comments

@firebird-issue-importer

Submitted by: @dyemanov

Is related to QA535

Votes: 1

Test case:

select *
from rdb$relations
where rdb$relation_id < 10
order by rdb$relation_id

PLAN (RDB$RELATIONS ORDER RDB$INDEX_1 INDEX (RDB$INDEX_1))

But it should be:
PLAN (RDB$RELATIONS ORDER RDB$INDEX_1)

Because the WHERE condition should just set the upper threshold key and thus limit the navigational scan. A prior regular index scan is completely redundant in this case.

Generally speaking, the same index should never appear in both ORDER and INDEX parts of the same plan item. This particular issue is a regression introduced in v2.0, however other aspects of this bug are visible in all FB versions.

Commits: 263b396 a5cde2b 74fbaf5 c65e55c FirebirdSQL/fbt-repository@ff96c7e FirebirdSQL/fbt-repository@920cc2b FirebirdSQL/fbt-repository@bbbf0b8 FirebirdSQL/fbt-repository@d4c51e1 FirebirdSQL/fbt-repository@b2d0198

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 12, 2007

Modified by: @dyemanov

Fix Version: 2.1 RC1 [ 10201 ] =>

Fix Version: 2.0.4 [ 10211 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

Workflow: jira [ 13358 ] => Firebird [ 14268 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 24, 2009

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 10, 2010

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2010

Modified by: @dyemanov

priority: Major [ 3 ] => Minor [ 4 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2010

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2010

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA535 [ QA535 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 25, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 25, 2015

Modified by: @pcisar

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

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