From 5820118da5e18cc219b39de553541a3b857340b4 Mon Sep 17 00:00:00 2001
From: drh <drh@noemail.net>
Date: Sat, 16 May 2015 20:51:25 +0000
Subject: [PATCH] Further restrictions on the use of the transitive property in
 WHERE clauses.

FossilOrigin-Name: 8c886c43ff0bd5953d289534791d2c6b9dc25445
---
 manifest      | 15 ++++++-------
 manifest.uuid |  2 +-
 src/where.c   | 58 +++++++++++++++++++++++++++++++++------------------
 3 files changed, 45 insertions(+), 30 deletions(-)

diff --git a/manifest b/manifest
index cc5e0de87d..8c1df345e0 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\sthe\stransitive\sconstraint\sprocessing\sto\sonly\sallow\stransitivity\sif\sthe\noperands\sof\sthe\s==\sor\sIS\soperator\shave\scompatible\saffinities.
-D 2015-05-16T19:17:17.907
+C Further\srestrictions\son\sthe\suse\sof\sthe\stransitive\sproperty\sin\sWHERE\sclauses.
+D 2015-05-16T20:51:25.495
 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f
 F Makefile.in edfc69769e613a6359c42c06ea1d42c3bece1736
 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23
@@ -307,7 +307,7 @@ F src/vxworks.h c18586c8edc1bddbc15c004fa16aeb1e1342b4fb
 F src/wal.c ce2cb2d06faab54d1bce3e739bec79e063dd9113
 F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4
 F src/walker.c c253b95b4ee44b21c406e2a1052636c31ea27804
-F src/where.c 58c0161b81ec25bf7d221baf445d871ab98d9cce
+F src/where.c ca8d3b5b579304b9920c91371d83db8384d41bf7
 F src/whereInt.h a6f5a762bc1b4b1c76e1cea79976b437ac35a435
 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2
@@ -1258,10 +1258,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1
 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4
 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32
 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P ee4b74250ad7a4061421d44b490cb79f649b3720
-R 1beebe1e9084926919d5e75f3ab331f9
-T *branch * transitive-constraints
-T *sym-transitive-constraints *
-T -sym-trunk *
+P a46a247fbcfe6e63b12cef31353835295a650c9b
+R 4551cb9328257688f241c62e5acce267
 U drh
-Z 9dd679bea28da23301e6184e5fdbcc54
+Z d352b897d8069cb12e380202c093ac79
diff --git a/manifest.uuid b/manifest.uuid
index d63f527ff2..dc1d2cee90 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-a46a247fbcfe6e63b12cef31353835295a650c9b
\ No newline at end of file
+8c886c43ff0bd5953d289534791d2c6b9dc25445
\ No newline at end of file
diff --git a/src/where.c b/src/where.c
index 1fe1d50069..5c64b86fbe 100644
--- a/src/where.c
+++ b/src/where.c
@@ -1182,6 +1182,41 @@ static void exprAnalyzeOrTerm(
 }
 #endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */
 
+/*
+** We already know that pExpr is a binary operator where both operands are
+** column references.  This routine checks to see if pExpr is an equivalence
+** relation:
+**   1.  The SQLITE_Transitive optimization must be enabled
+**   2.  Must be either an == or an IS operator
+**   3.  Not taken from the ON clause of a LEFT JOIN
+**   4.  The affinities of A and B must be compatible
+**   5.  Use the same collating sequence if not numeric affinity
+** If this routine returns TRUE, that means that the RHS can be substituted
+** for the LHS anyplace else in the WHERE clause where the LHS column occurs.
+** This is an optimization.  No harm comes from returning 0.  But if 1 is
+** returned when it should not be, then incorrect answers might result.
+*/
+static int isEquivalence(Parse *pParse, Expr *pExpr){
+  char aff1, aff2;
+  CollSeq *pColl1, *pColl2;
+  const char *zColl1, *zColl2;
+  if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
+  if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
+  if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
+  aff1 = sqlite3ExprAffinity(pExpr->pLeft);
+  aff2 = sqlite3ExprAffinity(pExpr->pRight);
+  if( aff1!=aff2
+   && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
+  ){
+    return 0;
+  }
+  pColl1 = sqlite3ExprCollSeq(pParse, pExpr->pRight);
+  zColl1 = pColl1 ? pColl1->zName : "BINARY";
+  pColl2 = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
+  zColl2 = pColl2 ? pColl2->zName : "BINARY";
+  return sqlite3StrICmp(zColl1, zColl2)==0;
+}
+
 /*
 ** The input to this routine is an WhereTerm structure with only the
 ** "pExpr" field filled in.  The job of this routine is to analyze the
@@ -1280,26 +1315,9 @@ static void exprAnalyze(
         pTerm = &pWC->a[idxTerm];
         pTerm->wtFlags |= TERM_COPIED;
 
-        /* Expressions of the form "A==B" or "A IS B" might be candidates
-        ** for propagating constraints via the transitive property.  In other
-        ** words:  "A==B AND B==$xyz" implies "A==$xyz".  If this term
-        ** qualifies, mark it with WO_EQUIV.  Necessary preconditions:
-        **   1.  The term is not in the ON clause of a LEFT JOIN
-        **   2.  The affinities of A and B must be compatible
-        **   3.  The SQLITE_Transitive optimization must be enabled
-        */
-        if( (op==TK_EQ || op==TK_IS)
-         && !ExprHasProperty(pExpr, EP_FromJoin)
-         && OptimizationEnabled(db, SQLITE_Transitive)
-        ){
-          char aff1 = sqlite3ExprAffinity(pDup->pLeft);
-          char aff2 = sqlite3ExprAffinity(pDup->pRight);
-          if( aff1==aff2
-           || (sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2))
-          ){
-            pTerm->eOperator |= WO_EQUIV;
-            eExtraOp = WO_EQUIV;
-          }
+        if( isEquivalence(pParse, pDup) ){
+          pTerm->eOperator |= WO_EQUIV;
+          eExtraOp = WO_EQUIV;
         }
       }else{
         pDup = pExpr;
-- 
2.47.2