SQLite

Check-in [c2fcb03299]
Login

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

Overview
Comment:Make sure joins work correctly when both sides of the join are connected using indexed expressions.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c2fcb03299f2872d7f97a540ea145519f8b2e5cc
User & Date: drh 2015-09-24 12:19:17.384
Context
2015-09-24
12:40
Strengthen the implementations of xShmMemoryBarrier on both the unix and windows VFSes, so that they likely work even if SQLITE_THREADSAFE=0 is used. (check-in: c6ab807b72 user: drh tags: trunk)
12:19
Make sure joins work correctly when both sides of the join are connected using indexed expressions. (check-in: c2fcb03299 user: drh tags: trunk)
11:26
Correctly handle the case of a WHERE-clause term with indexed expressions on both sides of the == sign. (check-in: d9b716a6bd user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192

  WhereClause *pWC;    /* Shorthand for pScan->pWC */
  WhereTerm *pTerm;    /* The term being tested */
  int k = pScan->k;    /* Where to start scanning */

  while( pScan->iEquiv<=pScan->nEquiv ){
    iCur = pScan->aiCur[pScan->iEquiv-1];
    iColumn = pScan->aiColumn[pScan->iEquiv-1];
    assert( iColumn!=(-2) || pScan->pIdxExpr!=0 );
    while( (pWC = pScan->pWC)!=0 ){
      for(pTerm=pWC->a+k; k<pWC->nTerm; k++, pTerm++){
        if( pTerm->leftCursor==iCur
         && pTerm->u.leftColumn==iColumn
         && (iColumn!=(-2)
               || sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
         && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))








|








178
179
180
181
182
183
184
185
186
187
188
189
190
191
192

  WhereClause *pWC;    /* Shorthand for pScan->pWC */
  WhereTerm *pTerm;    /* The term being tested */
  int k = pScan->k;    /* Where to start scanning */

  while( pScan->iEquiv<=pScan->nEquiv ){
    iCur = pScan->aiCur[pScan->iEquiv-1];
    iColumn = pScan->aiColumn[pScan->iEquiv-1];
    if( iColumn==(-2) && pScan->pIdxExpr==0 ) return 0;
    while( (pWC = pScan->pWC)!=0 ){
      for(pTerm=pWC->a+k; k<pWC->nTerm; k++, pTerm++){
        if( pTerm->leftCursor==iCur
         && pTerm->u.leftColumn==iColumn
         && (iColumn!=(-2)
               || sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
         && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
︙︙
Changes to test/indexexpr1.test.
︙︙
229
230
231
232
233
234
235


236
237
238
239
240
241
242
243











244
245

  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1
    VALUES('t4','t4all','600000 160000 40000 10000 2000 600 100 40 10');
  ANALYZE sqlite_master;
  SELECT i FROM t4 WHERE e=5;
} {9}



do_execsql_test indexexpr1-700 {
  DROP TABLE IF EXISTS t7;
  CREATE TABLE t7(a,b,c);
  INSERT INTO t7(a,b,c) VALUES(1,2,2),('abc','def','def'),(4,5,6);
  CREATE INDEX t7b ON t7(+b);
  CREATE INDEX t7c ON t7(+c);
  SELECT *, '|' FROM t7 WHERE +b=+c ORDER BY +a;
} {1 2 2 | abc def def |}












finish_test








>
>








>
>
>
>
>
>
>
>
>
>
>



229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258

  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1
    VALUES('t4','t4all','600000 160000 40000 10000 2000 600 100 40 10');
  ANALYZE sqlite_master;
  SELECT i FROM t4 WHERE e=5;
} {9}

# Indexed expressions on both sides of an == in a WHERE clause.
#
do_execsql_test indexexpr1-700 {
  DROP TABLE IF EXISTS t7;
  CREATE TABLE t7(a,b,c);
  INSERT INTO t7(a,b,c) VALUES(1,2,2),('abc','def','def'),(4,5,6);
  CREATE INDEX t7b ON t7(+b);
  CREATE INDEX t7c ON t7(+c);
  SELECT *, '|' FROM t7 WHERE +b=+c ORDER BY +a;
} {1 2 2 | abc def def |}
do_execsql_test indexexpr1-710 {
  CREATE TABLE t71(a,b,c);
  CREATE INDEX t71bc ON t71(b+c);
  CREATE TABLE t72(x,y,z);
  CREATE INDEX t72yz ON t72(y+z);
  INSERT INTO t71(a,b,c) VALUES(1,11,2),(2,7,15),(3,5,4);
  INSERT INTO t72(x,y,z) VALUES(1,10,3),(2,8,14),(3,9,9);
  SELECT a, x, '|' FROM t71, t72
   WHERE b+c=y+z
  ORDER BY +a, +x;
} {1 1 | 2 2 |}

finish_test