SQLite

Check-in [720ce06d]
Login

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

Overview
Comment:Do not allow the query planner to be tricked into thinking that an index on a constant expression might be useful for something. Problem reported on forum post ecdfc02339. This is a follow-up to the fixes at [44200596aa943963] and [2d2b91cc0f6fed8c].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 720ce06d93a9e4cc25c34c873c82165d8801f208c22701e51538f3210de84f65
User & Date: drh 2024-03-07 12:34:26
Context
2024-03-07
15:58
Fix harmless compiler warnings in test code for the intck extension. (check-in: 7fbdc1a8 user: drh tags: trunk)
12:34
Do not allow the query planner to be tricked into thinking that an index on a constant expression might be useful for something. Problem reported on forum post ecdfc02339. This is a follow-up to the fixes at [44200596aa943963] and [2d2b91cc0f6fed8c]. (check-in: 720ce06d user: drh tags: trunk)
2024-03-06
20:49
Add the json_pretty() SQL function. (check-in: ceb51c1c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/whereexpr.c.
︙︙
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999

    iCur = pFrom->a[j].iCursor;
    for(pIdx=pFrom->a[j].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( pIdx->aColExpr==0 ) continue;
      for(i=0; i<pIdx->nKeyCol; i++){
        if( pIdx->aiColumn[i]!=XN_EXPR ) continue;
        assert( pIdx->bHasExpr );
        if( sqlite3ExprCompareSkip(pExpr,pIdx->aColExpr->a[i].pExpr,iCur)==0
          && pExpr->op!=TK_STRING
        ){
          aiCurCol[0] = iCur;
          aiCurCol[1] = XN_EXPR;
          return 1;
        }
      }
    }








|








985
986
987
988
989
990
991
992
993
994
995
996
997
998
999

    iCur = pFrom->a[j].iCursor;
    for(pIdx=pFrom->a[j].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( pIdx->aColExpr==0 ) continue;
      for(i=0; i<pIdx->nKeyCol; i++){
        if( pIdx->aiColumn[i]!=XN_EXPR ) continue;
        assert( pIdx->bHasExpr );
        if( sqlite3ExprCompareSkip(pExpr,pIdx->aColExpr->a[i].pExpr,iCur)==0
         && !sqlite3ExprIsConstant(pIdx->aColExpr->a[i].pExpr)
        ){
          aiCurCol[0] = iCur;
          aiCurCol[1] = XN_EXPR;
          return 1;
        }
      }
    }
︙︙
Changes to test/whereL.test.
︙︙
204
205
206
207
208
209
210


















211
212

} 1
do_eqp_test 710 {
  SELECT v FROM t1 WHERE abs(v)=1 and v=1;
} {
  QUERY PLAN
  `--SEARCH t1 USING INDEX idx (<expr>=?)
}



















finish_test








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



204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230

} 1
do_eqp_test 710 {
  SELECT v FROM t1 WHERE abs(v)=1 and v=1;
} {
  QUERY PLAN
  `--SEARCH t1 USING INDEX idx (<expr>=?)
}

# 2024-03-07 https://sqlite.org/forum/forumpost/ecdfc02339
# A refinement is needed to the enhancements tested by the prior test case
# to avoid another problem with indexes on constant expressions.
#
reset_db
db null NULL
do_execsql_test 800 {
  CREATE TABLE t0(c0, c1);
  CREATE TABLE t1(c2);
  CREATE INDEX i0 ON t1(NULL);
  INSERT INTO t1(c2) VALUES (0.2);
  CREATE VIEW v0(c3) AS SELECT DISTINCT c2 FROM t1;
  SELECT * FROM v0 LEFT JOIN t0 ON c3<NULL LEFT JOIN t1 ON 1;
} {0.2 NULL NULL 0.2}
do_execsql_test 810 {
  SELECT * FROM v0 LEFT JOIN t0 ON c3<NULL LEFT JOIN t1 ON 1 WHERE c2/0.1;
} {0.2 NULL NULL 0.2}

finish_test