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
Comments
Commented by: @asfernandes Assigning to Dmitry accordingly to private conversation. |
Modified by: @asfernandesassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov Index is neither used for direct comparisons with NULLs, e.g.: select * from t where a = null; 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. |
Modified by: @dyemanovVersion: 2.5.1 [ 10333 ] Version: 2.1.4 [ 10361 ] Version: 2.5.0 [ 10221 ] Version: 3.0 Initial [ 10301 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovstatus: 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 ] |
Modified by: @pmakowski |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: