/ Check-in [ea20068e]
Login

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

Overview
Comment:Fix a false-postive in the sqlite3ExprImpliesNonNullRow() decision routine, that resulted in an incorrect LEFT JOIN strength reduction when the WHERE clause contained a row-value comparison. Ticket [02aa2bd02f97d0f2]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ea20068e6d97c9349ebcc7d0a01e99ebf08c6f44363f71a0218a1abea209adc5
User & Date: drh 2019-11-04 02:05:52
Context
2019-11-04
12:49
Changes an unreachable testcase() into an assert(). check-in: 5710845b user: drh tags: trunk
02:05
Fix a false-postive in the sqlite3ExprImpliesNonNullRow() decision routine, that resulted in an incorrect LEFT JOIN strength reduction when the WHERE clause contained a row-value comparison. Ticket [02aa2bd02f97d0f2] check-in: ea20068e user: drh tags: trunk
2019-11-03
00:07
The optimization of check-in [9b2879629c34fc0a] is incorrectly reasoned. The WHERE clause of the partial index might not be true if the table of the partial index is the right table of a left join. So disable the optimization in that case. Ticket [623eff57e76d45f6] check-in: 3be19e11 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

5208
5209
5210
5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
....
5226
5227
5228
5229
5230
5231
5232

5233
5234
5235
5236
5237
5238
5239
5240
5241

5242
5243
5244
5245
5246
5247
5248
  ){
    return 1;
  }
  return 0;
}

/*
** This is the Expr node callback for sqlite3ExprImpliesNotNullRow().
** If the expression node requires that the table at pWalker->iCur
** have one or more non-NULL column, then set pWalker->eCode to 1 and abort.
**
** This routine controls an optimization.  False positives (setting
** pWalker->eCode to 1 when it should not be) are deadly, but false-negatives
** (never setting pWalker->eCode) is a harmless missed optimization.
*/
................................................................................
  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_TRUTH:
      testcase( pExpr->op==TK_ISNOT );
      testcase( pExpr->op==TK_ISNULL );
      testcase( pExpr->op==TK_NOTNULL );
      testcase( pExpr->op==TK_IS );
      testcase( pExpr->op==TK_OR );

      testcase( pExpr->op==TK_CASE );
      testcase( pExpr->op==TK_IN );
      testcase( pExpr->op==TK_FUNCTION );
      testcase( pExpr->op==TK_TRUTH );
      return WRC_Prune;
    case TK_COLUMN:
      if( pWalker->u.iCur==pExpr->iTable ){







|







 







>









>







5208
5209
5210
5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
....
5226
5227
5228
5229
5230
5231
5232
5233
5234
5235
5236
5237
5238
5239
5240
5241
5242
5243
5244
5245
5246
5247
5248
5249
5250
  ){
    return 1;
  }
  return 0;
}

/*
** This is the Expr node callback for sqlite3ExprImpliesNonNullRow().
** If the expression node requires that the table at pWalker->iCur
** have one or more non-NULL column, then set pWalker->eCode to 1 and abort.
**
** This routine controls an optimization.  False positives (setting
** pWalker->eCode to 1 when it should not be) are deadly, but false-negatives
** (never setting pWalker->eCode) is a harmless missed optimization.
*/
................................................................................
  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_VECTOR:
    case TK_CASE:
    case TK_IN:
    case TK_FUNCTION:
    case TK_TRUTH:
      testcase( pExpr->op==TK_ISNOT );
      testcase( pExpr->op==TK_ISNULL );
      testcase( pExpr->op==TK_NOTNULL );
      testcase( pExpr->op==TK_IS );
      testcase( pExpr->op==TK_OR );
      testcase( pExpr->op==TK_VECTOR );
      testcase( pExpr->op==TK_CASE );
      testcase( pExpr->op==TK_IN );
      testcase( pExpr->op==TK_FUNCTION );
      testcase( pExpr->op==TK_TRUTH );
      return WRC_Prune;
    case TK_COLUMN:
      if( pWalker->u.iCur==pExpr->iTable ){

Changes to test/rowvalue.test.

611
612
613
614
615
616
617






















618
619
620
  SELECT ('B', 0)> (t0.c0 COLLATE nocase, 0) FROM t0;
  SELECT 2 FROM t0 WHERE ('B', 0)> (t0.c0 COLLATE nocase, 0);
} {1 2}
do_execsql_test 25.40 {
  SELECT (t0.c0 COLLATE nocase, 0) < ('B', 0) FROM t0;
  SELECT 2 FROM t0 WHERE (t0.c0 COLLATE nocase, 0) < ('B', 0);
} {1 2}
























finish_test







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



611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
  SELECT ('B', 0)> (t0.c0 COLLATE nocase, 0) FROM t0;
  SELECT 2 FROM t0 WHERE ('B', 0)> (t0.c0 COLLATE nocase, 0);
} {1 2}
do_execsql_test 25.40 {
  SELECT (t0.c0 COLLATE nocase, 0) < ('B', 0) FROM t0;
  SELECT 2 FROM t0 WHERE (t0.c0 COLLATE nocase, 0) < ('B', 0);
} {1 2}

# 2019-11-04 Ticket 02aa2bd02f97d0f2
# The TK_VECTOR operator messes up sqlite3ExprImpliesNonNull() which
# causes incorrect LEFT JOIN strength reduction.  TK_VECTOR should be
# treated the same as TK_OR.
#
db close
sqlite3 db :memory:
do_execsql_test 26.10 {
  CREATE TABLE t0(c0);
  CREATE TABLE t1(c1);
  INSERT INTO t1(c1) VALUES (0);
  SELECT (c0, x'') != (NULL, 0) FROM t1 LEFT JOIN t0;
} {1}
do_execsql_test 26.20 {
  SELECT 2 FROM t1 LEFT JOIN t0 ON (c0, x'') != (NULL, 0);
} {2}
do_execsql_test 26.30 {
  SELECT 3 FROM t1 LEFT JOIN t0 WHERE (c0, x'') != (NULL, 0);
} {3}




finish_test