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 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20set testprefix autoindex5 21 22# Schema is from the Debian security database 23# 24do_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# 87do_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# 111do_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 129finish_test 130