/ Check-in [5351e920]
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:When we play games with COLLATE in order to commute an operator in the WHERE clause processing, be sure not to use the commuted operator to qualify a partial index, as insufficient COLLATE information is preserved to verify that the expression will correctly qualify the index. Ticket [767a8cbc6d20bd68]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5351e920f489562f959ab8a376ff720f845ea165e0cdc7c3a271aac53c2aa64a
User & Date: drh 2019-09-03 14:27:25
Context
2019-09-03
16:23
Updates to the default settings in Makefile.linux-gcc. check-in: 3044cf69 user: drh tags: trunk
14:27
When we play games with COLLATE in order to commute an operator in the WHERE clause processing, be sure not to use the commuted operator to qualify a partial index, as insufficient COLLATE information is preserved to verify that the expression will correctly qualify the index. Ticket [767a8cbc6d20bd68] check-in: 5351e920 user: drh tags: trunk
2019-09-02
22:13
Fix a bug introduced earlier today by check-in [88833a9c2849c959]. Ticket [29f635e0af71234b] check-in: 6e7b4527 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2796
2797
2798
2799
2800
2801
2802


2803
2804
2805
2806
2807
2808
2809
2810
  Parse *pParse = pWC->pWInfo->pParse;
  while( pWhere->op==TK_AND ){
    if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
    pWhere = pWhere->pRight;
  }
  if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){


    Expr *pExpr = pTerm->pExpr;
    if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
     && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab) 
    ){
      return 1;
    }
  }
  return 0;







>
>
|







2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
  Parse *pParse = pWC->pWInfo->pParse;
  while( pWhere->op==TK_AND ){
    if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
    pWhere = pWhere->pRight;
  }
  if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    Expr *pExpr;
    if( pTerm->wtFlags & TERM_NOPARTIDX ) continue;
    pExpr = pTerm->pExpr;
    if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
     && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab) 
    ){
      return 1;
    }
  }
  return 0;

Changes to src/whereInt.h.

287
288
289
290
291
292
293

294
295
296
297
298
299
300
#  define TERM_VNULL    0x00   /* Disabled if not using stat4 */
#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 */







>







287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
#  define TERM_VNULL    0x00   /* Disabled if not using stat4 */
#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 */
#define TERM_NOPARTIDX  0x2000 /* Not for use to enable a partial index */

/*
** 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 */

Changes to src/whereexpr.c.

113
114
115
116
117
118
119





120
121
122
123

124
125
126
127
128
129
130

131
132
133
134
135

136
137
138
139
140
141
142
143
144
145
146

147
148
149
150
151
152
153
....
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
** If left/right precedence rules come into play when determining the
** collating sequence, then COLLATE operators are adjusted to ensure
** that the collating sequence does not change.  For example:
** "Y collate NOCASE op X" becomes "X op Y" because any collation sequence on
** the left hand side of a comparison overrides any collation sequence 
** attached to the right. For the same reason the EP_Collate flag
** is not commuted.





*/
static void exprCommute(Parse *pParse, Expr *pExpr){
  u16 expRight = (pExpr->pRight->flags & EP_Collate);
  u16 expLeft = (pExpr->pLeft->flags & EP_Collate);

  assert( allowedOp(pExpr->op) && pExpr->op!=TK_IN );
  if( expRight==expLeft ){
    /* Either X and Y both have COLLATE operator or neither do */
    if( expRight ){
      /* Both X and Y have COLLATE operators.  Make sure X is always
      ** used by clearing the EP_Collate flag from Y. */
      pExpr->pRight->flags &= ~EP_Collate;

    }else if( sqlite3ExprCollSeq(pParse, pExpr->pLeft)!=0 ){
      /* Neither X nor Y have COLLATE operators, but X has a non-default
      ** collating sequence.  So add the EP_Collate marker on X to cause
      ** it to be searched first. */
      pExpr->pLeft->flags |= EP_Collate;

    }
  }
  SWAP(Expr*,pExpr->pRight,pExpr->pLeft);
  if( pExpr->op>=TK_GT ){
    assert( TK_LT==TK_GT+2 );
    assert( TK_GE==TK_LE+2 );
    assert( TK_GT>TK_EQ );
    assert( TK_GT<TK_LE );
    assert( pExpr->op>=TK_GT && pExpr->op<=TK_GE );
    pExpr->op = ((pExpr->op-TK_GT)^2)+TK_GT;
  }

}

/*
** Translate from TK_xx operator to WO_xx bitmask.
*/
static u16 operatorMask(int op){
  u16 c;
................................................................................
          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;
        }
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pNew->leftCursor = aiCurCol[0];
      pNew->u.leftColumn = aiCurCol[1];
      testcase( (prereqLeft | extraRight) != prereqLeft );
      pNew->prereqRight = prereqLeft | extraRight;
      pNew->prereqAll = prereqAll;
      pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask;
    }







>
>
>
>
>

|


>







>





>











>







 







|







113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
....
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
** If left/right precedence rules come into play when determining the
** collating sequence, then COLLATE operators are adjusted to ensure
** that the collating sequence does not change.  For example:
** "Y collate NOCASE op X" becomes "X op Y" because any collation sequence on
** the left hand side of a comparison overrides any collation sequence 
** attached to the right. For the same reason the EP_Collate flag
** is not commuted.
**
** The return value is extra flags that are added to the WhereTerm object
** after it is commuted.  The only extra flag ever added is TERM_NOPARTIDX
** which prevents the term from being used to enable a partial index if
** COLLATE changes have been made.
*/
static u16 exprCommute(Parse *pParse, Expr *pExpr){
  u16 expRight = (pExpr->pRight->flags & EP_Collate);
  u16 expLeft = (pExpr->pLeft->flags & EP_Collate);
  u16 wtFlags = 0;
  assert( allowedOp(pExpr->op) && pExpr->op!=TK_IN );
  if( expRight==expLeft ){
    /* Either X and Y both have COLLATE operator or neither do */
    if( expRight ){
      /* Both X and Y have COLLATE operators.  Make sure X is always
      ** used by clearing the EP_Collate flag from Y. */
      pExpr->pRight->flags &= ~EP_Collate;
      wtFlags |= TERM_NOPARTIDX;
    }else if( sqlite3ExprCollSeq(pParse, pExpr->pLeft)!=0 ){
      /* Neither X nor Y have COLLATE operators, but X has a non-default
      ** collating sequence.  So add the EP_Collate marker on X to cause
      ** it to be searched first. */
      pExpr->pLeft->flags |= EP_Collate;
      wtFlags |= TERM_NOPARTIDX;
    }
  }
  SWAP(Expr*,pExpr->pRight,pExpr->pLeft);
  if( pExpr->op>=TK_GT ){
    assert( TK_LT==TK_GT+2 );
    assert( TK_GE==TK_LE+2 );
    assert( TK_GT>TK_EQ );
    assert( TK_GT<TK_LE );
    assert( pExpr->op>=TK_GT && pExpr->op<=TK_GE );
    pExpr->op = ((pExpr->op-TK_GT)^2)+TK_GT;
  }
  return wtFlags;
}

/*
** Translate from TK_xx operator to WO_xx bitmask.
*/
static u16 operatorMask(int op){
  u16 c;
................................................................................
          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;
        }
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }
      pNew->wtFlags |= exprCommute(pParse, pDup);
      pNew->leftCursor = aiCurCol[0];
      pNew->u.leftColumn = aiCurCol[1];
      testcase( (prereqLeft | extraRight) != prereqLeft );
      pNew->prereqRight = prereqLeft | extraRight;
      pNew->prereqAll = prereqAll;
      pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask;
    }

Changes to test/index6.test.

457
458
459
460
461
462
463


















464
465
466
} {1}
do_execsql_test index6-15.4 {
  SELECT 1 FROM t0 WHERE FALSE BETWEEN FALSE AND (t0.c0 IS FALSE);
} {1}
do_execsql_test index6-15.5 {
  SELECT 1 FROM t0 WHERE (c0 IS FALSE) IN (FALSE);
} {1}




















finish_test







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



457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
} {1}
do_execsql_test index6-15.4 {
  SELECT 1 FROM t0 WHERE FALSE BETWEEN FALSE AND (t0.c0 IS FALSE);
} {1}
do_execsql_test index6-15.5 {
  SELECT 1 FROM t0 WHERE (c0 IS FALSE) IN (FALSE);
} {1}

# 2019-09-03
# Ticket https://sqlite.org/src/info/767a8cbc6d20bd68
do_execsql_test index6-16.1 {
  DROP TABLE t0;
  CREATE TABLE t0(c0 COLLATE NOCASE, c1);
  CREATE INDEX i0 ON t0(0) WHERE c0 >= c1;
  INSERT INTO t0 VALUES('a', 'B');
  SELECT c1 <= c0, c0 >= c1 FROM t0;
} {1 0}
do_execsql_test index6-16.2 {
  SELECT 2 FROM t0 WHERE c0 >= c1;
} {}
do_execsql_test index6-16.3 {
  SELECT 3 FROM t0 WHERE c1 <= c0;
} {3}




finish_test