xref: /sqlite-3.40.0/test/cursorhint.test (revision bcf40a7f) 
1# 2014 July 15
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#
13
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix cursorhint
17
18do_execsql_test 1.0 {
19  CREATE TABLE t1(a,b,c,d);
20  CREATE TABLE t2(x,y,z);
21  INSERT INTO t1(a,b) VALUES(10, 15);
22  INSERT INTO t1(a,b) VALUES(20, 25);
23  INSERT INTO t2(x,y) VALUES('ten', 'fifteen');
24  INSERT INTO t2(x,y) VALUES('twenty', 'twentyfive');
25  CREATE TABLE t3(id TEXT PRIMARY KEY, a, b, c, d) WITHOUT ROWID;
26  INSERT INTO t3(id,a,b,c,d) SELECT rowid, a, b, c, d FROM t1;
27  PRAGMA automatic_index = 0;
28}
29
30# Run EXPLAIN on $sql.  Return a list of P4 values for all $opcode
31# opcodes.
32#
33proc p4_of_opcode {db opcode sql} {
34  set res {}
35  $db eval "EXPLAIN $sql" x {
36    if {$x(opcode)==$opcode} {lappend res $x(p4)}
37  }
38  return $res
39}
40
41# Run EXPLAIN on $sql.  Return a list of P5 values for all $opcode
42# opcodes that contain regexp $comment in their comment
43#
44proc p5_of_opcode {db opcode comment sql} {
45  set res {}
46  $db eval "EXPLAIN $sql" x {
47    if {$x(opcode)==$opcode && [regexp $comment $x(comment)]} {
48      lappend res $x(p5)
49    }
50  }
51  return $res
52}
53
54# Verify that when t1 is in the outer loop and t2 is in the inner loop,
55# no cursor hints occur for t1 (since it is a full table scan) but that
56# each t2 access has a cursor hint based on the current t1.a value.
57#
58do_test 1.1 {
59  p4_of_opcode db CursorHint {
60     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
61  }
62} {{EQ(r[1],c0)}}
63do_test 1.2 {
64  p5_of_opcode db OpenRead . {
65     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
66  }
67} {00 00}
68
69# Do the same test the other way around.
70#
71do_test 2.1 {
72  p4_of_opcode db CursorHint {
73     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
74  }
75} {{EQ(c0,r[1])}}
76do_test 2.2 {
77  p5_of_opcode db OpenRead . {
78     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
79  }
80} {00 00}
81
82# Various expressions captured by CursorHint
83#
84do_test 3.1 {
85  p4_of_opcode db CursorHint {
86    SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98
87  }
88} {AND(AND(EQ(c0,15),EQ(c2,22)),NE(rowid,98))}
89do_test 3.2 {
90  p4_of_opcode db CursorHint {
91    SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98
92  }
93} {AND(AND(LT(c1,15),GT(c2,22)),NE(c0,98))}
94
95# Indexed queries
96#
97do_test 4.1asc {
98  db eval {
99    CREATE INDEX t1bc ON t1(b,c);
100    CREATE INDEX t2yz ON t2(y,z);
101  }
102  p4_of_opcode db CursorHint {
103    SELECT * FROM t1 WHERE b>11 ORDER BY b ASC;
104  }
105} {}
106do_test 4.1desc {
107  p4_of_opcode db CursorHint {
108    SELECT * FROM t1 WHERE b>11 ORDER BY b DESC;
109  }
110} {GT(c0,11)}
111do_test 4.2 {
112  p5_of_opcode db OpenRead . {
113    SELECT * FROM t1 WHERE b>11;
114  }
115} {02 00}
116do_test 4.3asc {
117  p4_of_opcode db CursorHint {
118    SELECT c FROM t1 WHERE b<11 ORDER BY b ASC;
119  }
120} {LT(c0,11)}
121do_test 4.3desc {
122  p4_of_opcode db CursorHint {
123    SELECT c FROM t1 WHERE b<11 ORDER BY b DESC;
124  }
125} {}
126do_test 4.4 {
127  p5_of_opcode db OpenRead . {
128    SELECT c FROM t1 WHERE b<11;
129  }
130} {00}
131
132do_test 4.5asc {
133  p4_of_opcode db CursorHint {
134    SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b ASC;
135  }
136} {LE(c0,20)}
137do_test 4.5desc {
138  p4_of_opcode db CursorHint {
139    SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b DESC;
140  }
141} {GE(c0,10)}
142
143# If there are any equality terms used in the constraint, then all terms
144# should be hinted.
145#
146do_test 4.6asc {
147  p4_of_opcode db CursorHint {
148    SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c ASC;
149  }
150} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))}
151do_test 4.6desc {
152  p4_of_opcode db CursorHint {
153    SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c DESC;
154  }
155} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))}
156
157finish_test
158