SQLite

Check-in [46e28cbcf6]
Login

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

Overview
Comment:Do not disable a rowid=? term used to drive an IPK index if it is a transitive constraint. Commit [f1f9b5de] made the same change for non-IPK indexes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 46e28cbcf6044b36aa4ddcda09adb49a46c6c6a8d41d558467ede3091304aa8c
User & Date: dan 2021-08-30 17:02:48.783
Original Comment: Do not disable a rowid=? term used to drive an IPK index if it is a transitive constraint.
Context
2021-08-31
15:53
Have the planner ensure that if one scan uses a subset of the WHERE clause of another, that scan is estimated to cost less and return fewer rows. (check-in: c7b34930e2 user: dan tags: trunk)
2021-08-30
17:02
Do not disable a rowid=? term used to drive an IPK index if it is a transitive constraint. Commit [f1f9b5de] made the same change for non-IPK indexes. (check-in: 46e28cbcf6 user: dan tags: trunk)
2021-08-27
11:26
Patch lemon to fix "error" token handling, according ot forum post e680f42f53090061. (check-in: 106b5e5355 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/wherecode.c.
︙︙
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512

    iReleaseReg = ++pParse->nMem;
    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
    if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
    addrNxt = pLevel->addrNxt;
    sqlite3VdbeAddOp3(v, OP_SeekRowid, iCur, addrNxt, iRowidReg);
    VdbeCoverage(v);
    pLevel->op = OP_Noop;
    if( (pTerm->prereqAll & pLevel->notReady)==0 ){
      pTerm->wtFlags |= TERM_CODED;
    }
  }else if( (pLoop->wsFlags & WHERE_IPK)!=0
         && (pLoop->wsFlags & WHERE_COLUMN_RANGE)!=0
  ){
    /* Case 3:  We have an inequality comparison against the ROWID field.
    */
    int testOp = OP_Noop;
    int start;








<
<
<








1496
1497
1498
1499
1500
1501
1502



1503
1504
1505
1506
1507
1508
1509

    iReleaseReg = ++pParse->nMem;
    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
    if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
    addrNxt = pLevel->addrNxt;
    sqlite3VdbeAddOp3(v, OP_SeekRowid, iCur, addrNxt, iRowidReg);
    VdbeCoverage(v);
    pLevel->op = OP_Noop;



  }else if( (pLoop->wsFlags & WHERE_IPK)!=0
         && (pLoop->wsFlags & WHERE_COLUMN_RANGE)!=0
  ){
    /* Case 3:  We have an inequality comparison against the ROWID field.
    */
    int testOp = OP_Noop;
    int start;
︙︙
Changes to test/transitive1.test.
︙︙
375
376
377
378
379
380
381


















382
383

  CREATE TABLE t1(c0 INT);
  INSERT INTO t0(c0, c1) VALUES (0, 1);
  INSERT INTO t1(c0) VALUES (1);
  SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
  SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
  SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);
} {}



















finish_test








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



375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401

  CREATE TABLE t1(c0 INT);
  INSERT INTO t0(c0, c1) VALUES (0, 1);
  INSERT INTO t1(c0) VALUES (1);
  SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
  SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
  SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);
} {}

#-------------------------------------------------------------------------
# 2021-08-31 forum https://sqlite.org/forum/forumpost/8d1b58f112
reset_db
do_execsql_test transitive1-700 {
  CREATE TABLE t1(a INT PRIMARY KEY);
  INSERT INTO t1(a) VALUES(1),(2),(3);
  CREATE TABLE t2(x INTEGER PRIMARY KEY,y INT);
  INSERT INTO t2(y) VALUES(2),(3);
}

do_execsql_test transitive1-710 {
  SELECT * FROM t1 CROSS JOIN t2 WHERE t2.y=t1.a AND t1.a=t2.x
} {}

do_execsql_test transitive1-720 {
  SELECT * FROM t1 CROSS JOIN t2 WHERE likely(t2.y=t1.a) AND unlikely(t1.a=t2.x)
} {}

finish_test