SQLite

Check-in [5c8dd8df]
Login

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

Overview
Comment:When reading sqlite_stat4 data during query planning, be sure to expand zeroblobs prior to running comparisons. Fix for the issue identified by forum post 5275207102.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5c8dd8dfcaab9c364b3a126ca35880ef57f5cecbe030771e646c934c8cf43709
User & Date: drh 2023-03-25 22:37:23
Context
2023-03-25
23:52
When the left table of a RIGHT JOIN is used inside an aggregate function and the left table employs an index on expressions, then make sure the expressions evaluate to NULL for the cases where the left table should be NULL. Fix for forum post 9b491e1deb. (check-in: ffe23af7 user: drh tags: trunk)
22:42
When reading sqlite_stat4 data during query planning, be sure to expand zeroblobs prior to running comparisons. (check-in: 60f4d19d user: drh tags: branch-3.41)
22:37
When reading sqlite_stat4 data during query planning, be sure to expand zeroblobs prior to running comparisons. Fix for the issue identified by forum post 5275207102. (check-in: 5c8dd8df user: drh tags: trunk)
19:44
In the byte-code generator, when the result of an expression needs to be in a particular register, always use the sqlite3ExprCode() routine because it has the smarts to know whether to use OP_Copy or OP_SCopy. Do not try to OP_SCopy inline because an OP_Copy might be required. Fix for the problem identified by forum post 5522082cfc. (check-in: c104e5c6 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbemem.c.
︙︙
1583
1584
1585
1586
1587
1588
1589

1590
1591
1592
1593
1594
1595
1596

  if( op==TK_CAST ){
    u8 aff;
    assert( !ExprHasProperty(pExpr, EP_IntValue) );
    aff = sqlite3AffinityType(pExpr->u.zToken,0);
    rc = valueFromExpr(db, pExpr->pLeft, enc, aff, ppVal, pCtx);
    testcase( rc!=SQLITE_OK );
    if( *ppVal ){

      sqlite3VdbeMemCast(*ppVal, aff, enc);
      sqlite3ValueApplyAffinity(*ppVal, affinity, enc);
    }
    return rc;
  }

  /* Handle negative integers in a single step.  This is needed in the








>








1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597

  if( op==TK_CAST ){
    u8 aff;
    assert( !ExprHasProperty(pExpr, EP_IntValue) );
    aff = sqlite3AffinityType(pExpr->u.zToken,0);
    rc = valueFromExpr(db, pExpr->pLeft, enc, aff, ppVal, pCtx);
    testcase( rc!=SQLITE_OK );
    if( *ppVal ){
      rc = ExpandBlob(*ppVal);
      sqlite3VdbeMemCast(*ppVal, aff, enc);
      sqlite3ValueApplyAffinity(*ppVal, affinity, enc);
    }
    return rc;
  }

  /* Handle negative integers in a single step.  This is needed in the
︙︙
Changes to test/analyzeE.test.
︙︙
271
272
273
274
275
276
277



278











279
280

do_execsql_test analyzeE-6.3 {
  SELECT count(*)>1 FROM sqlite_stat4 WHERE idx='i1' AND neq='1 1 1 1 1 1';
} 1
do_execsql_test analyzeE-6.4 {
  SELECT count(*) FROM sqlite_stat4 WHERE idx='i1' AND neq<>'1 1 1 1 1 1';
} 0

















finish_test








>
>
>
|
>
>
>
>
>
>
>
>
>
>
>



271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294

do_execsql_test analyzeE-6.3 {
  SELECT count(*)>1 FROM sqlite_stat4 WHERE idx='i1' AND neq='1 1 1 1 1 1';
} 1
do_execsql_test analyzeE-6.4 {
  SELECT count(*) FROM sqlite_stat4 WHERE idx='i1' AND neq<>'1 1 1 1 1 1';
} 0

# 2023-03-25 https://sqlite.org/forum/forumpost/5275207102
# Correctly expand zeroblobs while processing STAT4 information
# during query planning.
#
reset_db
do_execsql_test analyzeE-7.0 {
  CREATE TABLE t1(a TEXT COLLATE binary);
  CREATE INDEX t1x ON t1(a);
  INSERT INTO t1(a) VALUES(0),('apple'),(NULL),(''),('banana');
  ANALYZE;
  SELECT format('(%s)',a) FROM t1 WHERE t1.a > CAST(zeroblob(5) AS TEXT);
} {(0) (apple) (banana)}
do_execsql_test analyzeE-7.1 {
  SELECT format('(%s)',a) FROM t1 WHERE t1.a <= CAST(zeroblob(5) AS TEXT);
} {()}

finish_test