SQLite

Check-in [a4fc98113a]
Login

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

Overview
Comment:Fix another problem on this branch.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | defer-where-subqueries
Files: files | file ages | folders
SHA3-256: a4fc98113aeb10860834f68a2fdcef690ea15d8303d23b6dd416994a4b7edab6
User & Date: dan 2017-07-10 15:17:30.050
Context
2017-07-10
15:26
Small performance optimization in sqlite3WhereExprUsage(). (check-in: 38edc6770e user: drh tags: defer-where-subqueries)
15:17
Fix another problem on this branch. (check-in: a4fc98113a user: dan tags: defer-where-subqueries)
14:39
Fix a problem causing non-covered WHERE terms to be evaluated before covered WHERE terms. (check-in: 7d3cb39f60 user: dan tags: defer-where-subqueries)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/sqliteInt.h.
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
#define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias     0x400000 /* Is an alias for a result set column */
#define EP_Leaf      0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */

/*
** Combinations of two or more EP_* flags
*/
#define EP_Propagate (EP_Collate|EP_Subquery|EP_VarSelect) /* Prop. up tree */

/*
** These macros can be used to test, set, or clear bits in the
** Expr.flags field.
*/
#define ExprHasProperty(E,P)     (((E)->flags&(P))!=0)
#define ExprHasAllProperty(E,P)  (((E)->flags&(P))==(P))







|







2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
#define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias     0x400000 /* Is an alias for a result set column */
#define EP_Leaf      0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */

/*
** Combinations of two or more EP_* flags
*/
#define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */

/*
** These macros can be used to test, set, or clear bits in the
** Expr.flags field.
*/
#define ExprHasProperty(E,P)     (((E)->flags&(P))!=0)
#define ExprHasAllProperty(E,P)  (((E)->flags&(P))==(P))
Changes to src/whereInt.h.
280
281
282
283
284
285
286

287
288
289
290
291
292
293
#else
#  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
#endif
#define TERM_LIKEOPT    0x100  /* Virtual terms from the LIKE optimization */
#define TERM_LIKECOND   0x200  /* Conditionally this LIKE operator term */
#define TERM_LIKE       0x400  /* The original LIKE operator */
#define TERM_IS         0x800  /* Term.pExpr is an IS operator */


/*
** An instance of the WhereScan object is used as an iterator for locating
** terms in the WHERE clause that are useful to the query planner.
*/
struct WhereScan {
  WhereClause *pOrigWC;      /* Original, innermost WhereClause */







>







280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
#else
#  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
#endif
#define TERM_LIKEOPT    0x100  /* Virtual terms from the LIKE optimization */
#define TERM_LIKECOND   0x200  /* Conditionally this LIKE operator term */
#define TERM_LIKE       0x400  /* The original LIKE operator */
#define TERM_IS         0x800  /* Term.pExpr is an IS operator */
#define TERM_VARSELECT  0x1000 /* Term.pExpr contains a correlated sub-query */

/*
** An instance of the WhereScan object is used as an iterator for locating
** terms in the WHERE clause that are useful to the query planner.
*/
struct WhereScan {
  WhereClause *pOrigWC;      /* Original, innermost WhereClause */
369
370
371
372
373
374
375

376
377
378
379
380
381
382
** above, the mapping might go like this:  4->3, 5->1, 8->2, 29->0,
** 57->5, 73->4.  Or one of 719 other combinations might be used. It
** does not really matter.  What is important is that sparse cursor
** numbers all get mapped into bit numbers that begin with 0 and contain
** no gaps.
*/
struct WhereMaskSet {

  int n;                        /* Number of assigned cursor values */
  int ix[BMS];                  /* Cursor assigned to each bit */
};

/*
** Initialize a WhereMaskSet object
*/







>







370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
** above, the mapping might go like this:  4->3, 5->1, 8->2, 29->0,
** 57->5, 73->4.  Or one of 719 other combinations might be used. It
** does not really matter.  What is important is that sparse cursor
** numbers all get mapped into bit numbers that begin with 0 and contain
** no gaps.
*/
struct WhereMaskSet {
  int bVarSelect;               /* Used by sqlite3WhereExprUsage() */
  int n;                        /* Number of assigned cursor values */
  int ix[BMS];                  /* Cursor assigned to each bit */
};

/*
** Initialize a WhereMaskSet object
*/
Changes to src/wherecode.c.
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
        continue;
      }
      
      if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
        iNext = 2;
        continue;
      }
      if( iLoop<3 && (pE->flags & EP_VarSelect) ){
        if( iNext==0 ) iNext = 3;
        continue;
      }

      if( pTerm->wtFlags & TERM_LIKECOND ){
        /* If the TERM_LIKECOND flag is set, that means that the range search
        ** is sufficient to guarantee that the LIKE operator is true, so we







|







2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
        continue;
      }
      
      if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
        iNext = 2;
        continue;
      }
      if( iLoop<3 && (pTerm->wtFlags & TERM_VARSELECT) ){
        if( iNext==0 ) iNext = 3;
        continue;
      }

      if( pTerm->wtFlags & TERM_LIKECOND ){
        /* If the TERM_LIKECOND flag is set, that means that the range search
        ** is sufficient to guarantee that the LIKE operator is true, so we
Changes to src/whereexpr.c.
947
948
949
950
951
952
953

954

955
956
957
958
959
960
961
      pTerm->prereqRight = sqlite3WhereExprListUsage(pMaskSet, pExpr->x.pList);
    }
  }else if( op==TK_ISNULL ){
    pTerm->prereqRight = 0;
  }else{
    pTerm->prereqRight = sqlite3WhereExprUsage(pMaskSet, pExpr->pRight);
  }

  prereqAll = sqlite3WhereExprUsage(pMaskSet, pExpr);

  if( ExprHasProperty(pExpr, EP_FromJoin) ){
    Bitmask x = sqlite3WhereGetMask(pMaskSet, pExpr->iRightJoinTable);
    prereqAll |= x;
    extraRight = x-1;  /* ON clause terms may not be used with an index
                       ** on left table of a LEFT JOIN.  Ticket #3015 */
    if( (prereqAll>>1)>=x ){
      sqlite3ErrorMsg(pParse, "ON clause references tables to its right");







>

>







947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
      pTerm->prereqRight = sqlite3WhereExprListUsage(pMaskSet, pExpr->x.pList);
    }
  }else if( op==TK_ISNULL ){
    pTerm->prereqRight = 0;
  }else{
    pTerm->prereqRight = sqlite3WhereExprUsage(pMaskSet, pExpr->pRight);
  }
  pMaskSet->bVarSelect = 0;
  prereqAll = sqlite3WhereExprUsage(pMaskSet, pExpr);
  if( pMaskSet->bVarSelect ) pTerm->wtFlags |= TERM_VARSELECT;
  if( ExprHasProperty(pExpr, EP_FromJoin) ){
    Bitmask x = sqlite3WhereGetMask(pMaskSet, pExpr->iRightJoinTable);
    prereqAll |= x;
    extraRight = x-1;  /* ON clause terms may not be used with an index
                       ** on left table of a LEFT JOIN.  Ticket #3015 */
    if( (prereqAll>>1)>=x ){
      sqlite3ErrorMsg(pParse, "ON clause references tables to its right");
1381
1382
1383
1384
1385
1386
1387

1388
1389
1390
1391
1392
1393
1394
    return sqlite3WhereGetMask(pMaskSet, p->iTable);
  }
  mask = (p->op==TK_IF_NULL_ROW) ? sqlite3WhereGetMask(pMaskSet, p->iTable) : 0;
  assert( !ExprHasProperty(p, EP_TokenOnly) );
  if( p->pRight ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pRight);
  if( p->pLeft ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pLeft);
  if( ExprHasProperty(p, EP_xIsSelect) ){

    mask |= exprSelectUsage(pMaskSet, p->x.pSelect);
  }else if( p->x.pList ){
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->x.pList);
  }
  return mask;
}
Bitmask sqlite3WhereExprListUsage(WhereMaskSet *pMaskSet, ExprList *pList){







>







1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
    return sqlite3WhereGetMask(pMaskSet, p->iTable);
  }
  mask = (p->op==TK_IF_NULL_ROW) ? sqlite3WhereGetMask(pMaskSet, p->iTable) : 0;
  assert( !ExprHasProperty(p, EP_TokenOnly) );
  if( p->pRight ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pRight);
  if( p->pLeft ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pLeft);
  if( ExprHasProperty(p, EP_xIsSelect) ){
    if( ExprHasProperty(p, EP_VarSelect) ) pMaskSet->bVarSelect = 1;
    mask |= exprSelectUsage(pMaskSet, p->x.pSelect);
  }else if( p->x.pList ){
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->x.pList);
  }
  return mask;
}
Bitmask sqlite3WhereExprListUsage(WhereMaskSet *pMaskSet, ExprList *pList){
Changes to test/pushdown.test.
51
52
53
54
55
56
57

































58
59
} {b2}

do_test 1.5 {
  set L [list]
  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  set L
} {b3}

































  
finish_test







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


51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
} {b2}

do_test 1.5 {
  set L [list]
  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  set L
} {b3}

#-----------------------------------------------

do_execsql_test 2.0 {
  CREATE TABLE u1(a, b, c);
  CREATE TABLE u2(x, y, z);

  INSERT INTO u1 VALUES('a1', 'b1', 'c1');
  INSERT INTO u2 VALUES('a1', 'b1', 'c1');
}

do_test 2.1 {
  set L [list]
  execsql {
    SELECT * FROM u1 WHERE f('one')=123 AND 123=(
      SELECT x FROM u2 WHERE x=a AND f('two')
    )
  }
  set L
} {one}

do_test 2.2 {
  set L [list]
  breakpoint
  execsql {
    SELECT * FROM u1 WHERE 123=(
      SELECT x FROM u2 WHERE x=a AND f('two')
    ) AND f('three')=123
  }
  set L
} {three}


  
finish_test