/ Check-in [5df4056448]
Login

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

Overview
Comment:Transitive constraints should only work if operands have compatible affinities and collating sequences.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:5df4056448fee1c766f8f79c735ed12abdce5101
User & Date: drh 2015-05-18 12:28:09
Context
2015-05-19
16:50
Merge the ota-update branch with trunk. check-in: 08e2864ed7 user: dan tags: trunk
14:14
Merge latest trunk changes with this branch. check-in: 6055a6725c user: dan tags: ota-update
2015-05-18
12:28
Transitive constraints should only work if operands have compatible affinities and collating sequences. check-in: 5df4056448 user: drh tags: trunk
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: 204e567f68 user: drh tags: transitive-constraints
04:24
Make a hard copy of the results of a subquery lest the result of the subquery be referenced after a change to the table that generated the subquery result. check-in: 9c0d80907b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

1177
1178
1179
1180
1181
1182
1183








































1184
1185
1186
1187
1188
1189
1190
....
1272
1273
1274
1275
1276
1277
1278

1279
1280
1281
1282

1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
        sqlite3ExprListDelete(db, pList);
      }
      pTerm->eOperator = WO_NOOP;  /* case 1 trumps case 3 */
    }
  }
}
#endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */









































/*
** 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
** structure.
**
................................................................................
          sqlite3ExprDelete(db, pDup);
          return;
        }
        idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
        if( idxNew==0 ) return;
        pNew = &pWC->a[idxNew];
        markTermAsChild(pWC, idxNew, idxTerm);

        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;
        if( (op==TK_EQ || op==TK_IS)
         && !ExprHasProperty(pExpr, EP_FromJoin)

         && OptimizationEnabled(db, SQLITE_Transitive)
        ){
          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;
        }
        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
      pNew->leftCursor = pLeft->iTable;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>


<
<
>
|
<



<







1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
....
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321


1322
1323

1324
1325
1326

1327
1328
1329
1330
1331
1332
1333
        sqlite3ExprListDelete(db, pList);
      }
      pTerm->eOperator = WO_NOOP;  /* case 1 trumps case 3 */
    }
  }
}
#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 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);
  /* Since pLeft and pRight are both a column references, their collating
  ** sequence should always be defined. */
  zColl1 = ALWAYS(pColl) ? pColl->zName : 0;
  pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
  zColl2 = ALWAYS(pColl) ? pColl->zName : 0;
  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
** structure.
**
................................................................................
          sqlite3ExprDelete(db, pDup);
          return;
        }
        idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
        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;
      }
      exprCommute(pParse, pDup);
      pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
      pNew->leftCursor = pLeft->iTable;

Changes to test/transitive1.test.

292
293
294
295
296
297
298



299





















































300
      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}


























































finish_test







>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
      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