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

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: 13/Jul/15 12:29 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

Issue Links:

QA Status: Done successfully

 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;

select * from t where a is not distinct from null;

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('', '');

 All   Comments   Change History   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;

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.