/ Check-in [a8da85c5]
Login

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

Overview
Comment:Preserve the COLLATE operator on an index on an expression when resolving the use of that expression into a reference to the index. See forum thread 7efabf4b03328e57 for details.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a8da85c57e07721dc1c918d67433d6c99ce48421e369123cc3194d855e55f7e8
User & Date: drh 2022-04-30 12:35:51
References
2022-05-02
15:47
Remove a testcase() macro taht was made obsolete by [a8da85c57e07721d]. (check-in: 053cf45e user: drh tags: trunk)
Context
2022-04-30
18:40
Simplified fix to the problem with subroutine reuse in the RIGHT JOIN no-match logic for a subquery on the right-hand side of an IN operator. The code still needs simplification. (check-in: a1937497 user: drh tags: trunk)
17:22
Fix another problem with subroutine reuse in the RIGHT JOIN no-match code for the subquery on the right-hand side of an IN operator. The current code works for all known cases, but seems unnecessarily complex and in need of simplification. Later: The fuzzer finds memory leaks with this version. (Closed-Leaf check-in: ad32da80 user: drh tags: not-working)
12:55
Preserve the COLLATE operator on an index on an expression when resolving the use of that expression into a reference to the index. See forum thread 7efabf4b03328e57 for details. (check-in: ef72f9e2 user: drh tags: branch-3.38)
12:35
Preserve the COLLATE operator on an index on an expression when resolving the use of that expression into a reference to the index. See forum thread 7efabf4b03328e57 for details. (check-in: a8da85c5 user: drh tags: trunk)
00:06
Further improvements to codeEqualityTerm() for cases when an IN operator with a right-hand side subquery is used as a constraint that needs to be processed by the RIGHT JOIN non-matched logic. (check-in: bb2798be user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

1251
1252
1253
1254
1255
1256
1257

1258
1259
1260
1261
1262
1263
1264
**
** If pExpr matches, then transform it into a reference to the index column
** that contains the value of pExpr.
*/
static int whereIndexExprTransNode(Walker *p, Expr *pExpr){
  IdxExprTrans *pX = p->u.pIdxTrans;
  if( sqlite3ExprCompare(0, pExpr, pX->pIdxExpr, pX->iTabCur)==0 ){

    preserveExpr(pX, pExpr);
    pExpr->affExpr = sqlite3ExprAffinity(pExpr);
    pExpr->op = TK_COLUMN;
    pExpr->iTable = pX->iIdxCur;
    pExpr->iColumn = pX->iIdxCol;
    testcase( ExprHasProperty(pExpr, EP_Skip) );
    testcase( ExprHasProperty(pExpr, EP_Unlikely) );







>







1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
**
** If pExpr matches, then transform it into a reference to the index column
** that contains the value of pExpr.
*/
static int whereIndexExprTransNode(Walker *p, Expr *pExpr){
  IdxExprTrans *pX = p->u.pIdxTrans;
  if( sqlite3ExprCompare(0, pExpr, pX->pIdxExpr, pX->iTabCur)==0 ){
    pExpr = sqlite3ExprSkipCollate(pExpr);
    preserveExpr(pX, pExpr);
    pExpr->affExpr = sqlite3ExprAffinity(pExpr);
    pExpr->op = TK_COLUMN;
    pExpr->iTable = pX->iIdxCur;
    pExpr->iColumn = pX->iIdxCol;
    testcase( ExprHasProperty(pExpr, EP_Skip) );
    testcase( ExprHasProperty(pExpr, EP_Unlikely) );

Changes to test/indexexpr1.test.

482
483
484
485
486
487
488
489



















490
    DROP TABLE IF EXISTS t1;
    CREATE TABLE t1(x REAL);
    CREATE INDEX t1x ON t1(x, +x);
    INSERT INTO t1(x) VALUES(2);
    SELECT +x FROM t1 WHERE x=2;
  } {2.0}
}




















finish_test








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

482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
    DROP TABLE IF EXISTS t1;
    CREATE TABLE t1(x REAL);
    CREATE INDEX t1x ON t1(x, +x);
    INSERT INTO t1(x) VALUES(2);
    SELECT +x FROM t1 WHERE x=2;
  } {2.0}
}

# 2022-04-30 https://sqlite.org/forum/info/7efabf4b03328e57
# Assertion fault during a DELETE INDEXED BY.
#
reset_db
do_execsql_test indexexpr-1900 {
  CREATE TABLE t1(x TEXT PRIMARY KEY, y TEXT, z INT);
  INSERT INTO t1(x,y,z) VALUES('alpha','ALPHA',1),('bravo','charlie',1);
  CREATE INDEX i1 ON t1(+y COLLATE NOCASE);
  SELECT * FROM t1;
} {alpha ALPHA 1 bravo charlie 1}
do_execsql_test indexexpr-1910 {
  DELETE FROM t1 INDEXED BY i1 
   WHERE x IS +y COLLATE NOCASE IN (SELECT z FROM t1)
  RETURNING *;
} {alpha ALPHA 1}
do_execsql_test indexexpr-1920 {
  SELECT * FROM t1;
} {bravo charlie 1}

finish_test