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