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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Daniel Kamil Kozar Gesendet: Donnerstag, 02. Februar 2017 18:46 An: sqlite-users@mailinglists.sqlite.org 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@mailinglists.sqlite.org/msg62850.html - however, it does not offer an explanation why the implementation behaves this way. Kind regards, Daniel _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org 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: h...@scigames.at 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
