Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix EXPLAIN QUERY PLAN so that it describes IN operators implemented using a ROWID lookup. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
60045fbf52162f15f2e18a4e392e80fa |
| User & Date: | drh 2018-08-16 15:29:40 |
Context
|
2018-08-16
| ||
| 16:24 | Fix a harmless unused-variable compiler warning that only came up in certain compile-time configurations. check-in: 45684292 user: drh tags: trunk | |
| 15:29 | Fix EXPLAIN QUERY PLAN so that it describes IN operators implemented using a ROWID lookup. check-in: 60045fbf user: drh tags: trunk | |
|
2018-08-15
| ||
| 14:03 | Allow sqlite3_snapshot_open() to be called to change the snapshot after a read transaction is already open on database. check-in: 41399169 user: dan tags: trunk | |
Changes
Changes to src/expr.c.
2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 |
if( nExpr==1 && pEList->a[0].pExpr->iColumn<0 ){
/* The "x IN (SELECT rowid FROM table)" case */
int iAddr = sqlite3VdbeAddOp0(v, OP_Once);
VdbeCoverage(v);
sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
eType = IN_INDEX_ROWID;
sqlite3VdbeJumpHere(v, iAddr);
}else{
Index *pIdx; /* Iterator variable */
int affinity_ok = 1;
int i;
/* Check that the affinity that will be used to perform each
|
| > |
2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 |
if( nExpr==1 && pEList->a[0].pExpr->iColumn<0 ){
/* The "x IN (SELECT rowid FROM table)" case */
int iAddr = sqlite3VdbeAddOp0(v, OP_Once);
VdbeCoverage(v);
sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
eType = IN_INDEX_ROWID;
ExplainQueryPlan((pParse, 0,
"USING ROWID SEARCH ON TABLE %s FOR IN-OPERATOR",pTab->zName));
sqlite3VdbeJumpHere(v, iAddr);
}else{
Index *pIdx; /* Iterator variable */
int affinity_ok = 1;
int i;
/* Check that the affinity that will be used to perform each
|
Changes to test/eqp.test.
739 740 741 742 743 744 745 746 747 748 749 750 751 752 |
}
det 8.2.4 "SELECT count(*) FROM t1" {
QUERY PLAN
`--SCAN TABLE t1
}
finish_test
|
> > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > | < |
739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 |
}
det 8.2.4 "SELECT count(*) FROM t1" {
QUERY PLAN
`--SCAN TABLE t1
}
# 2018-08-16: While working on Fossil I discovered that EXPLAIN QUERY PLAN
# did not describe IN operators implemented using a ROWID lookup. These
# test cases ensure that problem as been fixed.
#
do_execsql_test 9.0 {
-- Schema from Fossil 2018-08-16
CREATE TABLE forumpost(
fpid INTEGER PRIMARY KEY,
froot INT,
fprev INT,
firt INT,
fmtime REAL
);
CREATE INDEX forumthread ON forumpost(froot,fmtime);
CREATE TABLE blob(
rid INTEGER PRIMARY KEY,
rcvid INTEGER,
size INTEGER,
uuid TEXT UNIQUE NOT NULL,
content BLOB,
CHECK( length(uuid)>=40 AND rid>0 )
);
CREATE TABLE event(
type TEXT,
mtime DATETIME,
objid INTEGER PRIMARY KEY,
tagid INTEGER,
uid INTEGER REFERENCES user,
bgcolor TEXT,
euser TEXT,
user TEXT,
ecomment TEXT,
comment TEXT,
brief TEXT,
omtime DATETIME
);
CREATE INDEX event_i1 ON event(mtime);
CREATE TABLE private(rid INTEGER PRIMARY KEY);
}
do_eqp_test 9.1 {
WITH thread(age,duration,cnt,root,last) AS (
SELECT
julianday('now') - max(fmtime) AS age,
max(fmtime) - min(fmtime) AS duration,
sum(fprev IS NULL) AS msg_count,
froot,
(SELECT fpid FROM forumpost
WHERE froot=x.froot
AND fpid NOT IN private
ORDER BY fmtime DESC LIMIT 1)
FROM forumpost AS x
WHERE fpid NOT IN private --- Ensure this table mentioned in EQP output!
GROUP BY froot
ORDER BY 1 LIMIT 26 OFFSET 5
)
SELECT
thread.age,
thread.duration,
thread.cnt,
blob.uuid,
substr(event.comment,instr(event.comment,':')+1)
FROM thread, blob, event
WHERE blob.rid=thread.last
AND event.objid=thread.last
ORDER BY 1;
} {
QUERY PLAN
|--MATERIALIZE xxxxxx
| |--SCAN TABLE forumpost AS x USING INDEX forumthread
| |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
| |--CORRELATED SCALAR SUBQUERY
| | |--SEARCH TABLE forumpost USING COVERING INDEX forumthread (froot=?)
| | `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
| `--USE TEMP B-TREE FOR ORDER BY
|--SCAN SUBQUERY xxxxxx
|--SEARCH TABLE blob USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH TABLE event USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
}
finish_test
|