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
