/ Check-in [96ff2ba9]
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:Fix other problems similar to ticket [c0390363].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 96ff2ba9c4bb71d5f7c6f359986a76a5364b7ac3e1a612441543a9eabecf31df
User & Date: dan 2019-08-29 19:34:29
Context
2019-08-29
21:16
Improve vdbe branch coverage of NULLS LAST code. check-in: e8e9f77d user: dan tags: trunk
19:34
Fix other problems similar to ticket [c0390363]. check-in: 96ff2ba9 user: dan tags: trunk
16:48
Add a missing VdbeCoverage() macro. check-in: 33da6092 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

5010
5011
5012
5013
5014
5015
5016






5017
5018
5019
5020
5021
5022
5023
5024
5025
5026
5027
5028
5029
5030
5031
....
5098
5099
5100
5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
....
5124
5125
5126
5127
5128
5129
5130












5131
5132
5133
5134
5135
5136
5137
....
5144
5145
5146
5147
5148
5149
5150

5151
5152
5153
5154
5155
5156
5157
    case TK_NE:
    case TK_LT:
    case TK_LE:
    case TK_GT:
    case TK_GE:
    case TK_PLUS:
    case TK_MINUS:






    case TK_STAR:
    case TK_REM:
    case TK_BITAND:
    case TK_BITOR:
    case TK_SLASH:
    case TK_LSHIFT:
    case TK_RSHIFT: 
    case TK_CONCAT: {
      if( exprImpliesNotNull(pParse, p->pRight, pNN, iTab, seenNot) ) return 1;
      /* Fall thru into the next case */
    }
    case TK_SPAN:
    case TK_COLLATE:
    case TK_UPLUS:
    case TK_UMINUS: {
................................................................................
*/
static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
  testcase( pExpr->op==TK_AGG_COLUMN );
  testcase( pExpr->op==TK_AGG_FUNCTION );
  if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
  switch( pExpr->op ){
    case TK_ISNOT:
    case TK_NOT:
    case TK_BITNOT:
    case TK_ISNULL:
    case TK_NOTNULL:
    case TK_IS:
    case TK_OR:
    case TK_CASE:
    case TK_IN:
    case TK_FUNCTION:
................................................................................
    case TK_COLUMN:
      if( pWalker->u.iCur==pExpr->iTable ){
        pWalker->eCode = 1;
        return WRC_Abort;
      }
      return WRC_Prune;













    /* Virtual tables are allowed to use constraints like x=NULL.  So
    ** a term of the form x=y does not prove that y is not null if x
    ** is the column of a virtual table */
    case TK_EQ:
    case TK_NE:
    case TK_LT:
    case TK_LE:
................................................................................
      testcase( pExpr->op==TK_GT );
      testcase( pExpr->op==TK_GE );
      if( (pExpr->pLeft->op==TK_COLUMN && IsVirtual(pExpr->pLeft->y.pTab))
       || (pExpr->pRight->op==TK_COLUMN && IsVirtual(pExpr->pRight->y.pTab))
      ){
       return WRC_Prune;
      }

    default:
      return WRC_Continue;
  }
}

/*
** Return true (non-zero) if expression p can only be true if at least







>
>
>
>
>
>



<
|
<
<
<







 







<
<







 







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







 







>







5010
5011
5012
5013
5014
5015
5016
5017
5018
5019
5020
5021
5022
5023
5024
5025

5026



5027
5028
5029
5030
5031
5032
5033
....
5100
5101
5102
5103
5104
5105
5106


5107
5108
5109
5110
5111
5112
5113
....
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
....
5156
5157
5158
5159
5160
5161
5162
5163
5164
5165
5166
5167
5168
5169
5170
    case TK_NE:
    case TK_LT:
    case TK_LE:
    case TK_GT:
    case TK_GE:
    case TK_PLUS:
    case TK_MINUS:
    case TK_BITOR:
    case TK_LSHIFT:
    case TK_RSHIFT: 
    case TK_CONCAT: 
      seenNot = 1;
      /* Fall thru */
    case TK_STAR:
    case TK_REM:
    case TK_BITAND:

    case TK_SLASH: {



      if( exprImpliesNotNull(pParse, p->pRight, pNN, iTab, seenNot) ) return 1;
      /* Fall thru into the next case */
    }
    case TK_SPAN:
    case TK_COLLATE:
    case TK_UPLUS:
    case TK_UMINUS: {
................................................................................
*/
static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
  testcase( pExpr->op==TK_AGG_COLUMN );
  testcase( pExpr->op==TK_AGG_FUNCTION );
  if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
  switch( pExpr->op ){
    case TK_ISNOT:


    case TK_ISNULL:
    case TK_NOTNULL:
    case TK_IS:
    case TK_OR:
    case TK_CASE:
    case TK_IN:
    case TK_FUNCTION:
................................................................................
    case TK_COLUMN:
      if( pWalker->u.iCur==pExpr->iTable ){
        pWalker->eCode = 1;
        return WRC_Abort;
      }
      return WRC_Prune;

    case TK_AND:
      if( sqlite3ExprImpliesNonNullRow(pExpr->pLeft, pWalker->u.iCur)
       && sqlite3ExprImpliesNonNullRow(pExpr->pRight, pWalker->u.iCur)
      ){
        pWalker->eCode = 1;
      }
      return WRC_Prune;

    case TK_BETWEEN:
      sqlite3WalkExpr(pWalker, pExpr->pLeft);
      return WRC_Prune;

    /* Virtual tables are allowed to use constraints like x=NULL.  So
    ** a term of the form x=y does not prove that y is not null if x
    ** is the column of a virtual table */
    case TK_EQ:
    case TK_NE:
    case TK_LT:
    case TK_LE:
................................................................................
      testcase( pExpr->op==TK_GT );
      testcase( pExpr->op==TK_GE );
      if( (pExpr->pLeft->op==TK_COLUMN && IsVirtual(pExpr->pLeft->y.pTab))
       || (pExpr->pRight->op==TK_COLUMN && IsVirtual(pExpr->pRight->y.pTab))
      ){
       return WRC_Prune;
      }

    default:
      return WRC_Continue;
  }
}

/*
** Return true (non-zero) if expression p can only be true if at least

Changes to test/indexexpr2.test.

299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
















315
316












































317

reset_db
do_execsql_test 8.0 {
  CREATE TABLE t0(c0);
  CREATE INDEX i0 ON t0(c0) WHERE c0 NOT NULL;
  INSERT INTO t0(c0) VALUES (NULL);
}

breakpoint
do_execsql_test 8.1 {
  SELECT * FROM t0 WHERE ~('' BETWEEN t0.c0 AND TRUE);
} {{}}

do_execsql_test 8.2 {
  SELECT ~('' BETWEEN t0.c0 AND TRUE) FROM t0;
} {-1}































































finish_test








<
|


<
|



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|

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

>
299
300
301
302
303
304
305

306
307
308

309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
reset_db
do_execsql_test 8.0 {
  CREATE TABLE t0(c0);
  CREATE INDEX i0 ON t0(c0) WHERE c0 NOT NULL;
  INSERT INTO t0(c0) VALUES (NULL);
}


do_execsql_test 8.1.1 {
  SELECT * FROM t0 WHERE ~('' BETWEEN t0.c0 AND TRUE);
} {{}}

do_execsql_test 8.1.2 {
  SELECT ~('' BETWEEN t0.c0 AND TRUE) FROM t0;
} {-1}

foreach {tn expr} {
  1 " 0  ==  (34 BETWEEN c0 AND 33)"
  2 " 1  !=  (34 BETWEEN c0 AND 33)"
  3 "-1   <  (34 BETWEEN c0 AND 33)"
  4 "-1  <=  (34 BETWEEN c0 AND 33)"
  5 " 1   >  (34 BETWEEN c0 AND 33)"
  6 " 1  >=  (34 BETWEEN c0 AND 33)"
  7 " 1   -  (34 BETWEEN c0 AND 33)"
  8 "-1   +  (34 BETWEEN c0 AND 33)"
  9 " 1   |  (34 BETWEEN c0 AND 33)"
 10 " 1  <<  (34 BETWEEN c0 AND 33)"
 11 " 1  >>  (34 BETWEEN c0 AND 33)"
 12 " 1  ||  (34 BETWEEN c0 AND 33)"
} {
  do_execsql_test 8.3.$tn.1 "SELECT * FROM t0 WHERE $expr ORDER BY c0" { {} }
  do_execsql_test 8.3.$tn.2 "SELECT ($expr) IS TRUE FROM t0"           { 1 }
}

do_execsql_test 8.4 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2), (3, 4);
  CREATE TABLE t2(x, y);
}

foreach {tn expr} {
  1 " 0  ==  (a=0 AND y=1)"
  2 " 1  !=  (a=0 AND y=1)"
  3 "-1  <   (a=0 AND y=1)"
  4 "-1  <=  (a=0 AND y=1)"
  5 " 1   >  (a=0 AND y=1)"
  6 " 1  >=  (a=0 AND y=1)"
  7 " 1   -  (a=0 AND y=1)"
  8 "-1   +  (a=0 AND y=1)"
  9 " 1   |  (a=0 AND y=1)"
  10 "1  <<  (a=0 AND y=1)"
  11 "1  >>  (a=0 AND y=1)"
  12 "1  ||  (a=0 AND y=1)"

  13 " 0  ==  (10 BETWEEN y AND b)"
  14 " 1  !=  (10 BETWEEN y AND b)"
  15 "-1  <   (10 BETWEEN y AND b)"
  16 "-1  <=  (10 BETWEEN y AND b)"
  17 " 1   >  (10 BETWEEN y AND b)"
  18 " 1  >=  (10 BETWEEN y AND b)"
  19 " 1   -  (10 BETWEEN y AND b)"
  20 "-1   +  (10 BETWEEN y AND b)"
  21 " 1   |  (10 BETWEEN y AND b)"
  22 " 1  <<  (10 BETWEEN y AND b)"
  23 " 1  >>  (10 BETWEEN y AND b)"
  24 " 1  ||  (10 BETWEEN y AND b)"

  25 " 1  ||  (10 BETWEEN y AND b)"
} {
  do_execsql_test 8.5.$tn.1 "
    SELECT * FROM t1 LEFT JOIN t2 WHERE $expr
  " {1 2 {} {} 3 4 {} {}}

  do_execsql_test 8.5.$tn.2 "
    SELECT ($expr) IS TRUE FROM t1 LEFT JOIN t2
  " {1 1}
}

finish_test