/ Check-in [d9b716a6]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d9b716a6bd6145b2e7733c04d86227df777cd473
User & Date: drh 2015-09-24 11:26:45
Context
2015-09-24
12:19
Make sure joins work correctly when both sides of the join are connected using indexed expressions. check-in: c2fcb032 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: d9b716a6 user: drh tags: trunk
11:06
Fix a JSON1 test case so that it works on builds that omit virtual tables. check-in: a4444c0f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs 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