/ Check-in [fa792714]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:When an IN operator drives a query loop, mark it as "CODED" so that it will not be used afterwards for a (pointless) membership test. This is a better fix for ticket [df46dfb631f75694] than the previous fix that is now on a branch as it preserves the full optimization of check-in [e130319317e76119].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: fa792714ae62fa980f1767acc6d622a6727ceb677870243c88548423795dcb5b
User & Date: drh 2019-02-20 12:52:47
References
2019-02-23
00:21
Check-in [fa792714ae62fa98] is incorrect. Add a test case to refute it and also a fix to make it right. Then add an alternative fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused. check-in: d3915230 user: drh tags: trunk
2019-02-22
21:33
Check-in [fa792714ae62fa980] is not a valid fix for ticket [df46dfb631f75694], as the new test case in this check-in demonstrates. The fix here causes test cases for the [df46dfb631f75694] bug to fail again, so this check-in is on a branch. A new fix is needed for [df46dfb631f75694]. check-in: 0d456456 user: drh tags: tkt-df46dfb631
Context
2019-02-20
13:55
Progress handler improvements: (1) Invoke the callback after OP_Program opcodes (2) Invoke the callback multiple times in a row to catch up after a long run of no progress checks. check-in: 0c5db18d user: drh tags: trunk
13:23
Merge the latest performance enhancements and fixes from trunk. check-in: be71d1e2 user: drh tags: apple-osx
13:12
When an IN operator drives a query loop, mark it as "CODED" so that it will not be used afterwards for a (pointless) membership test. check-in: 55e38d53 user: drh tags: branch-3.27
12:52
When an IN operator drives a query loop, mark it as "CODED" so that it will not be used afterwards for a (pointless) membership test. This is a better fix for ticket [df46dfb631f75694] than the previous fix that is now on a branch as it preserves the full optimization of check-in [e130319317e76119]. check-in: fa792714 user: drh tags: trunk
2019-02-19
20:29
Enhance the xBestIndex method on delta_parse() to return SQLITE_CONSTRAINT if no delta argument is supplied. check-in: f16d127c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

1340
1341
1342
1343
1344
1345
1346

1347
1348
1349
1350
1351
1352
1353
    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;







>







1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
    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;
    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;

Changes to test/in.test.

646
647
648
649
650
651
652
653
































































654
do_execsql_test in-14.0 {
  CREATE TABLE c1(a);
  INSERT INTO c1 VALUES(1), (2), (4), (3);
}
do_execsql_test in-14.1 {
  SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
} {1 2 3 4}

































































finish_test








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

646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
do_execsql_test in-14.0 {
  CREATE TABLE c1(a);
  INSERT INTO c1 VALUES(1), (2), (4), (3);
}
do_execsql_test in-14.1 {
  SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
} {1 2 3 4}

# 2019-02-20 Ticket https://www.sqlite.org/src/tktview/df46dfb631f75694fbb97033b69
#
do_execsql_test in-15.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES(1);
  SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
} {1}
do_execsql_test in-15.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
  INSERT INTO t2 VALUES(1,11);
  INSERT INTO t2 VALUES(2,22);
  INSERT INTO t2 VALUES(3,33);
  SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;
} {11 0 22 0 33 1}
do_execsql_test in-15.2 {
  DROP TABLE IF EXISTS t3;
  CREATE TABLE t3(x INTEGER PRIMARY KEY);
  INSERT INTO t3 VALUES(8);
  SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3;
  SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3;
} {yes no}
do_execsql_test in-15.3 {
  SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
  SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
} {yes no}
do_execsql_test in-15.4 {
  DROP TABLE IF EXISTS t4;
  CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
    INSERT INTO t4(a,b) SELECT x, x+100 FROM c;
  SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;
} {103 108}
do_execsql_test in-15.5 {
  SELECT b FROM t4 WHERE a NOT IN (3,null,8);
} {}
do_execsql_test in-15.6 {
  DROP TABLE IF EXISTS t5;
  DROP TABLE IF EXISTS t6;
  CREATE TABLE t5(id INTEGER PRIMARY KEY, name TEXT);
  CREATE TABLE t6(id INTEGER PRIMARY KEY, name TEXT, t5_id INT);
  INSERT INTO t5 VALUES(1,'Alice'),(2,'Emma');
  INSERT INTO t6 VALUES(1,'Bob',1),(2,'Cindy',1),(3,'Dave',2);
  SELECT a.*
    FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id
   WHERE b.id IN (
          SELECT t6.t5_id
            FROM t6
           WHERE name='Bob'
             AND t6.t5_id IS NOT NULL
             AND t6.id IN (
                  SELECT id
                    FROM (SELECT t6.id, count(*) AS x
                            FROM t6
                           WHERE name='Bob'
                         ) AS 't'
                   WHERE x=1
                 )
             AND t6.id IN (1,id)
         );
} {1 Alice}


finish_test