/ Check-in [60045fbf]
Login

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:60045fbf52162f15f2e18a4e392e80fab19bdbce242728b5e62b0894eac49dfd
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2362   2362       if( nExpr==1 && pEList->a[0].pExpr->iColumn<0 ){
  2363   2363         /* The "x IN (SELECT rowid FROM table)" case */
  2364   2364         int iAddr = sqlite3VdbeAddOp0(v, OP_Once);
  2365   2365         VdbeCoverage(v);
  2366   2366   
  2367   2367         sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
  2368   2368         eType = IN_INDEX_ROWID;
  2369         -
         2369  +      ExplainQueryPlan((pParse, 0,
         2370  +            "USING ROWID SEARCH ON TABLE %s FOR IN-OPERATOR",pTab->zName));
  2370   2371         sqlite3VdbeJumpHere(v, iAddr);
  2371   2372       }else{
  2372   2373         Index *pIdx;                         /* Iterator variable */
  2373   2374         int affinity_ok = 1;
  2374   2375         int i;
  2375   2376   
  2376   2377         /* Check that the affinity that will be used to perform each 

Changes to test/eqp.test.

   739    739   }
   740    740   
   741    741   det 8.2.4 "SELECT count(*) FROM t1" {
   742    742     QUERY PLAN
   743    743     `--SCAN TABLE t1
   744    744   }
   745    745   
   746         -
   747         -
   748         -
   749         -
   750         -
          746  +# 2018-08-16:  While working on Fossil I discovered that EXPLAIN QUERY PLAN
          747  +# did not describe IN operators implemented using a ROWID lookup.  These
          748  +# test cases ensure that problem as been fixed.
          749  +#
          750  +do_execsql_test 9.0 {
          751  +  -- Schema from Fossil 2018-08-16
          752  +  CREATE TABLE forumpost(
          753  +    fpid INTEGER PRIMARY KEY,
          754  +    froot INT,
          755  +    fprev INT,
          756  +    firt INT,
          757  +    fmtime REAL
          758  +  );
          759  +  CREATE INDEX forumthread ON forumpost(froot,fmtime);
          760  +  CREATE TABLE blob(
          761  +    rid INTEGER PRIMARY KEY,
          762  +    rcvid INTEGER,
          763  +    size INTEGER,
          764  +    uuid TEXT UNIQUE NOT NULL,
          765  +    content BLOB,
          766  +    CHECK( length(uuid)>=40 AND rid>0 )
          767  +  );
          768  +  CREATE TABLE event(
          769  +    type TEXT,
          770  +    mtime DATETIME,
          771  +    objid INTEGER PRIMARY KEY,
          772  +    tagid INTEGER,
          773  +    uid INTEGER REFERENCES user,
          774  +    bgcolor TEXT,
          775  +    euser TEXT,
          776  +    user TEXT,
          777  +    ecomment TEXT,
          778  +    comment TEXT,
          779  +    brief TEXT,
          780  +    omtime DATETIME
          781  +  );
          782  +  CREATE INDEX event_i1 ON event(mtime);
          783  +  CREATE TABLE private(rid INTEGER PRIMARY KEY);
          784  +}
          785  +do_eqp_test 9.1 {
          786  +  WITH thread(age,duration,cnt,root,last) AS (
          787  +    SELECT
          788  +      julianday('now') - max(fmtime) AS age,
          789  +      max(fmtime) - min(fmtime) AS duration,
          790  +      sum(fprev IS NULL) AS msg_count,
          791  +      froot,
          792  +      (SELECT fpid FROM forumpost
          793  +        WHERE froot=x.froot
          794  +          AND fpid NOT IN private
          795  +        ORDER BY fmtime DESC LIMIT 1)
          796  +    FROM forumpost AS x
          797  +    WHERE fpid NOT IN private  --- Ensure this table mentioned in EQP output!
          798  +    GROUP BY froot
          799  +    ORDER BY 1 LIMIT 26 OFFSET 5
          800  +  )
          801  +  SELECT
          802  +    thread.age,
          803  +    thread.duration,
          804  +    thread.cnt,
          805  +    blob.uuid,
          806  +    substr(event.comment,instr(event.comment,':')+1)
          807  +  FROM thread, blob, event
          808  +  WHERE blob.rid=thread.last
          809  +    AND event.objid=thread.last
          810  +  ORDER BY 1;
          811  +} {
          812  +  QUERY PLAN
          813  +  |--MATERIALIZE xxxxxx
          814  +  |  |--SCAN TABLE forumpost AS x USING INDEX forumthread
          815  +  |  |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
          816  +  |  |--CORRELATED SCALAR SUBQUERY
          817  +  |  |  |--SEARCH TABLE forumpost USING COVERING INDEX forumthread (froot=?)
          818  +  |  |  `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
          819  +  |  `--USE TEMP B-TREE FOR ORDER BY
          820  +  |--SCAN SUBQUERY xxxxxx
          821  +  |--SEARCH TABLE blob USING INTEGER PRIMARY KEY (rowid=?)
          822  +  |--SEARCH TABLE event USING INTEGER PRIMARY KEY (rowid=?)
          823  +  `--USE TEMP B-TREE FOR ORDER BY
          824  +}
   751    825   
   752    826   finish_test