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

Side by Side Diff: third_party/sqlite/sqlite-src-3100200/test/orderby5.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 # 2013-06-14

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. The

12 # focus of this file is testing that the optimizations that disable

13 # ORDER BY clauses work correctly

14 #

15

16

17 set testdir [file dirname $argv0]

18 source $testdir/tester.tcl

19 set ::testprefix orderby5

20

21 # Generate test data for a join. Verify that the join gets the

22 # correct answer.

23 #

24 do_execsql_test 1.1 {

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

26 CREATE INDEX t1bc ON t1(b,c);

27

28 EXPLAIN QUERY PLAN

29 SELECT DISTINCT a, b, c FROM t1 WHERE a=0;

30 } {~/B-TREE/}

31 do_execsql_test 1.2.1 {

32 EXPLAIN QUERY PLAN

33 SELECT DISTINCT a, c, b FROM t1 WHERE a=0;

34 } {~/B-TREE/}

35 do_execsql_test 1.2.2 {

36 EXPLAIN QUERY PLAN

37 SELECT DISTINCT a, c, b FROM t1 WHERE a='xyz' COLLATE nocase;

38 } {/B-TREE/}

39 do_execsql_test 1.2.3 {

40 EXPLAIN QUERY PLAN

41 SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz';

42 } {/B-TREE/}

43 do_execsql_test 1.2.4 {

44 EXPLAIN QUERY PLAN

45 SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz' COLLATE nocase;

46 } {~/B-TREE/}

47 do_execsql_test 1.3 {

48 EXPLAIN QUERY PLAN

49 SELECT DISTINCT b, a, c FROM t1 WHERE a=0;

50 } {~/B-TREE/}

51 do_execsql_test 1.4 {

52 EXPLAIN QUERY PLAN

53 SELECT DISTINCT b, c, a FROM t1 WHERE a=0;

54 } {~/B-TREE/}

55 do_execsql_test 1.5 {

56 EXPLAIN QUERY PLAN

57 SELECT DISTINCT c, a, b FROM t1 WHERE a=0;

58 } {~/B-TREE/}

59 do_execsql_test 1.6 {

60 EXPLAIN QUERY PLAN

61 SELECT DISTINCT c, b, a FROM t1 WHERE a=0;

62 } {~/B-TREE/}

63 do_execsql_test 1.7 {

64 EXPLAIN QUERY PLAN

65 SELECT DISTINCT c, b, a FROM t1 WHERE +a=0;

66 } {/B-TREE/}

67

68 # In some cases, it is faster to do repeated index lookups than it is to

69 # sort. But in other cases, it is faster to sort than to do repeated index

70 # lookups.

71 #

72 do_execsql_test 2.1a {

73 CREATE TABLE t2(a,b,c);

74 CREATE INDEX t2bc ON t2(b,c);

75 ANALYZE;

76 INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9');

77 INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5');

78 ANALYZE sqlite_master;

79

80 EXPLAIN QUERY PLAN

81 SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;

82 } {~/B-TREE/}

83

84 do_execsql_test 2.1b {

85 EXPLAIN QUERY PLAN

86 SELECT * FROM t1 WHERE likelihood(a=0, 0.05) ORDER BY a, b, c;

87 } {/B-TREE/}

88

89 do_execsql_test 2.2 {

90 EXPLAIN QUERY PLAN

91 SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;

92 } {/B-TREE/}

93 do_execsql_test 2.3 {

94 EXPLAIN QUERY PLAN

95 SELECT * FROM t1 WHERE a=0 ORDER BY b, a, c;

96 } {~/B-TREE/}

97 do_execsql_test 2.4 {

98 EXPLAIN QUERY PLAN

99 SELECT * FROM t1 WHERE a=0 ORDER BY b, c, a;

100 } {~/B-TREE/}

101 do_execsql_test 2.5 {

102 EXPLAIN QUERY PLAN

103 SELECT * FROM t1 WHERE a=0 ORDER BY a, c, b;

104 } {/B-TREE/}

105 do_execsql_test 2.6 {

106 EXPLAIN QUERY PLAN

107 SELECT * FROM t1 WHERE a=0 ORDER BY c, a, b;

108 } {/B-TREE/}

109 do_execsql_test 2.7 {

110 EXPLAIN QUERY PLAN

111 SELECT * FROM t1 WHERE a=0 ORDER BY c, b, a;

112 } {/B-TREE/}

113

114

115 do_execsql_test 3.0 {

116 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f);

117 CREATE INDEX t3bcde ON t3(b, c, d, e);

118 EXPLAIN QUERY PLAN

119 SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;

120 } {~/B-TREE/}

121 do_execsql_test 3.1 {

122 DROP TABLE t3;

123 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f) WITHOUT rowid;

124 CREATE INDEX t3bcde ON t3(b, c, d, e);

125 EXPLAIN QUERY PLAN

126 SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;

127 } {~/B-TREE/}

128

129

130 finish_test

OLD	NEW