Thanks Jay, That's a good hint about the origin of the problem.
However, you refer to the sort order, but the problem is with WHERE statement. Since numeric comparison with NULL always evaluates to NULL (see section 4.0 of the link you gave me), a statement like "SELECT * FROM table WHERE value > NULL" would return an empty result set on any table -- *always* -- whereas "SELECT * FROM table WHERE value IS NOT NULL" would return all rows with non-null value. That works on the normal tables, but it's probably broken on the virtual tables in the latest version. I've checked what's happening on 3.7.4: when parsing "SELECT value FROM table WHERE value IS NOT NULL", the xBestIndex method receives no contraints, which, I believe, is the correct thing: pIdxInfo->nConstraint == 0 So - who else thinks it's a bug? Cheers Igor Jay A. Kreibich-2 wrote: > > On Mon, Aug 01, 2011 at 12:34:33PM -0700, Igor Sereda scratched on the > wall: >> >> Hello, >> >> I'm seeing strange input given into xBestIndex method of my virtual >> table. >> >> I'm maintaining sqlite4java wrapper and I'm trying to upgrade it from >> SQLite >> 3.7.4 to 3.7.7.1. A couple of failed tests uncovered that there's a >> problem >> when searching a simple virtual table with constraints that contain NULL. >> >> More specifically, the virtual table is declared as follows in xCreate >> method: >> >> CREATE TABLE x(value INTEGER) >> >> When the following SQL is executed: >> >> SELECT value FROM table WHERE value IS NOT NULL >> >> , xBestIndex receives the following parameters: >> >> pIdxInfo->nConstraint == 1 >> pIdxInfo->aConstraint[0].usable == 1 >> pIdxInfo->aConstraint[0].iColumn == 0 >> pIdxInfo->aConstraint[0].op == 4 (GT) >> >> So basically the search is going to be for condition "value > ?". >> >> When xFilter is called, the value passed is NULL. So instead of searching >> for "value IS NOT NULL" the module is instructed to search for "value > >> NULL" - which gives the opposite result. And when SQL executed is >> "SELECT >> value FROM table WHERE value > NULL", all the parameters are identical. > > All values in SQLite have a consistent sort order. As section 3.1 of > http://sqlite.org/datatype3.html#comparisons shows, NULL is considered > to be the "smallest" value. Hence, "value > NULL" is equivalent to > "value IS NOT NULL". > > It might not be the most obvious logic, but it is the logic used by > SQLite and the query optimizer, so it is the logic that needs to be > used by any virtual table. > >> This problem did not exist in SQLite 3.7.4. > > What did earlier versions do? > >> Do I miss something or is this a bug? > > I assume it is a change in the query optimizer. Since this is a > legit way to express an IS NOT NULL, it isn't exactly "wrong", just > different. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Virtual-Table-xBestIndex-and-NULL-Search-Conditions-%28Bug-%29-tp32172549p32173021.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
