/ Check-in [6bfaf525]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Refinements to the determination of when an A==B term is an equivalence. Add test cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | transitive-constraints
Files: files | file ages | folders
SHA1: 6bfaf525cac2e0c0a4a3bd3a1fc7bf5bd3234303
User & Date: drh 2015-05-18 11:34:52
Context
2015-05-18
12:18
Use an ALWAY() on conditionals in the transitive constraint logic that are always true as far as we know. Closed-Leaf check-in: 204e567f user: drh tags: transitive-constraints
11:34
Refinements to the determination of when an A==B term is an equivalence. Add test cases. check-in: 6bfaf525 user: drh tags: transitive-constraints
2015-05-16
20:51
Further restrictions on the use of the transitive property in WHERE clauses. check-in: 8c886c43 user: drh tags: transitive-constraints
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

  1184   1184   
  1185   1185   /*
  1186   1186   ** We already know that pExpr is a binary operator where both operands are
  1187   1187   ** column references.  This routine checks to see if pExpr is an equivalence
  1188   1188   ** relation:
  1189   1189   **   1.  The SQLITE_Transitive optimization must be enabled
  1190   1190   **   2.  Must be either an == or an IS operator
  1191         -**   3.  Not taken from the ON clause of a LEFT JOIN
         1191  +**   3.  Not originating the ON clause of an OUTER JOIN
  1192   1192   **   4.  The affinities of A and B must be compatible
  1193         -**   5.  Use the same collating sequence if not numeric affinity
         1193  +**   5a. Both operands use the same collating sequence OR
         1194  +**   5b. The overall collating sequence is BINARY
  1194   1195   ** If this routine returns TRUE, that means that the RHS can be substituted
  1195   1196   ** for the LHS anyplace else in the WHERE clause where the LHS column occurs.
  1196   1197   ** This is an optimization.  No harm comes from returning 0.  But if 1 is
  1197   1198   ** returned when it should not be, then incorrect answers might result.
  1198   1199   */
  1199         -static int isEquivalence(Parse *pParse, Expr *pExpr){
         1200  +static int termIsEquivalence(Parse *pParse, Expr *pExpr){
  1200   1201     char aff1, aff2;
  1201         -  CollSeq *pColl1, *pColl2;
         1202  +  CollSeq *pColl;
  1202   1203     const char *zColl1, *zColl2;
  1203   1204     if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
  1204   1205     if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
  1205   1206     if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
  1206   1207     aff1 = sqlite3ExprAffinity(pExpr->pLeft);
  1207   1208     aff2 = sqlite3ExprAffinity(pExpr->pRight);
  1208   1209     if( aff1!=aff2
  1209   1210      && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
  1210   1211     ){
  1211   1212       return 0;
  1212   1213     }
  1213         -  pColl1 = sqlite3ExprCollSeq(pParse, pExpr->pRight);
  1214         -  zColl1 = pColl1 ? pColl1->zName : "BINARY";
  1215         -  pColl2 = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
  1216         -  zColl2 = pColl2 ? pColl2->zName : "BINARY";
         1214  +  pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
         1215  +  if( pColl==0 || sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
         1216  +  pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
         1217  +  zColl1 = pColl ? pColl->zName : "BINARY";
         1218  +  pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
         1219  +  zColl2 = pColl ? pColl->zName : "BINARY";
  1217   1220     return sqlite3StrICmp(zColl1, zColl2)==0;
  1218   1221   }
  1219   1222   
  1220   1223   /*
  1221   1224   ** The input to this routine is an WhereTerm structure with only the
  1222   1225   ** "pExpr" field filled in.  The job of this routine is to analyze the
  1223   1226   ** subexpression and populate all the other fields of the WhereTerm
................................................................................
  1311   1314           if( idxNew==0 ) return;
  1312   1315           pNew = &pWC->a[idxNew];
  1313   1316           markTermAsChild(pWC, idxNew, idxTerm);
  1314   1317           if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
  1315   1318           pTerm = &pWC->a[idxTerm];
  1316   1319           pTerm->wtFlags |= TERM_COPIED;
  1317   1320   
  1318         -        if( isEquivalence(pParse, pDup) ){
         1321  +        if( termIsEquivalence(pParse, pDup) ){
  1319   1322             pTerm->eOperator |= WO_EQUIV;
  1320   1323             eExtraOp = WO_EQUIV;
  1321   1324           }
  1322   1325         }else{
  1323   1326           pDup = pExpr;
  1324   1327           pNew = pTerm;
  1325   1328         }

Changes to test/transitive1.test.

   292    292         JOIN files ON files.idFile = episodeview.idFile
   293    293         JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
   294    294         JOIN path ON path.idPath = tvshowlinkpath.idPath
   295    295     WHERE tvshowview.idShow = 1
   296    296     GROUP BY episodeview.c12;
   297    297   } {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}
   298    298   
          299  +##############################################################################
          300  +# 2015-05-18.  Make sure transitive constraints are avoided when column
          301  +# affinities and collating sequences get in the way.
          302  +#
          303  +db close
          304  +forcedelete test.db
          305  +sqlite3 db test.db
          306  +do_execsql_test transitive1-500 {
          307  +  CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
          308  +  INSERT INTO x VALUES(10, '10');
          309  +  SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
          310  +} {10 10}
          311  +do_execsql_test transitive1-510 {
          312  +  CREATE TABLE t1(x TEXT); 
          313  +  CREATE TABLE t2(y TEXT); 
          314  +  INSERT INTO t1 VALUES('abc');
          315  +  INSERT INTO t2 VALUES('ABC');
          316  +  SELECT * FROM t1 CROSS JOIN t2 WHERE (x=y COLLATE nocase) AND y='ABC';
          317  +} {abc ABC}
          318  +do_execsql_test transitive1-520 {
          319  +  CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT);
          320  +  INSERT INTO t3 VALUES(10, '10');
          321  +  SELECT * FROM t3 WHERE i=t AND t = '10 ';
          322  +} {}
          323  +do_execsql_test transitive1-530 {
          324  +  CREATE TABLE u1(x TEXT, y INTEGER, z TEXT);
          325  +  CREATE INDEX i1 ON u1(x);
          326  +  INSERT INTO u1 VALUES('00013', 13, '013');
          327  +  SELECT * FROM u1 WHERE x=y AND y=z AND z='013';
          328  +} {00013 13 013}
          329  +do_execsql_test transitive1-540 {
          330  +  CREATE TABLE b1(x, y);
          331  +  INSERT INTO b1 VALUES('abc', 'ABC');
          332  +  CREATE INDEX b1x ON b1(x);
          333  +  SELECT * FROM b1 WHERE (x=y COLLATE nocase) AND y='ABC';
          334  +} {abc ABC}
          335  +do_execsql_test transitive1-550 {
          336  +  CREATE TABLE c1(x, y COLLATE nocase, z);
          337  +  INSERT INTO c1 VALUES('ABC', 'ABC', 'abc');
          338  +  SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
          339  +} {ABC ABC abc}
          340  +do_execsql_test transitive1-560 {
          341  +  CREATE INDEX c1x ON c1(x);
          342  +  SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
          343  +} {ABC ABC abc}
          344  +do_execsql_test transitive1-560eqp {
          345  +  EXPLAIN QUERY PLAN
          346  +  SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
          347  +} {/SCAN TABLE c1/}
          348  +do_execsql_test transitive1-570 {
          349  +  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
          350  +} {}
          351  +do_execsql_test transitive1-570eqp {
          352  +  EXPLAIN QUERY PLAN
          353  +  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
          354  +} {/SEARCH TABLE c1 USING INDEX c1x/}
   299    355   
   300    356   finish_test