/ Check-in [543f75a6]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Always make sure the left-hand side of the IS NOT NULL operator is a simple column, not a general expression, before applying the IS NOT NULL query optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 543f75a6abe3aa4f49df43e71a462702d40b3251
User & Date: drh 2011-04-08 21:35:26
Original Comment: Always make sure the left-hand side of the IS NOT NULL operator is a smiple column, not a general expression, before applying the IS NOT NULL query optimization.
References
2011-04-08
23:04
Make sure the left-hand side of the IS NOT NULL operator is a simple column and not a general expression before applying the IS NOT NULL optimization. This is a backport of check-in [543f75a6abe3]. check-in: e8177e01 user: drh tags: branch-3.7.2
Context
2011-04-09
02:09
Do not do a backup if the number of reserved bytes in the source and destination do not match. Try to make the match, but if unable fail. check-in: 0ca8a233 user: drh tags: trunk
2011-04-08
21:35
Always make sure the left-hand side of the IS NOT NULL operator is a simple column, not a general expression, before applying the IS NOT NULL query optimization. check-in: 543f75a6 user: drh tags: trunk
18:47
Add some rtree tests to the backcompat.test script. check-in: 8ea3601c user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

1338
1339
1340
1341
1342
1343
1344


1345

1346
1347
1348
1349
1350
1351
1352
  ** virtual term of that form.
  **
  ** Note that the virtual term must be tagged with TERM_VNULL.  This
  ** TERM_VNULL tag will suppress the not-null check at the beginning
  ** of the loop.  Without the TERM_VNULL flag, the not-null check at
  ** the start of the loop will prevent any results from being returned.
  */


  if( pExpr->op==TK_NOTNULL && pExpr->pLeft->iColumn>=0 ){

    Expr *pNewExpr;
    Expr *pLeft = pExpr->pLeft;
    int idxNew;
    WhereTerm *pNewTerm;

    pNewExpr = sqlite3PExpr(pParse, TK_GT,
                            sqlite3ExprDup(db, pLeft, 0),







>
>
|
>







1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
  ** virtual term of that form.
  **
  ** Note that the virtual term must be tagged with TERM_VNULL.  This
  ** TERM_VNULL tag will suppress the not-null check at the beginning
  ** of the loop.  Without the TERM_VNULL flag, the not-null check at
  ** the start of the loop will prevent any results from being returned.
  */
  if( pExpr->op==TK_NOTNULL
   && pExpr->pLeft->op==TK_COLUMN
   && pExpr->pLeft->iColumn>=0
  ){
    Expr *pNewExpr;
    Expr *pLeft = pExpr->pLeft;
    int idxNew;
    WhereTerm *pNewTerm;

    pNewExpr = sqlite3PExpr(pParse, TK_GT,
                            sqlite3ExprDup(db, pLeft, 0),

Changes to test/analyze5.test.

205
206
207
208
209
210
211


212
213
214
215
216
217
218
#
foreach {testid where index rows} {
  500  {x IS NULL AND u='charlie'}         t1u  20
  501  {x=1 AND u='charlie'}               t1x   5
  502  {x IS NULL}                          {} 100
  503  {x=1}                               t1x  50
  504  {x IS NOT NULL}                     t1x  25



} {
  # Verify that the expected index is used with the expected row count
  do_test analyze5-1.${testid}a {
    set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
    set idx {}
    regexp {INDEX (t1.) } $x all idx







>
>







205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
#
foreach {testid where index rows} {
  500  {x IS NULL AND u='charlie'}         t1u  20
  501  {x=1 AND u='charlie'}               t1x   5
  502  {x IS NULL}                          {} 100
  503  {x=1}                               t1x  50
  504  {x IS NOT NULL}                     t1x  25
  505  {+x IS NOT NULL}                     {} 500
  506  {upper(x) IS NOT NULL}               {} 500

} {
  # Verify that the expected index is used with the expected row count
  do_test analyze5-1.${testid}a {
    set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
    set idx {}
    regexp {INDEX (t1.) } $x all idx