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

Side by Side Diff: third_party/sqlite/sqlite-src-3080704/test/analyzeD.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 # 2005 July 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 # This file implements regression tests for SQLite library.

12 # This file implements tests for the ANALYZE command.

13 #

14 # $Id: analyze.test,v 1.9 2008/08/11 18:44:58 drh Exp $

15

16 set testdir [file dirname $argv0]

17 source $testdir/tester.tcl

18 set ::testprefix analyzeD

19

20 ifcapable {!stat4} {

21 finish_test

22 return

23 }

24

25

26 # Set up a table with the following properties:

27 #

28 # * Contains 1000 rows.

29 # * Column a contains even integers between 0 and 18, inclusive (so that

30 # a=? for any such integer matches 100 rows).

31 # * Column b contains integers between 0 and 9, inclusive.

32 # * Column c contains integers between 0 and 199, inclusive (so that

33 # for any such integer, c=? matches 5 rows).

34 # * Then add 7 rows with a new value for "a" - 3001. The stat4 table will

35 # not contain any samples with a=3001.

36 #

37 do_execsql_test 1.0 {

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

39 }

40 do_test 1.1 {

41 for {set i 1} {$i < 1000} {incr i} {

42 set c [expr $i % 200]

43 execsql { INSERT INTO t1(a, b, c) VALUES( 2*($i/100), $i%10, $c ) }

44 }

45

46 execsql {

47 INSERT INTO t1 VALUES(3001, 3001, 3001);

48 INSERT INTO t1 VALUES(3001, 3001, 3002);

49 INSERT INTO t1 VALUES(3001, 3001, 3003);

50 INSERT INTO t1 VALUES(3001, 3001, 3004);

51 INSERT INTO t1 VALUES(3001, 3001, 3005);

52 INSERT INTO t1 VALUES(3001, 3001, 3006);

53 INSERT INTO t1 VALUES(3001, 3001, 3007);

54

55 CREATE INDEX t1_ab ON t1(a, b);

56 CREATE INDEX t1_c ON t1(c);

57

58 ANALYZE;

59 }

60 } {}

61

62 # With full ANALYZE data, SQLite sees that c=150 (5 rows) is better than

63 # a=3001 (7 rows).

64 #

65 do_eqp_test 1.2 {

66 SELECT * FROM t1 WHERE a=3001 AND c=150;

67 } {

68 0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)}

69 }

70

71 do_test 1.3 {

72 execsql { DELETE FROM sqlite_stat1 }

73 db close

74 sqlite3 db test.db

75 } {}

76

77 # Without stat1, because 3001 is larger than all samples in the stat4

78 # table, SQLite things that a=3001 matches just 1 row. So it (incorrectly)

79 # chooses it over the c=150 index (5 rows). Even with stat1 data, things

80 # worked this way before commit [e6f7f97dbc].

81 #

82 do_eqp_test 1.4 {

83 SELECT * FROM t1 WHERE a=3001 AND c=150;

84 } {

85 0 0 0 {SEARCH TABLE t1 USING INDEX t1_ab (a=?)}

86 }

87

88 do_test 1.5 {

89 execsql {

90 UPDATE t1 SET a=13 WHERE a = 3001;

91 ANALYZE;

92 }

93 } {}

94

95 do_eqp_test 1.6 {

96 SELECT * FROM t1 WHERE a=13 AND c=150;

97 } {

98 0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)}

99 }

100

101 do_test 1.7 {

102 execsql { DELETE FROM sqlite_stat1 }

103 db close

104 sqlite3 db test.db

105 } {}

106

107 # Same test as 1.4, except this time the 7 rows that match the a=? condition

108 # do not feature larger values than all rows in the stat4 table. So SQLite

109 # gets this right, even without stat1 data.

110 do_eqp_test 1.8 {

111 SELECT * FROM t1 WHERE a=13 AND c=150;

112 } {

113 0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)}

114 }

115

116 finish_test

117

OLD	NEW