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-issue-importer opened this issue Jan 10, 2012 · 10 comments
Closed

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

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

Commented by: @asfernandes

Assigning to Dmitry accordingly to private conversation.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 16, 2012

Modified by: @pmakowski

Link: This issue relate to QA460 [ QA460 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 16, 2012

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 13, 2015

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