/ Check-in [6d410442]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add some extra source-code comments and a test case.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | coroutine-autoindex
Files: files | file ages | folders
SHA1: 6d410442fb532e2cf0ad1859bc9e843651f8ff4d
User & Date: drh 2015-05-29 14:36:30
Context
2015-05-29
14:47
Add support for automatic indexes on FROM-clause subqueries that are implemented via co-routine. check-in: 020b8b10 user: drh tags: trunk
14:36
Add some extra source-code comments and a test case. Closed-Leaf check-in: 6d410442 user: drh tags: coroutine-autoindex
13:55
An attempt to allow automatic index creation on subqueries accessed via co-routine. check-in: 521345ad user: drh tags: coroutine-autoindex
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  5216   5216       pProbe = &sPk;
  5217   5217     }
  5218   5218     rSize = pTab->nRowLogEst;
  5219   5219     rLogSize = estLog(rSize);
  5220   5220   
  5221   5221   #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
  5222   5222     /* Automatic indexes */
  5223         -  if( !pBuilder->pOrSet
         5223  +  if( !pBuilder->pOrSet   /* Not part of an OR optimization */
  5224   5224      && (pWInfo->wctrlFlags & WHERE_NO_AUTOINDEX)==0
  5225   5225      && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0
  5226         -   && pSrc->pIndex==0
  5227         -   && !pSrc->notIndexed
  5228         -   && HasRowid(pTab)
  5229         -   && !pSrc->isCorrelated
  5230         -   && !pSrc->isRecursive
         5226  +   && pSrc->pIndex==0     /* Has no INDEXED BY clause */
         5227  +   && !pSrc->notIndexed   /* Has no NOT INDEXED clause */
         5228  +   && HasRowid(pTab)      /* Is not a WITHOUT ROWID table. (FIXME: Why not?) */
         5229  +   && !pSrc->isCorrelated /* Not a correlated subquery */
         5230  +   && !pSrc->isRecursive  /* Not a recursive common table expression. */
  5231   5231     ){
  5232   5232       /* Generate auto-index WhereLoops */
  5233   5233       WhereTerm *pTerm;
  5234   5234       WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
  5235   5235       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  5236   5236         if( pTerm->prereqRight & pNew->maskSelf ) continue;
  5237   5237         if( termCanDriveIndex(pTerm, pSrc, 0) ){

Added test/autoindex5.test.

            1  +# 2014-10-24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library.  The
           13  +# focus of this script is testing automatic index creation logic,
           14  +# and specifically ensuring that automatic indexes can be used with
           15  +# co-routine subqueries.
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +# Schema is from the Debian security database
           22  +#
           23  +do_execsql_test autoindex5-1.0 {
           24  +  CREATE TABLE source_package_status
           25  +          (bug_name TEXT NOT NULL,
           26  +           package INTEGER NOT NULL,
           27  +           vulnerable INTEGER NOT NULL,
           28  +           urgency TEXT NOT NULL,
           29  +           PRIMARY KEY (bug_name, package));
           30  +  CREATE INDEX source_package_status_package
           31  +              ON source_package_status(package);
           32  +  
           33  +  CREATE TABLE source_packages
           34  +              (name TEXT NOT NULL,
           35  +              release TEXT NOT NULL,
           36  +              subrelease TEXT NOT NULL,
           37  +              archive TEXT NOT NULL,
           38  +              version TEXT NOT NULL,
           39  +              version_id INTEGER NOT NULL DEFAULT 0,
           40  +              PRIMARY KEY (name, release, subrelease, archive));
           41  +  
           42  +  CREATE TABLE bugs
           43  +          (name TEXT NOT NULL PRIMARY KEY,
           44  +           cve_status TEXT NOT NULL
           45  +               CHECK (cve_status IN
           46  +                      ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
           47  +           not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
           48  +           description TEXT NOT NULL,
           49  +           release_date TEXT NOT NULL,
           50  +           source_file TEXT NOT NULL,
           51  +           source_line INTEGER NOT NULL);
           52  +  
           53  +  CREATE TABLE package_notes
           54  +          (id INTEGER NOT NULL PRIMARY KEY,
           55  +           bug_name TEXT NOT NULL,
           56  +           package TEXT NOT NULL,
           57  +           fixed_version TEXT
           58  +               CHECK (fixed_version IS NULL OR fixed_version <> ''),
           59  +           fixed_version_id INTEGER NOT NULL DEFAULT 0,
           60  +           release TEXT NOT NULL,
           61  +           package_kind TEXT NOT NULL DEFAULT 'unknown',
           62  +           urgency TEXT NOT NULL,
           63  +           bug_origin TEXT NOT NULL DEFAULT '');
           64  +  CREATE INDEX package_notes_package
           65  +              ON package_notes(package);
           66  +  CREATE UNIQUE INDEX package_notes_bug
           67  +              ON package_notes(bug_name, package, release);
           68  +  
           69  +  CREATE TABLE debian_bugs
           70  +          (bug INTEGER NOT NULL,
           71  +           note INTEGER NOT NULL,
           72  +           PRIMARY KEY (bug, note));
           73  +  
           74  +  
           75  +  CREATE VIEW debian_cve AS
           76  +              SELECT DISTINCT debian_bugs.bug, st.bug_name
           77  +              FROM package_notes, debian_bugs, source_package_status AS st
           78  +              WHERE package_notes.bug_name = st.bug_name
           79  +              AND debian_bugs.note = package_notes.id
           80  +              ORDER BY debian_bugs.bug;
           81  +} {}
           82  +
           83  +# The following query should use an automatic index for the view
           84  +# in FROM clause of the subquery of the second result column.
           85  +#
           86  +do_execsql_test autoindex5-1.1 {
           87  +  EXPLAIN QUERY PLAN
           88  +  SELECT
           89  +    st.bug_name,
           90  +    (SELECT ALL debian_cve.bug FROM debian_cve
           91  +      WHERE debian_cve.bug_name = st.bug_name
           92  +      ORDER BY debian_cve.bug),
           93  +    sp.release
           94  +  FROM
           95  +     source_package_status AS st,
           96  +     source_packages AS sp,
           97  +     bugs
           98  +  WHERE
           99  +     sp.rowid = st.package
          100  +     AND st.bug_name = bugs.name
          101  +     AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
          102  +     AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
          103  +            OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
          104  +  ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
          105  +} {/SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX .bug_name=/}
          106  +    
          107  +
          108  +finish_test