Issue Details (XML | Word | Printable)

Key: CORE-3722
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
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
 

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: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 09/Jan/12 11:24 PM
Assigning to Dmitry accordingly to private conversation.

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.