SQLite

Check-in [d9b716a6bd]
Login

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

Overview
Comment:Correctly handle the case of a WHERE-clause term with indexed expressions on both sides of the == sign.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d9b716a6bd6145b2e7733c04d86227df777cd473
User & Date: drh 2015-09-24 11:26:45.688
Context
2015-09-24
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)
11:06
Fix a JSON1 test case so that it works on builds that omit virtual tables. (check-in: a4444c0f66 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
189
190
191
192
193
194
195

196
197
198
199
200
201
202
203
204
205
206

         && pTerm->u.leftColumn==iColumn
         && (iColumn!=(-2)
               || sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
         && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
        ){
          if( (pTerm->eOperator & WO_EQUIV)!=0
           && pScan->nEquiv<ArraySize(pScan->aiCur)

          ){
            int j;
            pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight);
            assert( pX->op==TK_COLUMN );
            for(j=0; j<pScan->nEquiv; j++){
              if( pScan->aiCur[j]==pX->iTable
               && pScan->aiColumn[j]==pX->iColumn ){
                  break;
              }
            }
            if( j==pScan->nEquiv ){








>


<
<








189
190
191
192
193
194
195
196
197
198


199
200
201
202
203
204
205

         && pTerm->u.leftColumn==iColumn
         && (iColumn!=(-2)
               || sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
         && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
        ){
          if( (pTerm->eOperator & WO_EQUIV)!=0
           && pScan->nEquiv<ArraySize(pScan->aiCur)
           && (pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight))->op==TK_COLUMN
          ){
            int j;


            for(j=0; j<pScan->nEquiv; j++){
              if( pScan->aiCur[j]==pX->iTable
               && pScan->aiColumn[j]==pX->iColumn ){
                  break;
              }
            }
            if( j==pScan->nEquiv ){
︙︙
Changes to test/indexexpr1.test.
︙︙
229
230
231
232
233
234
235
236
237






238
239

  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}










finish_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