/ Check-in [af39661e]
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 | trunk
Files: files | file ages | folders
SHA3-256: af39661e60f562b9eb10343fd83e8fe21be4d7276111e7853c1179a24cab09ce
User & Date: drh 2018-11-05 07:53:17
Context
2018-11-05
16:38
Eponymous virtual tables appear to exist in all schemas. This is an alternative and improved fix to the eponymous virtual table in trigger problem that was previously addressed by checkin [1fa74930ab56171e]. check-in: b8d35c4a user: drh tags: trunk
13:53
Merge recent trunk enhancements. check-in: 62acf7a0 user: drh tags: apple-osx
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
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
2018-11-03
16:51
Add extra defenses against strategically corrupt databases to fts3/4. check-in: d44318f5 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

1378
1379
1380
1381
1382
1383
1384

1385
1386
1387
1388
1389
1390
1391
  ** virtual term of that form.
  **
  ** Note that the virtual term must be tagged with TERM_VNULL.
  */
  if( pExpr->op==TK_NOTNULL
   && pExpr->pLeft->op==TK_COLUMN
   && pExpr->pLeft->iColumn>=0

   && OptimizationEnabled(db, SQLITE_Stat34)
  ){
    Expr *pNewExpr;
    Expr *pLeft = pExpr->pLeft;
    int idxNew;
    WhereTerm *pNewTerm;








>







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

Changes to test/where.test.

1408
1409
1410
1411
1412
1413
1414
1415













1416
  SELECT * FROM t12 WHERE 
  a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10) 
  AND (b=1 OR c=1);
} {
  4 1 0
  4 0 1
}














finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>

1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
  SELECT * FROM t12 WHERE 
  a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10) 
  AND (b=1 OR c=1);
} {
  4 1 0
  4 0 1
}

# 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a]
# Incorrect result in LEFT JOIN when STAT4 is enabled.
#
sqlite3 db :memory:
do_execsql_test where-22.1 {
  CREATE TABLE t1(a INT);
  CREATE INDEX t1a ON t1(a);
  INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
  CREATE TABLE t2(dummy INT);
  SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;
} {5}


finish_test