/ Check-in [55e38d53ad]
Login

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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.27
Files: files | file ages | folders
SHA3-256: 55e38d53adf1b3e95b0931359f8e135f0b2c063f34676b8dd27d933acdd6af5f
User & Date: drh 2019-02-20 13:12:01
Context
2019-02-20
13:14
Increment the version number to 3.27.2. check-in: a70d67d8af user: drh tags: branch-3.27
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: 55e38d53ad 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: fa792714ae user: drh tags: trunk
2019-02-08
13:17
Version 3.27.1 check-in: 0eca3dd3d3 user: drh tags: release, branch-3.27, version-3.27.1
Changes
Hide Diffs Unified Diffs Show Whitespace Changes 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