Debian Codegraph @

test/whereG.test

raw
1 # 2013-09-05
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 #
12 # Test cases for query planning decisions and the likely(), unlikely(), and
13 # likelihood() functions.
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix whereG
18
19 do_execsql_test whereG-1.0 {
20 CREATE TABLE composer(
21 cid INTEGER PRIMARY KEY,
22 cname TEXT
23 );
24 CREATE TABLE album(
25 aid INTEGER PRIMARY KEY,
26 aname TEXT
27 );
28 CREATE TABLE track(
29 tid INTEGER PRIMARY KEY,
30 cid INTEGER REFERENCES composer,
31 aid INTEGER REFERENCES album,
32 title TEXT
33 );
34 CREATE INDEX track_i1 ON track(cid);
35 CREATE INDEX track_i2 ON track(aid);
36 INSERT INTO composer VALUES(1, 'W. A. Mozart');
37 INSERT INTO composer VALUES(2, 'Beethoven');
38 INSERT INTO composer VALUES(3, 'Thomas Tallis');
39 INSERT INTO composer VALUES(4, 'Joseph Hayden');
40 INSERT INTO composer VALUES(5, 'Thomas Weelkes');
41 INSERT INTO composer VALUES(6, 'J. S. Bach');
42 INSERT INTO composer VALUES(7, 'Orlando Gibbons');
43 INSERT INTO composer VALUES(8, 'Josquin des Prés');
44 INSERT INTO composer VALUES(9, 'Byrd');
45 INSERT INTO composer VALUES(10, 'Francis Poulenc');
46 INSERT INTO composer VALUES(11, 'Mendelsshon');
47 INSERT INTO composer VALUES(12, 'Zoltán Kodály');
48 INSERT INTO composer VALUES(13, 'Handel');
49 INSERT INTO album VALUES(100, 'Kodály: Missa Brevis');
50 INSERT INTO album VALUES(101, 'Messiah');
51 INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65');
52 INSERT INTO album VALUES(103, 'The complete English anthems');
53 INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232');
54 INSERT INTO track VALUES(10005, 12, 100, 'Sanctus');
55 INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei');
56 INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs');
57 INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death');
58 INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei');
59 INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me');
60 INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus');
61 INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis');
62 } {}
63 do_eqp_test whereG-1.1 {
64 SELECT DISTINCT aname
65 FROM album, composer, track
66 WHERE unlikely(cname LIKE '%bach%')
67 AND composer.cid=track.cid
68 AND album.aid=track.aid;
69 } {composer*track*album}
70 do_execsql_test whereG-1.2 {
71 SELECT DISTINCT aname
72 FROM album, composer, track
73 WHERE unlikely(cname LIKE '%bach%')
74 AND composer.cid=track.cid
75 AND album.aid=track.aid;
76 } {{Mass in B Minor, BWV 232}}
77
78 do_eqp_test whereG-1.3 {
79 SELECT DISTINCT aname
80 FROM album, composer, track
81 WHERE likelihood(cname LIKE '%bach%', 0.5)
82 AND composer.cid=track.cid
83 AND album.aid=track.aid;
84 } {/.*track.*composer.*album.*/}
85 do_execsql_test whereG-1.4 {
86 SELECT DISTINCT aname
87 FROM album, composer, track
88 WHERE likelihood(cname LIKE '%bach%', 0.5)
89 AND composer.cid=track.cid
90 AND album.aid=track.aid;
91 } {{Mass in B Minor, BWV 232}}
92
93 do_eqp_test whereG-1.5 {
94 SELECT DISTINCT aname
95 FROM album, composer, track
96 WHERE cname LIKE '%bach%'
97 AND composer.cid=track.cid
98 AND album.aid=track.aid;
99 } {/.*track.*(composer.*album|album.*composer).*/}
100 do_execsql_test whereG-1.6 {
101 SELECT DISTINCT aname
102 FROM album, composer, track
103 WHERE cname LIKE '%bach%'
104 AND composer.cid=track.cid
105 AND album.aid=track.aid;
106 } {{Mass in B Minor, BWV 232}}
107
108 do_eqp_test whereG-1.7 {
109 SELECT DISTINCT aname
110 FROM album, composer, track
111 WHERE cname LIKE '%bach%'
112 AND unlikely(composer.cid=track.cid)
113 AND unlikely(album.aid=track.aid);
114 } {/.*track.*(composer.*album|album.*composer).*/}
115 do_execsql_test whereG-1.8 {
116 SELECT DISTINCT aname
117 FROM album, composer, track
118 WHERE cname LIKE '%bach%'
119 AND unlikely(composer.cid=track.cid)
120 AND unlikely(album.aid=track.aid);
121 } {{Mass in B Minor, BWV 232}}
122
123 do_test whereG-2.1 {
124 catchsql {
125 SELECT DISTINCT aname
126 FROM album, composer, track
127 WHERE likelihood(cname LIKE '%bach%', -0.01)
128 AND composer.cid=track.cid
129 AND album.aid=track.aid;
130 }
131 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
132 do_test whereG-2.2 {
133 catchsql {
134 SELECT DISTINCT aname
135 FROM album, composer, track
136 WHERE likelihood(cname LIKE '%bach%', 1.01)
137 AND composer.cid=track.cid
138 AND album.aid=track.aid;
139 }
140 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
141 do_test whereG-2.3 {
142 catchsql {
143 SELECT DISTINCT aname
144 FROM album, composer, track
145 WHERE likelihood(cname LIKE '%bach%', track.cid)
146 AND composer.cid=track.cid
147 AND album.aid=track.aid;
148 }
149 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
150
151 # Commuting a term of the WHERE clause should not change the query plan
152 #
153 do_execsql_test whereG-3.0 {
154 CREATE TABLE a(a1 PRIMARY KEY, a2);
155 CREATE TABLE b(b1 PRIMARY KEY, b2);
156 } {}
157 do_eqp_test whereG-3.1 {
158 SELECT * FROM a, b WHERE b1=a1 AND a2=5;
159 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
160 do_eqp_test whereG-3.2 {
161 SELECT * FROM a, b WHERE a1=b1 AND a2=5;
162 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
163 do_eqp_test whereG-3.3 {
164 SELECT * FROM a, b WHERE a2=5 AND b1=a1;
165 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
166 do_eqp_test whereG-3.4 {
167 SELECT * FROM a, b WHERE a2=5 AND a1=b1;
168 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
169
170 # Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]:
171 # Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy
172 # where an OP_Copy was needed.
173 #
174 do_execsql_test whereG-4.0 {
175 CREATE TABLE t4(x);
176 INSERT INTO t4 VALUES('right'),('wrong');
177 SELECT DISTINCT x
178 FROM (SELECT x FROM t4 GROUP BY x)
179 WHERE x='right'
180 ORDER BY x;
181 } {right}
182
183 #-------------------------------------------------------------------------
184 # Test that likelihood() specifications on indexed terms are taken into
185 # account by various forms of loops.
186 #
187 # 5.1.*: open ended range scans
188 # 5.2.*: skip-scans
189 #
190 reset_db
191
192 do_execsql_test 5.1 {
193 CREATE TABLE t1(a, b, c);
194 CREATE INDEX i1 ON t1(a, b);
195 }
196 do_eqp_test 5.1.2 {
197 SELECT * FROM t1 WHERE a>?
198 } {SEARCH t1 USING INDEX i1 (a>?)}
199 do_eqp_test 5.1.3 {
200 SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
201 } {SCAN t1}
202 do_eqp_test 5.1.4 {
203 SELECT * FROM t1 WHERE likely(a>?)
204 } {SCAN t1}
205
206 do_test 5.2 {
207 for {set i 0} {$i < 100} {incr i} {
208 execsql { INSERT INTO t1 VALUES('abc', $i, $i); }
209 }
210 execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; }
211 execsql { ANALYZE }
212 } {}
213 do_eqp_test 5.2.2 {
214 SELECT * FROM t1 WHERE likelihood(b>?, 0.01)
215 } {SEARCH t1 USING INDEX i1 (ANY(a) AND b>?)}
216 do_eqp_test 5.2.3 {
217 SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
218 } {SCAN t1}
219 do_eqp_test 5.2.4 {
220 SELECT * FROM t1 WHERE likely(b>?)
221 } {SCAN t1}
222
223 ifcapable stat4 {
224 do_eqp_test 5.3.1.stat4 {
225 SELECT * FROM t1 WHERE a=?
226 } {SCAN t1}
227 } else {
228 do_eqp_test 5.3.1 {
229 SELECT * FROM t1 WHERE a=?
230 } {SEARCH t1 USING INDEX i1}
231 }
232 do_eqp_test 5.3.2 {
233 SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
234 } {SCAN t1}
235 do_eqp_test 5.3.3 {
236 SELECT * FROM t1 WHERE likely(a=?)
237 } {SCAN t1}
238
239 # 2015-06-18
240 # Ticket [https://sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70]
241 #
242 do_execsql_test 6.0 {
243 DROP TABLE IF EXISTS t1;
244 CREATE TABLE t1(i int, x, y, z);
245 INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4);
246 DROP TABLE IF EXISTS t2;
247 CREATE TABLE t2(i int, bool char);
248 INSERT INTO t2 VALUES(1,'T'), (2,'F');
249 SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T';
250 SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND bool='T';
251 } {4 4}
252
253 # 2015-06-20
254 # Crash discovered by AFL
255 #
256 do_execsql_test 7.0 {
257 DROP TABLE IF EXISTS t1;
258 CREATE TABLE t1(a, b, PRIMARY KEY(a,b));
259 INSERT INTO t1 VALUES(9,1),(1,2);
260 DROP TABLE IF EXISTS t2;
261 CREATE TABLE t2(x, y, PRIMARY KEY(x,y));
262 INSERT INTO t2 VALUES(3,3),(4,4);
263 SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2;
264 } {1 3 1 4 9 3 9 4}
265 do_execsql_test 7.1 {
266 SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2;
267 } {1 3 1 4 9 3 9 4}
268 do_execsql_test 7.2 {
269 SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2;
270 } {1 3 1 4 9 3 9 4}
271 do_execsql_test 7.3 {
272 SELECT coalesce(a,a), x FROM t1, t2 ORDER BY 1, 2;
273 } {1 3 1 4 9 3 9 4}
274
275 # 2019-08-22
276 # Ticket https://sqlite.org/src/info/7e07a3dbf5a8cd26
277 #
278 do_execsql_test 8.1 {
279 DROP TABLE IF EXISTS t0;
280 CREATE TABLE t0 (c0);
281 INSERT INTO t0(c0) VALUES ('a');
282 SELECT LIKELY(t0.rowid) <= '0' FROM t0;
283 } {1}
284 do_execsql_test 8.2 {
285 SELECT * FROM t0 WHERE LIKELY(t0.rowid) <= '0';
286 } {a}
287 do_execsql_test 8.3 {
288 SELECT (t0.rowid) <= '0' FROM t0;
289 } {0}
290 do_execsql_test 8.4 {
291 SELECT * FROM t0 WHERE (t0.rowid) <= '0';
292 } {}
293 do_execsql_test 8.5 {
294 SELECT unlikely(t0.rowid) <= '0', likelihood(t0.rowid,0.5) <= '0' FROM t0;
295 } {1 1}
296 do_execsql_test 8.6 {
297 SELECT * FROM t0 WHERE unlikely(t0.rowid) <= '0';
298 } {a}
299 do_execsql_test 8.7 {
300 SELECT * FROM t0 WHERE likelihood(t0.rowid, 0.5) <= '0';
301 } {a}
302 do_execsql_test 8.8 {
303 SELECT unlikely(t0.rowid <= '0'),
304 likely(t0.rowid <= '0'),
305 likelihood(t0.rowid <= '0',0.5)
306 FROM t0;
307 } {0 0 0}
308 do_execsql_test 8.9 {
309 SELECT * FROM t0 WHERE unlikely(t0.rowid <= '0');
310 } {}
311 do_execsql_test 8.10 {
312 SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5);
313 } {}
314 # Forum https://sqlite.org/forum/forumpost/45ec3d9788
315 reset_db
316 do_execsql_test 8.11 {
317 CREATE TABLE t1(c0 INT);
318 INSERT INTO t1(c0) VALUES (NULL);
319 CREATE INDEX i46 ON t1(CAST( (c0 IS TRUE) AS TEXT));
320 CREATE VIEW v0(c2) AS SELECT CAST( (c0 IS TRUE) AS TEXT ) FROM t1;
321 }
322 do_execsql_test 8.12 {
323 SELECT quote(c0), quote(c2) FROM t1, v0 WHERE (0 < LIKELY(v0.c2));
324 } {NULL '0'}
325 do_execsql_test 8.13 {
326 SELECT quote(c0), quote(c2) FROM t1, v0 WHERE (0 < LIKELY(v0.c2)) IS TRUE;
327 } {NULL '0'}
328
329 # 2019-12-31: assertion fault discovered by Yongheng's fuzzer.
330 # Harmless memIsValid() due to the code generators failure to
331 # release the registers used by OP_ResultRow.
332 #
333 do_execsql_test 9.10 {
334 DROP TABLE IF EXISTS t1;
335 CREATE TABLE t1(a, b FLOAT);
336 INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
337 SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
338 } {NULL '' 'X'}
339
340 # 2020-06-14: assert() changed back into testcase()
341 # ticket 9fb26d37cefaba40
342 #
343 reset_db
344 do_execsql_test 10.1 {
345 CREATE TABLE a(b TEXT); INSERT INTO a VALUES(0),(4),(9);
346 CREATE TABLE c(d NUM);
347 CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c;
348 SELECT g = g FROM f GROUP BY h;
349 } {1}
350
351 reset_db
352 do_execsql_test 11.0 {
353 CREATE TABLE t1(x PRIMARY KEY, y);
354 INSERT INTO t1 VALUES('AAA', 'BBB');
355
356 CREATE TABLE t2(z);
357 INSERT INTO t2 VALUES('t2');
358
359 CREATE TABLE t3(x PRIMARY KEY, y);
360 INSERT INTO t3 VALUES('AAA', 'AAA');
361 }
362
363 do_execsql_test 11.1.1 {
364 SELECT * FROM t1 JOIN t2 ON unlikely(x=y) AND y='AAA'
365 }
366 do_execsql_test 11.1.2 {
367 SELECT * FROM t1 JOIN t2 ON likely(x=y) AND y='AAA'
368 }
369 do_execsql_test 11.1.3 {
370 SELECT * FROM t1 JOIN t2 ON x=y AND y='AAA'
371 }
372
373 do_execsql_test 11.2.1 {
374 SELECT * FROM t3 JOIN t2 ON unlikely(x=y) AND y='AAA'
375 } {AAA AAA t2}
376 do_execsql_test 11.2.2 {
377 SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
378 } {AAA AAA t2}
379 do_execsql_test 11.2.3 {
380 SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
381 } {AAA AAA t2}
382
383 # 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a
384 # Affinity problem when a likely() function is used as a column in
385 # an index.
386 #
387 reset_db
388 do_execsql_test 12.0 {
389 CREATE TABLE t1(a REAL);
390 INSERT INTO t1(a) VALUES(123);
391 CREATE INDEX t1x1 ON t1(likely(a));
392 SELECT typeof(likely(a)) FROM t1 NOT INDEXED;
393 SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1;
394 } {real real}
395 do_execsql_test 12.1 {
396 CREATE INDEX t1x2 ON t1(abs(a));
397 SELECT typeof(abs(a)) FROM t1 NOT INDEXED;
398 SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2;
399 } {real real}
400
401
402 finish_test