blob: a78d151b98f70b3606c0887d7b7d3241e97e014c [file] [log] [blame]
Kaido Kert25902c62024-06-17 17:10:28 -07001# 2016 June 17
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 is on testing that cursor-hints are correct for queries
13# involving LEFT JOIN.
14#
15
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set ::testprefix cursorhint2
20
21ifcapable !cursorhints {
22 finish_test
23 return
24}
25
26proc extract_hints {sql} {
27
28 db eval "SELECT tbl_name, rootpage FROM sqlite_master where rootpage" {
29 set lookup($rootpage) $tbl_name
30 }
31
32 set ret [list]
33 db eval "EXPLAIN $sql" a {
34 switch -- $a(opcode) {
35 OpenRead {
36 set csr($a(p1)) $lookup($a(p2))
37 }
38 CursorHint {
39 lappend ret $csr($a(p1)) $a(p4)
40 }
41 }
42 }
43
44 set ret
45}
46
47proc do_extract_hints_test {tn sql ret} {
48 uplevel [list do_test $tn [list extract_hints $sql] [list {*}$ret]]
49}
50
51do_execsql_test 1.0 {
52 PRAGMA automatic_index = 0;
53 CREATE TABLE t1(a, b);
54 CREATE TABLE t2(c, d);
55 CREATE TABLE t3(e, f);
56}
57
58do_extract_hints_test 1.1 {
59 SELECT * FROM t1 WHERE a=1;
60} {
61 t1 EQ(c0,1)
62}
63
64do_extract_hints_test 1.2 {
65 SELECT * FROM t1 CROSS JOIN t2 ON (a=c) WHERE d IS NULL;
66} {
67 t2 {AND(ISNULL(c1),EQ(r[1],c0))}
68}
69
70do_extract_hints_test 1.3 {
71 SELECT * FROM t1 LEFT JOIN t2 ON (a=c) WHERE d IS NULL;
72} {
73 t2 {EQ(r[2],c0)}
74}
75
76do_extract_hints_test 1.4 {
77 SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND a=10) WHERE d IS NULL;
78} {
79 t2 {AND(EQ(r[2],c0),EQ(r[3],10))}
80}
81
82do_extract_hints_test 1.5 {
83 SELECT * FROM t1 CROSS JOIN t2 ON (a=c AND a=10) WHERE d IS NULL;
84} {
85 t1 EQ(c0,10) t2 {AND(ISNULL(c1),EQ(r[3],c0))}
86}
87
88do_extract_hints_test 1.6 {
89 SELECT * FROM t1 LEFT JOIN t2 ON (a=c) LEFT JOIN t3 ON (d=f);
90} {
91 t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
92}
93
94if 0 {
95 do_extract_hints_test 1.7 {
96 SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f);
97 } {
98 t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))}
99 }
100}
101
102#-------------------------------------------------------------------------
103#
104do_execsql_test 2.0 {
105 CREATE TABLE x1(x, y);
106 CREATE TABLE x2(a, b);
107}
108
109do_extract_hints_test 2.1 {
110 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL;
111} {
112 x2 {EQ(c0,r[2])}
113}
114
115do_extract_hints_test 2.2 {
116 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL;
117} {
118 x2 {EQ(c0,r[2])}
119}
120
121do_extract_hints_test 2.3 {
122 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL)
123} {
124 x2 {EQ(c0,r[2])}
125}
126
127do_extract_hints_test 2.4 {
128 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
129} {
130 x2 {EQ(c0,r[2])}
131}
132
133do_extract_hints_test 2.5 {
134 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
135} {
136 x2 {EQ(c0,r[2])}
137}
138
139if {0} {
140 # These tests no longer work due to the LEFT-JOIN strength reduction
141 # optimization
142 do_extract_hints_test 2.6 {
143 SELECT * FROM x1 CROSS JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
144 } {
145 x2 {EQ(c0,r[2])}
146 }
147
148 do_extract_hints_test 2.7 {
149 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
150 } {
151 x2 {EQ(c0,r[2])}
152 }
153
154 do_extract_hints_test 2.8 {
155 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
156 } {
157 x2 {EQ(c0,r[2])}
158 }
159
160 do_extract_hints_test 2.9 {
161 SELECT * FROM x1 LEFT JOIN x2 ON (a=x)
162 WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
163 } {
164 x2 {EQ(c0,r[2])}
165 }
166
167 do_extract_hints_test 2.10 {
168 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
169 } {
170 x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
171 }
172
173 ifcapable !icu {
174 # This test only works using the built-in LIKE, not the ICU LIKE extension.
175 do_extract_hints_test 2.11 {
176 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
177 } {
178 x2 {AND(expr,EQ(c0,r[2]))}
179 }
180 }
181}
182
183do_extract_hints_test 2.12 {
184 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
185} {
186 x2 {EQ(c0,r[2])}
187}
188
189reset_db
190do_execsql_test 3.0 {
191 CREATE TABLE t1 (i1 TEXT);
192 CREATE TABLE t2 (i2 TEXT UNIQUE);
193 INSERT INTO t1 VALUES('0');
194 INSERT INTO t2 VALUES('0');
195}
196
197do_extract_hints_test 3.1 {
198 SELECT * FROM t1 CROSS JOIN t2 WHERE (t1.i1 = t2.i2) AND t2.i2 = 1;
199} {
200 t1 {EQ(c0,r[1])} t2 EQ(c0,1)
201}
202
203
204finish_test