Issue Details (XML | Word | Printable)

Key: CORE-3318
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Maxim Kuzmin
Votes: 1
Watchers: 1

If you were logged in you would be able to see more operations.
Firebird Core

Using property NOT NULL of field when IS NULL/NOT NULL condition

Created: 28/Jan/11 01:32 PM   Updated: 28/Jan/11 02:30 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None

 Description  « Hide
Now, if the field SOMEFIELD declared as NOT NULL, when using conditions WHERE SOMEFIELD IS NULL / NOT NULL, will be scanned the entire table (NATURAL) or used index scan. But, obviously, that:
1. Provided WHERE SOMEFIELD IS NULL, will selected 0 records.
2. Provided WHERE SOMEFIELD IS NOT NULL, will selected all records.
Need using this info at creating the execution plan and at executing query.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 28/Jan/11 01:45 PM
Currently SELECT WHERE field IS NULL is the only way to discover corrupted records, which contain NULLs in not nullable field. If optimizer use the rule, there will be no way to fix such broken databases.

Dmitry Yemanov added a comment - 28/Jan/11 02:02 PM
Dimitry, do you know any way to store NULL into a not nullable field in Firebird 3?

Adriano dos Santos Fernandes added a comment - 28/Jan/11 02:19 PM
Dimitry, if this improvement is done, you could still check data validity with something like field || '' is null

Dmitry Yemanov added a comment - 28/Jan/11 02:30 PM
Adriano, in fact it depends on implementation :-) The requester wants to avoid an index scan for IS NULL, i.e. make this operation a complete no-op. So adding || '' shouldn't make any difference, as it's not about using an index or not.