/ Check-in [0ba6d709]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Do not assume that "x IS NOT ?" implies "x NOT NULL" when considering partial indexes. Fix for ticket [8025674847].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0ba6d709b50d92db1542f2ff30535a80184b00dadf759d51e5cae7a6e37b1764
User & Date: dan 2019-05-11 13:04:33
References
2019-05-11
13:05 Closed ticket [80256748]: Incorrect use of "WHERE x NOT NULL" index for query with a "WHERE x IS NOT ?" term plus 4 other changes artifact: 6f695f82 user: dan
Context
2019-05-11
16:14
When considering partial indexes, do not assume that a "CASE x ..." expression implies "x IS NOT NULL". check-in: 1b243032 user: dan tags: trunk
13:04
Do not assume that "x IS NOT ?" implies "x NOT NULL" when considering partial indexes. Fix for ticket [8025674847]. check-in: 0ba6d709 user: dan tags: trunk
2019-05-10
20:44
Add tests for the RBU module. check-in: a194e536 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  4942   4942                || sqlite3ExprImpliesExpr(pParse, pE1, pE2->pRight, iTab) )
  4943   4943     ){
  4944   4944       return 1;
  4945   4945     }
  4946   4946     if( pE2->op==TK_NOTNULL
  4947   4947      && pE1->op!=TK_ISNULL
  4948   4948      && pE1->op!=TK_IS
         4949  +   && pE1->op!=TK_ISNOT
  4949   4950      && pE1->op!=TK_OR
  4950   4951     ){
  4951   4952       Expr *pX = sqlite3ExprSkipCollate(pE1->pLeft);
  4952   4953       testcase( pX!=pE1->pLeft );
  4953   4954       if( sqlite3ExprCompare(pParse, pX, pE2->pLeft, iTab)==0 ) return 1;
  4954   4955     }
  4955   4956     return 0;

Changes to test/index6.test.

   418    418   do_execsql_test index6-13.1 {
   419    419     DROP TABLE IF EXISTS t0;
   420    420     CREATE TABLE t0(c0);
   421    421     CREATE INDEX index_0 ON t0(c0) WHERE c0 NOT NULL;
   422    422     INSERT INTO t0(c0) VALUES (NULL);
   423    423     SELECT * FROM t0 WHERE c0 OR 1;
   424    424   } {{}}
          425  +
          426  +# 2019-05-11
          427  +# Ticket https://sqlite.org/src/tktview/8025674847
          428  +reset_db
          429  +do_execsql_test index6-14.1 {
          430  +  CREATE TABLE IF NOT EXISTS t0 (c0, c1);
          431  +  CREATE INDEX IF NOT EXISTS i0 ON t0(c0, c1) WHERE c0 NOT NULL;
          432  +  INSERT INTO t0(c0, c1) VALUES(NULL, 'row');
          433  +  SELECT * FROM t0 WHERE t0.c0 IS NOT 1;
          434  +} {{} row}
   425    435   
   426    436   finish_test
          437  +