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

Bad plan with the IN ( select ..from ) predicate [CORE2344] #2768

Closed
firebird-automations opened this issue Feb 25, 2009 · 6 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Derryck welas (welas)

Duplicates CORE1769

Attachments:
TEST.zip

When using a the folowing construction i get a bad plan:

SELECT TESTVAL,SOLD_CTRY FROM CTRY_TEST A WHERE SOLD_CTRY IN
(
SELECT CTRY_OBJID FROM CTRY_GROUP_COLLECTION B WHERE B.CTRY_GROUP_OBJID
IN (SELECT OBJID FROM CTRY_GROUP C WHERE C.GROUPCODE='AMERICAS')
)

PLAN (C INDEX (CTRY_GROUP_PK))
PLAN (B INDEX (CTRY_CODES_FK))
PLAN (A NATURAL)

The same result but with a join uses a more logical plan.

SELECT TESTVAL,SOLD_CTRY FROM CTRY_TEST A
JOIN CTRY_GROUP_COLLECTION B ON A.SOLD_CTRY=B.CTRY_OBJID
JOIN CTRY_GROUP C ON C.OBJID=B.CTRY_GROUP_OBJID AND C.GROUPCODE='AMERICAS'

PLAN JOIN (C INDEX (IDX_GROUPCODE), B INDEX (CTRY_GROUP_FK), A INDEX (CTRY_CODES_FK2))

this was tested on (2.5.0.22381 ) and 2.1.1

@firebird-automations
Copy link
Collaborator Author

Modified by: Derryck welas (welas)

Attachment: TEST.zip [ 11381 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Well, these two queries are not equivalent (the join one supposedly requires a DISCTINCT term), but I seem getting your point.

This is not a bug but rather a design limitation. The IN subqueries are always being joined to the parent ones, i.e. similar to A LEFT JOIN B LEFT JOIN C which also implies a pre-defined join order from the outer tables to the inner ones.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue duplicates CORE1769 [ CORE1769 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

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

1 participant