SQLite

Check-in [979f384a93]
Login

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

Overview
Comment:Fix an obscure problem allowing the propagate-constants optimization to improperly substitute a column of a sub-query with NONE affinity. Forum post 2025-04-08T14:18:45Z.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 979f384a93d25e24f760469681618101feaab873738e1b52a7d4b818f7c527d9
User & Date: dan 2025-04-10 15:01:58.329
Context
2025-04-10
16:48
Improve the error messages returned by sqlite3session_diff(). (check-in: a3217cdb75 user: dan tags: trunk)
15:01
Fix an obscure problem allowing the propagate-constants optimization to improperly substitute a column of a sub-query with NONE affinity. Forum post 2025-04-08T14:18:45Z. (check-in: 979f384a93 user: dan tags: trunk)
14:53
Fix an obscure problem allowing the propagate-constants optimization to improperly substitute a column of a sub-query with NONE affinity. (Closed-Leaf check-in: d82725dcae user: dan tags: forum-0109bca824)
10:18
Remove unnecessary "www." prefixes on domain names in URLs. (check-in: 20acd630b9 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
4854
4855
4856
4857
4858
4859
4860

4861
4862
4863
4864
4865
4866
4867
4868

    assert( pE2->op==TK_COLUMN );
    if( pE2->iTable==pColumn->iTable
     && pE2->iColumn==pColumn->iColumn
    ){
      return;  /* Already present.  Return without doing anything. */
    }
  }

  if( sqlite3ExprAffinity(pColumn)==SQLITE_AFF_BLOB ){
    pConst->bHasAffBlob = 1;
  }

  pConst->nConst++;
  pConst->apExpr = sqlite3DbReallocOrFree(pConst->pParse->db, pConst->apExpr,
                         pConst->nConst*2*sizeof(Expr*));
  if( pConst->apExpr==0 ){








>
|








4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
4868
4869

    assert( pE2->op==TK_COLUMN );
    if( pE2->iTable==pColumn->iTable
     && pE2->iColumn==pColumn->iColumn
    ){
      return;  /* Already present.  Return without doing anything. */
    }
  }
  assert( SQLITE_AFF_NONE<SQLITE_AFF_BLOB );
  if( sqlite3ExprAffinity(pColumn)<=SQLITE_AFF_BLOB ){
    pConst->bHasAffBlob = 1;
  }

  pConst->nConst++;
  pConst->apExpr = sqlite3DbReallocOrFree(pConst->pParse->db, pConst->apExpr,
                         pConst->nConst*2*sizeof(Expr*));
  if( pConst->apExpr==0 ){
︙︙
4929
4930
4931
4932
4933
4934
4935

4936
4937
4938
4939
4940
4941
4942
4943

    return WRC_Continue;
  }
  for(i=0; i<pConst->nConst; i++){
    Expr *pColumn = pConst->apExpr[i*2];
    if( pColumn==pExpr ) continue;
    if( pColumn->iTable!=pExpr->iTable ) continue;
    if( pColumn->iColumn!=pExpr->iColumn ) continue;

    if( bIgnoreAffBlob && sqlite3ExprAffinity(pColumn)==SQLITE_AFF_BLOB ){
      break;
    }
    /* A match is found.  Add the EP_FixedCol property */
    pConst->nChng++;
    ExprClearProperty(pExpr, EP_Leaf);
    ExprSetProperty(pExpr, EP_FixedCol);
    assert( pExpr->pLeft==0 );








>
|








4930
4931
4932
4933
4934
4935
4936
4937
4938
4939
4940
4941
4942
4943
4944
4945

    return WRC_Continue;
  }
  for(i=0; i<pConst->nConst; i++){
    Expr *pColumn = pConst->apExpr[i*2];
    if( pColumn==pExpr ) continue;
    if( pColumn->iTable!=pExpr->iTable ) continue;
    if( pColumn->iColumn!=pExpr->iColumn ) continue;
    assert( SQLITE_AFF_NONE<SQLITE_AFF_BLOB );
    if( bIgnoreAffBlob && sqlite3ExprAffinity(pColumn)<=SQLITE_AFF_BLOB ){
      break;
    }
    /* A match is found.  Add the EP_FixedCol property */
    pConst->nChng++;
    ExprClearProperty(pExpr, EP_Leaf);
    ExprSetProperty(pExpr, EP_FixedCol);
    assert( pExpr->pLeft==0 );
︙︙
Changes to test/whereL.test.
︙︙
249
250
251
252
253
254
255








































256
257

  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








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



249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297

  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}

#-------------------------------------------------------------------------
# 2025-04-10 https://sqlite.org/forum/forumpost/0109bca824
reset_db

do_execsql_test 900 {
  SELECT * FROM (SELECT 1.0 AS abc) WHERE abc=1;
} {1.0}
do_execsql_test 910 {
  SELECT * FROM (SELECT 1.0 AS abc) WHERE abc LIKE '1.0';
} {1.0}
do_execsql_test 920 {
  SELECT * FROM (SELECT 1.0 AS abc) WHERE abc=1 AND abc LIKE '1.0';
} {1.0}

do_execsql_test 930 {
  CREATE TABLE IF NOT EXISTS t0 (c0 BLOB);
  CREATE TABLE IF NOT EXISTS t1 (c0 INTEGER);

  INSERT INTO t1 VALUES ('1');
  INSERT INTO t0 VALUES (''), (''), ('2');
}

do_execsql_test 940 {
  SELECT *
    FROM (SELECT 0.0 AS col_0) as subQuery
    LEFT JOIN t0 ON ((CASE ''
          WHEN t0.c0 THEN subQuery.col_0
          ELSE (t0.c0) END) LIKE (((((subQuery.col_0))))))
    LEFT JOIN t1 ON ((subQuery.col_0) == (false));
} {0.0 {} 1 0.0 {} 1}

do_execsql_test 950 {
  SELECT *
    FROM (SELECT 0.0 AS col_0) as subQuery
    LEFT JOIN t0 ON ((CASE ''
          WHEN t0.c0 THEN subQuery.col_0
          ELSE (t0.c0) END) LIKE (((((subQuery.col_0))))))
    LEFT JOIN t1 ON ((subQuery.col_0) == (false)) WHERE t1.c0;
} {0.0 {} 1 0.0 {} 1}

finish_test