third_party/sqlite/sqlite-src-3100200/test/autoindex5.test - Issue 2846743003: [sql] Remove SQLite 3.10.2 reference directory.

Side by Side Diff: third_party/sqlite/sqlite-src-3100200/test/autoindex5.test

Issue 2846743003: [sql] Remove SQLite 3.10.2 reference directory. (Closed)

Patch Set: Created 3 years, 7 months ago

Use n/p to move between diff chunks; N/P to move between comments. Draft comments are only viewable by you.

Jump to:

OLD	NEW
	(Empty)
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

OLD	NEW