/ Check-in [8d09ce5d]
Login

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

Overview
Comment:Disable the IS NOT NULL optimization when the IS NOT NULL operator is part of the ON clause of a LEFT JOIN. Fix for ticket [65eb38f6e46de8c75e188a17ec].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.25
Files: files | file ages | folders
SHA3-256: 8d09ce5d738bb00c4524c663ec69555fe917ac733d58b06a302675ce7fe33fca
User & Date: drh 2018-11-05 13:48:50
Context
2018-11-05
20:37
Version 3.25.3 check-in: 89e099fb user: drh tags: release, version-3.25.3, branch-3.25
13:48
Disable the IS NOT NULL optimization when the IS NOT NULL operator is part of the ON clause of a LEFT JOIN. Fix for ticket [65eb38f6e46de8c75e188a17ec]. check-in: 8d09ce5d user: drh tags: branch-3.25
13:43
Add extra defenses against strategically corrupt databases to fts3/4. check-in: 940f2adc user: drh tags: branch-3.25
07:53
Disable the IS NOT NULL optimization when the IS NOT NULL operator is part of the ON clause of a LEFT JOIN. Fix for ticket [65eb38f6e46de8c75e188a17ec]. check-in: af39661e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

  1378   1378     ** virtual term of that form.
  1379   1379     **
  1380   1380     ** Note that the virtual term must be tagged with TERM_VNULL.
  1381   1381     */
  1382   1382     if( pExpr->op==TK_NOTNULL
  1383   1383      && pExpr->pLeft->op==TK_COLUMN
  1384   1384      && pExpr->pLeft->iColumn>=0
         1385  +   && !ExprHasProperty(pExpr, EP_FromJoin)
  1385   1386      && OptimizationEnabled(db, SQLITE_Stat34)
  1386   1387     ){
  1387   1388       Expr *pNewExpr;
  1388   1389       Expr *pLeft = pExpr->pLeft;
  1389   1390       int idxNew;
  1390   1391       WhereTerm *pNewTerm;
  1391   1392   

Changes to test/where.test.

  1408   1408     SELECT * FROM t12 WHERE 
  1409   1409     a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10) 
  1410   1410     AND (b=1 OR c=1);
  1411   1411   } {
  1412   1412     4 1 0
  1413   1413     4 0 1
  1414   1414   }
         1415  +
         1416  +# 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a]
         1417  +# Incorrect result in LEFT JOIN when STAT4 is enabled.
         1418  +#
         1419  +sqlite3 db :memory:
         1420  +do_execsql_test where-22.1 {
         1421  +  CREATE TABLE t1(a INT);
         1422  +  CREATE INDEX t1a ON t1(a);
         1423  +  INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
         1424  +  CREATE TABLE t2(dummy INT);
         1425  +  SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;
         1426  +} {5}
         1427  +
  1415   1428   
  1416   1429   finish_test