SQLite

/*
** 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 originating the ON clause of an OUTER JOIN
**   4.  The affinities of A and B must be compatible
**   5a. Both operands use the same collating sequence OR
**   5b. The overall collating sequence is BINARY
** 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 termIsEquivalence(Parse *pParse, Expr *pExpr){
  char aff1, aff2;
  CollSeq *pColl;
  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;
  }
  pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
  if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  zColl1 = pColl ? pColl->zName : "BINARY";
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
  zColl2 = pColl ? pColl->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
** subexpression and populate all the other fields of the WhereTerm

        if( idxNew==0 ) return;
        pNew = &pWC->a[idxNew];
        markTermAsChild(pWC, idxNew, idxTerm);
        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;

        if( termIsEquivalence(pParse, pDup) ){
          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;
        }
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }

      JOIN files ON files.idFile = episodeview.idFile
      JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
      JOIN path ON path.idPath = tvshowlinkpath.idPath
  WHERE tvshowview.idShow = 1
  GROUP BY episodeview.c12;
} {1 /tmp/tvshows/The.Big.Bang.Theory/ {The Big Bang Theory} {Leonard Hofstadter and Sheldon Cooper are brilliant physicists, the kind of "beautiful minds" that understand how the universe works. But none of that genius helps them interact with people, especially women. All this begins to change when a free-spirited beauty named Penny moves in next door. Sheldon, Leonard's roommate, is quite content spending his nights playing Klingon Boggle with their socially dysfunctional friends, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leonard sees in Penny a whole new universe of possibilities... including love.} 2007-09-24 Comedy CBS TV-PG 3 1 0}

##############################################################################
# 2015-05-18.  Make sure transitive constraints are avoided when column
# affinities and collating sequences get in the way.
#
db close
forcedelete test.db
sqlite3 db test.db
do_execsql_test transitive1-500 {
  CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO x VALUES(10, '10');
  SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
} {10 10}
do_execsql_test transitive1-510 {
  CREATE TABLE t1(x TEXT); 
  CREATE TABLE t2(y TEXT); 
  INSERT INTO t1 VALUES('abc');
  INSERT INTO t2 VALUES('ABC');
  SELECT * FROM t1 CROSS JOIN t2 WHERE (x=y COLLATE nocase) AND y='ABC';
} {abc ABC}
do_execsql_test transitive1-520 {
  CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT);
  INSERT INTO t3 VALUES(10, '10');
  SELECT * FROM t3 WHERE i=t AND t = '10 ';
} {}
do_execsql_test transitive1-530 {
  CREATE TABLE u1(x TEXT, y INTEGER, z TEXT);
  CREATE INDEX i1 ON u1(x);
  INSERT INTO u1 VALUES('00013', 13, '013');
  SELECT * FROM u1 WHERE x=y AND y=z AND z='013';
} {00013 13 013}
do_execsql_test transitive1-540 {
  CREATE TABLE b1(x, y);
  INSERT INTO b1 VALUES('abc', 'ABC');
  CREATE INDEX b1x ON b1(x);
  SELECT * FROM b1 WHERE (x=y COLLATE nocase) AND y='ABC';
} {abc ABC}
do_execsql_test transitive1-550 {
  CREATE TABLE c1(x, y COLLATE nocase, z);
  INSERT INTO c1 VALUES('ABC', 'ABC', 'abc');
  SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
} {ABC ABC abc}
do_execsql_test transitive1-560 {
  CREATE INDEX c1x ON c1(x);
  SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
} {ABC ABC abc}
do_execsql_test transitive1-560eqp {
  EXPLAIN QUERY PLAN
  SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
} {/SCAN TABLE c1/}
do_execsql_test transitive1-570 {
  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
} {}
do_execsql_test transitive1-570eqp {
  EXPLAIN QUERY PLAN
  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
} {/SEARCH TABLE c1 USING INDEX c1x/}

finish_test