[#CORE-3722] IS NOT DISTINCT FROM NULL doesn't use index

Firebird Core

IS NOT DISTINCT FROM NULL doesn't use index

Created: 09/Jan/12 11:23 PM Updated: 16/May/12 12:51 PM

Component/s:

Engine

Affects Version/s:

3.0 Initial, 2.5.0, 2.1.4, 2.5.1

Fix Version/s:

2.1.5, 2.5.2, 3.0 Alpha 1

Time Tracking:

Not Specified

Issue Links:

Relate

This issue relate to:

~~QA-460~~ Test for CORE-3722

Planning Status:

Unspecified

Description

« Hide

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))

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Adriano dos Santos Fernandes added a comment - 09/Jan/12 11:24 PM

Assigning to Dmitry accordingly to private conversation.

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 10/Jan/12 05:22 AM

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.