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

IS NOT DISTINCT FROM NULL doesn't use index [CORE3722] #1397

Closed
firebird-automations opened this issue Jan 10, 2012 · 10 comments
Closed

IS NOT DISTINCT FROM NULL doesn't use index [CORE3722] #1397

firebird-automations opened this issue Jan 10, 2012 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Relate to QA460

Problem happens only with the NULL constant. An expression resulting in NULL works.

create table t (a varchar(5));
create index t_a on t (a);

select * from t where a is null;
PLAN (T INDEX (T_A))

select * from t where a is not distinct from null;
PLAN (T NATURAL)

select * from t where a is not distinct from null PLAN (T INDEX (T_A));
Statement failed, SQLSTATE = 42000
index T_A cannot be used in the specified plan

select * from t where a is not distinct from nullif('', '');
PLAN (T INDEX (T_A))

Commits: 4af9803 f529cbf 60aa013

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Assigning to Dmitry accordingly to private conversation.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Index is neither used for direct comparisons with NULLs, e.g.:

select * from t where a = null;
PLAN (T NATURAL)

Obviously, it must return an empty result set, but doing that via the index would be way faster than scanning the whole table.

The issue affects only non-numeric indexed columns.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.1.5 [ 10420 ]

Fix Version: 2.5.2 [ 10450 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

Link: This issue relate to QA460 [ QA460 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

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