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 |
Timelines: | family | ancestors | descendants | both | branch-3.27 |
Files: | files | file ages | folders |
SHA3-256: |
55e38d53adf1b3e95b0931359f8e135f |
User & Date: | drh 2019-02-20 13:12:01.065 |
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
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 |