Quantcast

IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Daniel Kamil Kozar
Hi.
I'd like to ask why using a IS NULL or IS NOT NULL constraint in a
query made to a virtual table does not result in these constraints
being present in the sqlite3_index_info structure passed to the
virtual table's xBestIndex.
Currently, using one of these constraints results in no constraints at
all being passed, thus forcing the virtual table to use the most
"generic" index for accessing it. This seems like an omission to me,
since virtual tables could very well provide their own handling for
this special kind of constraint, without having to revert to a full
scan of the table just so that SQLite can look for rows that satisfy
this constraint.
Interestingly, specifying a = NULL constraint in a query results in
the constraint being passed to xBestIndex. This is curious, since - at
least to my understanding - no two NULLs are supposed to compare
equal, and SQLite behaves just like that when using its normal table
implementations : running a query with = NULL gives an empty result,
while IS NULL gives the expected result.
I modified src/test_intarray.c in order for it to be self-contained
and make some pre-defined queries which show what I'm talking about.
The source is available here :
https://gist.githubusercontent.com/xavery/976981b84d3bba90ef6bb480adc7a16d/raw/61ed629ef3dc4af0735e50ad40d33c16c7d11a13/sqlite-vtab-isnull.c
I could only find one thread which also deals with this issue :
http://www.mail-archive.com/sqlite-users@.../msg62850.html
- however, it does not offer an explanation why the implementation
behaves this way.

Kind regards,
Daniel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Clemens Ladisch
Daniel Kamil Kozar wrote:
> I'd like to ask why using a IS NULL or IS NOT NULL constraint in a
> query made to a virtual table does not result in these constraints
> being present in the sqlite3_index_info structure passed to the
> virtual table's xBestIndex.

Because there is no SQLITE_INDEX_CONSTRAINT_IS.

> I could only find one thread which also deals with this issue :
> http://www.mail-archive.com/sqlite-users@.../msg62850.html

As shown there, "x IS NOT NULL" originally ended up as the constraint
"x > NULL", but this whan then considered a bug.

The FTS and R-tree virtual tables do not contain NULL values, so
I guess it was never seen as necessary.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Hick Gunter
In reply to this post by Daniel Kamil Kozar
The supported constraints are:

#define SQLITE_INDEX_CONSTRAINT_EQ      2
#define SQLITE_INDEX_CONSTRAINT_GT      4
#define SQLITE_INDEX_CONSTRAINT_LE      8
#define SQLITE_INDEX_CONSTRAINT_LT     16
#define SQLITE_INDEX_CONSTRAINT_GE     32
#define SQLITE_INDEX_CONSTRAINT_MATCH  64
#define SQLITE_INDEX_CONSTRAINT_LIKE   65     /* 3.10.0 and later only */
#define SQLITE_INDEX_CONSTRAINT_GLOB   66     /* 3.10.0 and later only */
#define SQLITE_INDEX_CONSTRAINT_REGEXP 67     /* 3.10.0 and later only */

Notice that there is no SQLITE_INDEX_CONSTRAINT_NULL. You can of course implement that "= NULL" works like "IS NULL" on your virtual table, but that does result in some strange looking SQL, and you have to be sure to tell SQLite to omit it's own constraint check. Alternatively, consider computed virtual fields that will return 0 or 1 depending on the base field.

In your table declaration, add <field>_IS_NULL INTEGER for all fields that need to support this.

SELECT ... FROM your_table WHERE ...<field>_IS_NULL = 1 ...

This should make SQLite pass the constraint to xBestIndex and the value (0 or 1, depending if you need IS NULL or NOT NULL) to xFilter.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Daniel Kamil Kozar
Gesendet: Donnerstag, 02. Februar 2017 18:46
An: [hidden email]
Betreff: [sqlite] IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Hi.
I'd like to ask why using a IS NULL or IS NOT NULL constraint in a query made to a virtual table does not result in these constraints being present in the sqlite3_index_info structure passed to the virtual table's xBestIndex.
Currently, using one of these constraints results in no constraints at all being passed, thus forcing the virtual table to use the most "generic" index for accessing it. This seems like an omission to me, since virtual tables could very well provide their own handling for this special kind of constraint, without having to revert to a full scan of the table just so that SQLite can look for rows that satisfy this constraint.
Interestingly, specifying a = NULL constraint in a query results in the constraint being passed to xBestIndex. This is curious, since - at least to my understanding - no two NULLs are supposed to compare equal, and SQLite behaves just like that when using its normal table implementations : running a query with = NULL gives an empty result, while IS NULL gives the expected result.
I modified src/test_intarray.c in order for it to be self-contained and make some pre-defined queries which show what I'm talking about.
The source is available here :
https://gist.githubusercontent.com/xavery/976981b84d3bba90ef6bb480adc7a16d/raw/61ed629ef3dc4af0735e50ad40d33c16c7d11a13/sqlite-vtab-isnull.c
I could only find one thread which also deals with this issue :
http://www.mail-archive.com/sqlite-users@.../msg62850.html
- however, it does not offer an explanation why the implementation behaves this way.

Kind regards,
Daniel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Daniel Kamil Kozar
Thanks a ton for the replies. This pretty much confirms what I've been
suspecting.

On 3 February 2017 at 11:24, Clemens Ladisch <[hidden email]> wrote:
> The FTS and R-tree virtual tables do not contain NULL values, so
> I guess it was never seen as necessary.

Does this make it an accidental omission, then? Would you guys be open
to implementing this type of constraint in the virtual table
interface?

On 3 February 2017 at 11:26, Hick Gunter <[hidden email]> wrote:
> Notice that there is no SQLITE_INDEX_CONSTRAINT_NULL. You can of course implement that "= NULL" works like "IS NULL" on your virtual table, but that does result in some strange looking SQL, and you have to be sure to tell SQLite to omit it's own constraint check. Alternatively, consider computed virtual fields that will return 0 or 1 depending on the base field.
>
> In your table declaration, add <field>_IS_NULL INTEGER for all fields that need to support this.
>
> SELECT ... FROM your_table WHERE ...<field>_IS_NULL = 1 ...
>
> This should make SQLite pass the constraint to xBestIndex and the value (0 or 1, depending if you need IS NULL or NOT NULL) to xFilter.

I guess that would work. Thanks for the suggestion.

Kind regards,
Daniel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...