third_party/sqlite/sqlite-src-3080704/test/analyzeC.test - Issue 2363173002: [sqlite] Remove obsolete reference version 3.8.7.4.

Side by Side Diff: third_party/sqlite/sqlite-src-3080704/test/analyzeC.test

Issue 2363173002: [sqlite] Remove obsolete reference version 3.8.7.4. (Closed)

Patch Set: Created 4 years, 2 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-07-22

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 contains automated tests used to verify that the text terms

13 # at the end of sqlite_stat1.stat are processed correctly.

14 #

15 # (1) "unordered" means that the index cannot be used for ORDER BY

16 # or for range queries

17 #

18 # (2) "sz=NNN" sets the relative size of the index entries

19 #

20 # (3) All other fields are silently ignored

21 #

22

23 set testdir [file dirname $argv0]

24 source $testdir/tester.tcl

25 set testprefix analyzeC

26

27 # Baseline case. Range queries work OK. Indexes can be used for

28 # ORDER BY.

29 #

30 do_execsql_test 1.0 {

31 CREATE TABLE t1(a,b,c);

32 INSERT INTO t1(a,b,c)

33 VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111);

34 CREATE INDEX t1a ON t1(a);

35 CREATE INDEX t1b ON t1(b);

36 ANALYZE;

37 DELETE FROM sqlite_stat1;

38 INSERT INTO sqlite_stat1(tbl,idx,stat)

39 VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4');

40 ANALYZE sqlite_master;

41 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;

42 } {4 5 6 # 7 8 9 # 4 8 12 #}

43 do_execsql_test 1.1 {

44 EXPLAIN QUERY PLAN

45 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;

46 } {/.* USING INDEX t1a .a>. AND a<...*/}

47 do_execsql_test 1.2 {

48 SELECT c FROM t1 ORDER BY a;

49 } {3 111 6 12 9 12}

50 do_execsql_test 1.3 {

51 EXPLAIN QUERY PLAN

52 SELECT c FROM t1 ORDER BY a;

53 } {/.SCAN TABLE t1 USING INDEX t1a./}

54 do_execsql_test 1.3x {

55 EXPLAIN QUERY PLAN

56 SELECT c FROM t1 ORDER BY a;

57 } {~/.B-TREE FOR ORDER BY./}

58

59 # Now mark the t1a index as "unordered". Range queries and ORDER BY no

60 # longer use the index, but equality queries do.

61 #

62 do_execsql_test 2.0 {

63 UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a';

64 ANALYZE sqlite_master;

65 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;

66 } {4 5 6 # 7 8 9 # 4 8 12 #}

67 do_execsql_test 2.1 {

68 EXPLAIN QUERY PLAN

69 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;

70 } {~/.USING INDEX./}

71 do_execsql_test 2.2 {

72 SELECT c FROM t1 ORDER BY a;

73 } {3 111 6 12 9 12}

74 do_execsql_test 2.3 {

75 EXPLAIN QUERY PLAN

76 SELECT c FROM t1 ORDER BY a;

77 } {~/.USING INDEX./}

78 do_execsql_test 2.3x {

79 EXPLAIN QUERY PLAN

80 SELECT c FROM t1 ORDER BY a;

81 } {/.B-TREE FOR ORDER BY./}

82

83 # Ignore extraneous text parameters in the sqlite_stat1.stat field.

84 #

85 do_execsql_test 3.0 {

86 UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11'

87 WHERE idx='t1a';

88 ANALYZE sqlite_master;

89 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;

90 } {4 5 6 # 7 8 9 # 4 8 12 #}

91 do_execsql_test 3.1 {

92 EXPLAIN QUERY PLAN

93 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;

94 } {~/.USING INDEX./}

95 do_execsql_test 3.2 {

96 SELECT c FROM t1 ORDER BY a;

97 } {3 111 6 12 9 12}

98 do_execsql_test 3.3 {

99 EXPLAIN QUERY PLAN

100 SELECT c FROM t1 ORDER BY a;

101 } {~/.USING INDEX./}

102 do_execsql_test 3.3x {

103 EXPLAIN QUERY PLAN

104 SELECT c FROM t1 ORDER BY a;

105 } {/.B-TREE FOR ORDER BY./}

106

107 # The sz=NNN parameter determines which index to scan

108 #

109 do_execsql_test 4.0 {

110 DROP INDEX t1a;

111 CREATE INDEX t1ab ON t1(a,b);

112 CREATE INDEX t1ca ON t1(c,a);

113 DELETE FROM sqlite_stat1;

114 INSERT INTO sqlite_stat1(tbl,idx,stat)

115 VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20');

116 ANALYZE sqlite_master;

117 SELECT count(a) FROM t1;

118 } {6}

119 do_execsql_test 4.1 {

120 EXPLAIN QUERY PLAN

121 SELECT count(a) FROM t1;

122 } {/.INDEX t1ab./}

123 do_execsql_test 4.2 {

124 DELETE FROM sqlite_stat1;

125 INSERT INTO sqlite_stat1(tbl,idx,stat)

126 VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10');

127 ANALYZE sqlite_master;

128 SELECT count(a) FROM t1;

129 } {6}

130 do_execsql_test 4.3 {

131 EXPLAIN QUERY PLAN

132 SELECT count(a) FROM t1;

133 } {/.INDEX t1ca./}

134

135

136 # The sz=NNN parameter works even if there is other extraneous text

137 # in the sqlite_stat1.stat column.

138 #

139 do_execsql_test 5.0 {

140 DELETE FROM sqlite_stat1;

141 INSERT INTO sqlite_stat1(tbl,idx,stat)

142 VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'),

143 ('t1','t1ca','12345 3 2 whatever sz=20 junk');

144 ANALYZE sqlite_master;

145 SELECT count(a) FROM t1;

146 } {6}

147 do_execsql_test 5.1 {

148 EXPLAIN QUERY PLAN

149 SELECT count(a) FROM t1;

150 } {/.INDEX t1ab./}

151 do_execsql_test 5.2 {

152 DELETE FROM sqlite_stat1;

153 INSERT INTO sqlite_stat1(tbl,idx,stat)

154 VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'),

155 ('t1','t1ab','12345 3 2 whatever sz=20 junk');

156 ANALYZE sqlite_master;

157 SELECT count(a) FROM t1;

158 } {6}

159 do_execsql_test 5.3 {

160 EXPLAIN QUERY PLAN

161 SELECT count(a) FROM t1;

162 } {/.INDEX t1ca./}

163

164

165

166

167 finish_test

OLD	NEW