SQLite

Check-in [791473bc]
Login

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

Overview
Comment:An index on the expression "likely(X)" should store the true value of X, even if X is a real number with no fractional part. The optimization of storing some REAL indexed values as INT (see [10d851353c2abead]) should be omitted for index columns that are expressions. Forum post 3b940c437a.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 791473bc6677c2ae76f558082a4b43ca1eaf9895b76624d5b55d58f2af898ff3
User & Date: drh 2021-06-14 14:00:09
Context
2021-06-14
14:52
Fix a test script problem in corruptN.test. (check-in: 3ddfe9ae user: dan tags: trunk)
14:35
Update this branch with latest changes from trunk. (check-in: f21a53fb user: dan tags: reuse-schema)
14:12
Update this branch with latest changes from trunk. (check-in: b38c2b8b user: dan tags: wal2)
14:00
An index on the expression "likely(X)" should store the true value of X, even if X is a real number with no fractional part. The optimization of storing some REAL indexed values as INT (see [10d851353c2abead]) should be omitted for index columns that are expressions. Forum post 3b940c437a. (check-in: 791473bc user: drh tags: trunk)
11:20
Avoid allocating oversized column names in the sqllimit1.test module. (check-in: 7068f1f6 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

938
939
940
941
942
943
944

945
946
947
948
949
950
951

952
953
954
955
956
957
958
     && pPrior->aiColumn[j]==pIdx->aiColumn[j]
     && pPrior->aiColumn[j]!=XN_EXPR
    ){
      /* This column was already computed by the previous index */
      continue;
    }
    sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iDataCur, j, regBase+j);

    /* If the column affinity is REAL but the number is an integer, then it
    ** might be stored in the table as an integer (using a compact
    ** representation) then converted to REAL by an OP_RealAffinity opcode.
    ** But we are getting ready to store this value back into an index, where
    ** it should be converted by to INTEGER again.  So omit the OP_RealAffinity
    ** opcode if it is present */
    sqlite3VdbeDeletePriorOpcode(v, OP_RealAffinity);

  }
  if( regOut ){
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regOut);
  }
  sqlite3ReleaseTempRange(pParse, regBase, nCol);
  return regBase;
}







>
|
|
|
|
|
|
|
>







938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
     && pPrior->aiColumn[j]==pIdx->aiColumn[j]
     && pPrior->aiColumn[j]!=XN_EXPR
    ){
      /* This column was already computed by the previous index */
      continue;
    }
    sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iDataCur, j, regBase+j);
    if( pIdx->aiColumn[j]>=0 ){
      /* If the column affinity is REAL but the number is an integer, then it
      ** might be stored in the table as an integer (using a compact
      ** representation) then converted to REAL by an OP_RealAffinity opcode.
      ** But we are getting ready to store this value back into an index, where
      ** it should be converted by to INTEGER again.  So omit the
      ** OP_RealAffinity opcode if it is present */
      sqlite3VdbeDeletePriorOpcode(v, OP_RealAffinity);
    }
  }
  if( regOut ){
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regOut);
  }
  sqlite3ReleaseTempRange(pParse, regBase, nCol);
  return regBase;
}

Changes to test/whereG.test.

361
362
363
364
365
366
367


















368
369
370
} {AAA AAA t2}
do_execsql_test 11.2.2 {
  SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
} {AAA AAA t2}
do_execsql_test 11.2.3 {
  SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
} {AAA AAA t2}




















finish_test







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



361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
} {AAA AAA t2}
do_execsql_test 11.2.2 {
  SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
} {AAA AAA t2}
do_execsql_test 11.2.3 {
  SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
} {AAA AAA t2}

# 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a
# Affinity problem when a likely() function is used as a column in
# an index.
#
reset_db
do_execsql_test 12.0 {
  CREATE TABLE t1(a REAL);
  INSERT INTO t1(a) VALUES(123);
  CREATE INDEX t1x1 ON t1(likely(a));
  SELECT typeof(likely(a)) FROM t1 NOT INDEXED;
  SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1;
} {real real}
do_execsql_test 12.1 {
  CREATE INDEX t1x2 ON t1(abs(a));
  SELECT typeof(abs(a)) FROM t1 NOT INDEXED;
  SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2;
} {real real}


finish_test