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

Side by Side Diff: third_party/sqlite/sqlite-src-3100200/test/analyze7.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 # 2011 April 1

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 when an idnex

13 # name is given as the argument.

14 #

15

16 set testdir [file dirname $argv0]

17 source $testdir/tester.tcl

18

19 # There is nothing to test if ANALYZE is disable for this build.

20 #

21 ifcapable {!analyze\|\|!vtab} {

22 finish_test

23 return

24 }

25

26 # Generate some test data

27 #

28 do_test analyze7-1.0 {

29 load_static_extension db wholenumber

30 execsql {

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

32 CREATE INDEX t1a ON t1(a);

33 CREATE INDEX t1b ON t1(b);

34 CREATE INDEX t1cd ON t1(c,d);

35 CREATE VIRTUAL TABLE nums USING wholenumber;

36 INSERT INTO t1 SELECT value, value, value/100, value FROM nums

37 WHERE value BETWEEN 1 AND 256;

38 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;

39 }

40 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

41 do_test analyze7-1.1 {

42 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}

43 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}

44 do_test analyze7-1.2 {

45 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}

46 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

47

48 # Run an analyze on one of the three indices. Verify that this

49 # effects the row-count estimate on the one query that uses that

50 # one index.

51 #

52 do_test analyze7-2.0 {

53 execsql {ANALYZE t1a;}

54 db cache flush

55 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}

56 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

57 do_test analyze7-2.1 {

58 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}

59 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}

60 do_test analyze7-2.2 {

61 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}

62 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

63

64 # Verify that since the query planner now things that t1a is more

65 # selective than t1b, it prefers to use t1a.

66 #

67 do_test analyze7-2.3 {

68 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}

69 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

70

71 # Run an analysis on another of the three indices. Verify that this

72 # new analysis works and does not disrupt the previous analysis.

73 #

74 do_test analyze7-3.0 {

75 execsql {ANALYZE t1cd;}

76 db cache flush;

77 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}

78 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

79 do_test analyze7-3.1 {

80 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}

81 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}

82 do_test analyze7-3.2.1 {

83 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}

84 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

85 ifcapable stat4\|\|stat3 {

86 # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated

87 # row count for (c=2) than it does for (c=?).

88 do_test analyze7-3.2.2 {

89 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}

90 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

91 } else {

92 # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the

93 # same as that for (c=?).

94 do_test analyze7-3.2.3 {

95 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}

96 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

97 }

98 do_test analyze7-3.3 {

99 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}

100 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

101

102 ifcapable {!stat4 && !stat3} {

103 do_test analyze7-3.4 {

104 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}

105 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}

106 do_test analyze7-3.5 {

107 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}

108 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

109 }

110 do_test analyze7-3.6 {

111 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}

112 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)}}

113

114 finish_test

OLD	NEW