Skip to content
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

Using property NOT NULL of field when IS NULL/NOT NULL condition [CORE3318] #3685

Open
firebird-automations opened this issue Jan 28, 2011 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Maxim Kuzmin (cybermax)

Votes: 1

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Dimitry, do you know any way to store NULL into a not nullable field in Firebird 3?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Dimitry, if this improvement is done, you could still check data validity with something like field || '' is null

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants