Fix a problem where an expression like (a, b) IN (SELECT ...) might not use an index on (a, b) if the affinities and collation sequences of "a" and "b" are not identical.

FossilOrigin-Name: 4d870fd8b5450047a7486fc023d1ac9439642e8ed91eadfd5026c4cda7cc9179

1 files changed, 34 insertions, 4 deletions

diff --git a/src/where.c b/src/where.c
index 93984347b..f6b0990e6 100644
--- a/src/where.c
+++ b/src/where.c
@@ -302,6 +302,28 @@ static Expr *whereRightSubexprIsColumn(Expr *p){
   return 0;
 }
 
+static int SQLITE_NOINLINE indexInAffinityOk(
+  Parse *pParse, 
+  WhereTerm *pTerm, 
+  u8 idxaff, 
+  CollSeq **ppColl
+){
+  Expr *pX = pTerm->pExpr;
+  Expr inexpr;
+
+  if( sqlite3ExprIsVector(pX->pLeft) ){
+    int iField = pTerm->u.x.iField - 1;
+    inexpr.op = TK_EQ;
+    inexpr.pLeft = pX->pLeft->x.pList->a[iField].pExpr;
+    assert( ExprUseXSelect(pX) );
+    inexpr.pRight = pX->x.pSelect->pEList->a[iField].pExpr;
+    pX = &inexpr;
+  }
+
+  *ppColl = sqlite3ExprCompareCollSeq(pParse, pX);
+  return sqlite3IndexAffinityOk(pX, idxaff);
+}
+
 /*
 ** Advance to the next WhereTerm that matches according to the criteria
 ** established when the pScan object was initialized by whereScanInit().
@@ -355,11 +377,19 @@ static WhereTerm *whereScanNext(WhereScan *pScan){
               CollSeq *pColl;
               Parse *pParse = pWC->pWInfo->pParse;
               pX = pTerm->pExpr;
-              if( !sqlite3IndexAffinityOk(pX, pScan->idxaff) ){
-                continue;
+
+              if( (pTerm->eOperator & WO_IN) ){
+                if( !indexInAffinityOk(pParse, pTerm, pScan->idxaff, &pColl) ){
+                  continue;
+                }
+              }else{
+                if( !sqlite3IndexAffinityOk(pX, pScan->idxaff) ){
+                  continue;
+                }
+                assert(pX->pLeft);
+                pColl = sqlite3ExprCompareCollSeq(pParse, pX);
               }
-              assert(pX->pLeft);
-              pColl = sqlite3ExprCompareCollSeq(pParse, pX);
+
               if( pColl==0 ) pColl = pParse->db->pDfltColl;
               if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
                 continue;