modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/autoindex5.test (about)

     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  set testprefix autoindex5
    21  
    22  # Schema is from the Debian security database
    23  #
    24  do_execsql_test autoindex5-1.0 {
    25    CREATE TABLE source_package_status
    26            (bug_name TEXT NOT NULL,
    27             package INTEGER NOT NULL,
    28             vulnerable INTEGER NOT NULL,
    29             urgency TEXT NOT NULL,
    30             PRIMARY KEY (bug_name, package));
    31    CREATE INDEX source_package_status_package
    32                ON source_package_status(package);
    33    
    34    CREATE TABLE source_packages
    35                (name TEXT NOT NULL,
    36                release TEXT NOT NULL,
    37                subrelease TEXT NOT NULL,
    38                archive TEXT NOT NULL,
    39                version TEXT NOT NULL,
    40                version_id INTEGER NOT NULL DEFAULT 0,
    41                PRIMARY KEY (name, release, subrelease, archive));
    42    
    43    CREATE TABLE bugs
    44            (name TEXT NOT NULL PRIMARY KEY,
    45             cve_status TEXT NOT NULL
    46                 CHECK (cve_status IN
    47                        ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
    48             not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
    49             description TEXT NOT NULL,
    50             release_date TEXT NOT NULL,
    51             source_file TEXT NOT NULL,
    52             source_line INTEGER NOT NULL);
    53    
    54    CREATE TABLE package_notes
    55            (id INTEGER NOT NULL PRIMARY KEY,
    56             bug_name TEXT NOT NULL,
    57             package TEXT NOT NULL,
    58             fixed_version TEXT
    59                 CHECK (fixed_version IS NULL OR fixed_version <> ''),
    60             fixed_version_id INTEGER NOT NULL DEFAULT 0,
    61             release TEXT NOT NULL,
    62             package_kind TEXT NOT NULL DEFAULT 'unknown',
    63             urgency TEXT NOT NULL,
    64             bug_origin TEXT NOT NULL DEFAULT '');
    65    CREATE INDEX package_notes_package
    66                ON package_notes(package);
    67    CREATE UNIQUE INDEX package_notes_bug
    68                ON package_notes(bug_name, package, release);
    69    
    70    CREATE TABLE debian_bugs
    71            (bug INTEGER NOT NULL,
    72             note INTEGER NOT NULL,
    73             PRIMARY KEY (bug, note));
    74    
    75    
    76    CREATE VIEW debian_cve AS
    77                SELECT DISTINCT debian_bugs.bug, st.bug_name
    78                FROM package_notes, debian_bugs, source_package_status AS st
    79                WHERE package_notes.bug_name = st.bug_name
    80                AND debian_bugs.note = package_notes.id
    81                ORDER BY debian_bugs.bug;
    82  } {}
    83  
    84  # The following query should use an automatic index for the view
    85  # in FROM clause of the subquery of the second result column.
    86  #
    87  do_execsql_test autoindex5-1.1 {
    88    EXPLAIN QUERY PLAN
    89    SELECT
    90      st.bug_name,
    91      (SELECT ALL debian_cve.bug FROM debian_cve
    92        WHERE debian_cve.bug_name = st.bug_name
    93        ORDER BY debian_cve.bug),
    94      sp.release
    95    FROM
    96       source_package_status AS st,
    97       source_packages AS sp,
    98       bugs
    99    WHERE
   100       sp.rowid = st.package
   101       AND st.bug_name = bugs.name
   102       AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
   103       AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
   104              OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
   105    ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
   106  } {/SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX .bug_name=/}
   107  
   108  #-------------------------------------------------------------------------
   109  # Test that ticket [8a2adec1] has been fixed.
   110  #
   111  do_execsql_test 2.1 {
   112    CREATE TABLE one(o);
   113    INSERT INTO one DEFAULT VALUES;
   114  
   115    CREATE TABLE t1(x, z);
   116    INSERT INTO t1 VALUES('aaa', 4.0);
   117    INSERT INTO t1 VALUES('aaa', 4.0);
   118    CREATE VIEW vvv AS
   119      SELECT * FROM t1
   120      UNION ALL
   121      SELECT 0, 0 WHERE 0;
   122  
   123    SELECT (
   124        SELECT sum(z) FROM vvv WHERE x='aaa'
   125    ) FROM one;
   126  } {8.0}
   127      
   128  
   129  finish_test