The "x IN (?)" optimization in check-ins [2ff3b25f40] and [e68b427afb] is

incorrect, as demonstrated by the in4-5.1 test case in this check-in.
The "COLLATE binary" that was being added to the RHS of IN was overriding
the implicit collating sequence of the LHS.  This change defines the EP_Generic
expression node property that blocks all affinity or collating sequence
information in the expression subtree and adds that property to the expression
taken from RHS of the IN operator.

FossilOrigin-Name: 2ea4a9f75f46eaa928ba17e9e91bc0432750d46d

3 files changed, 18 insertions, 7 deletions

diff --git a/src/expr.c b/src/expr.c
index 83b948e8e..204d69617 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -33,6 +33,7 @@
 char sqlite3ExprAffinity(Expr *pExpr){
   int op;
   pExpr = sqlite3ExprSkipCollate(pExpr);
+  if( pExpr->flags & EP_Generic ) return SQLITE_AFF_NONE;
   op = pExpr->op;
   if( op==TK_SELECT ){
     assert( pExpr->flags&EP_xIsSelect );
@@ -122,6 +123,7 @@ CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
   Expr *p = pExpr;
   while( p ){
     int op = p->op;
+    if( p->flags & EP_Generic ) break;
     if( op==TK_CAST || op==TK_UPLUS ){
       p = p->pLeft;
       continue;
diff --git a/src/parse.y b/src/parse.y
index aae30f5ee..ef0092525 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -1026,15 +1026,24 @@ expr(A) ::= expr(W) between_op(N) expr(X) AND expr(Y). [BETWEEN] {
       **      expr1 IN (?1)
       **      expr1 NOT IN (?2)
       **
-      ** with exactly one value on the RHS can be simplified to:
+      ** with exactly one value on the RHS can be simplified to something
+      ** like this:
       **
-      **      expr1 == (+?1 COLLATE binary)
-      **      expr1 <> (+?2 COLLATE binary)
+      **      expr1 == ?1
+      **      expr1 <> ?2
+      **
+      ** But, the RHS of the == or <> is marked with the EP_Generic flag
+      ** so that it may not contribute to the computation of comparison
+      ** affinity or the collating sequence to use for comparison.  Otherwise,
+      ** the semantics would be subtly different from IN or NOT IN.
       */
-      Expr *pRHS = sqlite3ExprAddCollateString(pParse, Y->a[0].pExpr, "binary");
+      Expr *pRHS = Y->a[0].pExpr;
       Y->a[0].pExpr = 0;
       sqlite3ExprListDelete(pParse->db, Y);
-      pRHS = sqlite3PExpr(pParse, TK_UPLUS, pRHS, 0, 0);
+      if( pRHS ){
+        pRHS->flags &= ~EP_Collate;
+        pRHS->flags |= EP_Generic;
+      }
       A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
     }else{
       A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 0974db7c9..55b7e080a 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1893,8 +1893,8 @@ struct Expr {
 #define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */
 #define EP_DblQuoted 0x000040 /* token.z was originally in "..." */
 #define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */
-#define EP_Collate   0x000100 /* Tree contains a TK_COLLATE opeartor */
-      /* unused      0x000200 */
+#define EP_Collate   0x000100 /* Tree contains a TK_COLLATE operator */
+#define EP_Generic   0x000200 /* Ignore COLLATE or affinity on this tree */
 #define EP_IntValue  0x000400 /* Integer value contained in u.iValue */
 #define EP_xIsSelect 0x000800 /* x.pSelect is valid (otherwise x.pList is) */
 #define EP_Skip      0x001000 /* COLLATE, AS, or UNLIKELY */