/ Check-in [55e38d53]
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: a70d67d8 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: 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-08
13:17
Version 3.27.1 check-in: 0eca3dd3 user: drh tags: release, branch-3.27, version-3.27.1
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

  1340   1340       iReleaseReg = ++pParse->nMem;
  1341   1341       iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
  1342   1342       if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
  1343   1343       addrNxt = pLevel->addrNxt;
  1344   1344       sqlite3VdbeAddOp3(v, OP_SeekRowid, iCur, addrNxt, iRowidReg);
  1345   1345       VdbeCoverage(v);
  1346   1346       pLevel->op = OP_Noop;
         1347  +    pTerm->wtFlags |= TERM_CODED;
  1347   1348     }else if( (pLoop->wsFlags & WHERE_IPK)!=0
  1348   1349            && (pLoop->wsFlags & WHERE_COLUMN_RANGE)!=0
  1349   1350     ){
  1350   1351       /* Case 3:  We have an inequality comparison against the ROWID field.
  1351   1352       */
  1352   1353       int testOp = OP_Noop;
  1353   1354       int start;

Changes to test/in.test.

   646    646   do_execsql_test in-14.0 {
   647    647     CREATE TABLE c1(a);
   648    648     INSERT INTO c1 VALUES(1), (2), (4), (3);
   649    649   }
   650    650   do_execsql_test in-14.1 {
   651    651     SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
   652    652   } {1 2 3 4}
          653  +
          654  +# 2019-02-20 Ticket https://www.sqlite.org/src/tktview/df46dfb631f75694fbb97033b69
          655  +#
          656  +do_execsql_test in-15.0 {
          657  +  DROP TABLE IF EXISTS t1;
          658  +  CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY);
          659  +  INSERT INTO t1 VALUES(1);
          660  +  SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
          661  +} {1}
          662  +do_execsql_test in-15.1 {
          663  +  DROP TABLE IF EXISTS t2;
          664  +  CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
          665  +  INSERT INTO t2 VALUES(1,11);
          666  +  INSERT INTO t2 VALUES(2,22);
          667  +  INSERT INTO t2 VALUES(3,33);
          668  +  SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;
          669  +} {11 0 22 0 33 1}
          670  +do_execsql_test in-15.2 {
          671  +  DROP TABLE IF EXISTS t3;
          672  +  CREATE TABLE t3(x INTEGER PRIMARY KEY);
          673  +  INSERT INTO t3 VALUES(8);
          674  +  SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3;
          675  +  SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3;
          676  +} {yes no}
          677  +do_execsql_test in-15.3 {
          678  +  SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
          679  +  SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
          680  +} {yes no}
          681  +do_execsql_test in-15.4 {
          682  +  DROP TABLE IF EXISTS t4;
          683  +  CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);
          684  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
          685  +    INSERT INTO t4(a,b) SELECT x, x+100 FROM c;
          686  +  SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;
          687  +} {103 108}
          688  +do_execsql_test in-15.5 {
          689  +  SELECT b FROM t4 WHERE a NOT IN (3,null,8);
          690  +} {}
          691  +do_execsql_test in-15.6 {
          692  +  DROP TABLE IF EXISTS t5;
          693  +  DROP TABLE IF EXISTS t6;
          694  +  CREATE TABLE t5(id INTEGER PRIMARY KEY, name TEXT);
          695  +  CREATE TABLE t6(id INTEGER PRIMARY KEY, name TEXT, t5_id INT);
          696  +  INSERT INTO t5 VALUES(1,'Alice'),(2,'Emma');
          697  +  INSERT INTO t6 VALUES(1,'Bob',1),(2,'Cindy',1),(3,'Dave',2);
          698  +  SELECT a.*
          699  +    FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id
          700  +   WHERE b.id IN (
          701  +          SELECT t6.t5_id
          702  +            FROM t6
          703  +           WHERE name='Bob'
          704  +             AND t6.t5_id IS NOT NULL
          705  +             AND t6.id IN (
          706  +                  SELECT id
          707  +                    FROM (SELECT t6.id, count(*) AS x
          708  +                            FROM t6
          709  +                           WHERE name='Bob'
          710  +                         ) AS 't'
          711  +                   WHERE x=1
          712  +                 )
          713  +             AND t6.id IN (1,id)
          714  +         );
          715  +} {1 Alice}
          716  +
   653    717   
   654    718   finish_test