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]

FossilOrigin-Name: 5351e920f489562f959ab8a376ff720f845ea165e0cdc7c3a271aac53c2aa64a

3 files changed, 15 insertions, 3 deletions

diff --git a/src/where.c b/src/where.c
index b51d4485c..8c7874dc5 100644
--- a/src/where.c
+++ b/src/where.c
@@ -2800,7 +2800,9 @@ static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
   }
   if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
   for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
-    Expr *pExpr = pTerm->pExpr;
+    Expr *pExpr;
+    if( pTerm->wtFlags & TERM_NOPARTIDX ) continue;
+    pExpr = pTerm->pExpr;
     if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
      && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab) 
     ){
diff --git a/src/whereInt.h b/src/whereInt.h
index e63ca46d5..64978cf11 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -291,6 +291,7 @@ struct WhereTerm {
 #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
diff --git a/src/whereexpr.c b/src/whereexpr.c
index d878bdb9e..f6f966b9e 100644
--- a/src/whereexpr.c
+++ b/src/whereexpr.c
@@ -117,10 +117,16 @@ static int allowedOp(int op){
 ** 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 void exprCommute(Parse *pParse, Expr *pExpr){
+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 */
@@ -128,11 +134,13 @@ static void exprCommute(Parse *pParse, Expr *pExpr){
       /* 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);
@@ -144,6 +152,7 @@ static void exprCommute(Parse *pParse, Expr *pExpr){
     assert( pExpr->op>=TK_GT && pExpr->op<=TK_GE );
     pExpr->op = ((pExpr->op-TK_GT)^2)+TK_GT;
   }
+  return wtFlags;
 }
 
 /*
@@ -1140,7 +1149,7 @@ static void exprAnalyze(
         pDup = pExpr;
         pNew = pTerm;
       }
-      exprCommute(pParse, pDup);
+      pNew->wtFlags |= exprCommute(pParse, pDup);
       pNew->leftCursor = aiCurCol[0];
       pNew->u.leftColumn = aiCurCol[1];
       testcase( (prereqLeft | extraRight) != prereqLeft );