wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2008 December 30 |
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 the multi-index OR clause optimizer. |
||
13 | # |
||
14 | # $Id: where9.test,v 1.9 2009/06/05 17:09:12 drh Exp $ |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | ifcapable !or_opt { |
||
20 | finish_test |
||
21 | return |
||
22 | } |
||
23 | |||
24 | # Evaluate SQL. Return the result set followed by the |
||
25 | # and the number of full-scan steps. |
||
26 | # |
||
27 | proc count_steps {sql} { |
||
28 | set r [db eval $sql] |
||
29 | lappend r scan [db status step] sort [db status sort] |
||
30 | } |
||
31 | |||
32 | |||
33 | # Construct test data. |
||
34 | # |
||
35 | do_test where9-1.1 { |
||
36 | db eval { |
||
37 | CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g); |
||
38 | INSERT INTO t1 VALUES(1,11,1001,1.001,100.1,'bcdefghij','yxwvuts'); |
||
39 | INSERT INTO t1 VALUES(2,22,1001,2.002,100.1,'cdefghijk','yxwvuts'); |
||
40 | INSERT INTO t1 VALUES(3,33,1001,3.003,100.1,'defghijkl','xwvutsr'); |
||
41 | INSERT INTO t1 VALUES(4,44,2002,4.004,200.2,'efghijklm','xwvutsr'); |
||
42 | INSERT INTO t1 VALUES(5,55,2002,5.005,200.2,'fghijklmn','xwvutsr'); |
||
43 | INSERT INTO t1 VALUES(6,66,2002,6.006,200.2,'ghijklmno','xwvutsr'); |
||
44 | INSERT INTO t1 VALUES(7,77,3003,7.007,300.3,'hijklmnop','xwvutsr'); |
||
45 | INSERT INTO t1 VALUES(8,88,3003,8.008,300.3,'ijklmnopq','wvutsrq'); |
||
46 | INSERT INTO t1 VALUES(9,99,3003,9.009,300.3,'jklmnopqr','wvutsrq'); |
||
47 | INSERT INTO t1 VALUES(10,110,4004,10.01,400.4,'klmnopqrs','wvutsrq'); |
||
48 | INSERT INTO t1 VALUES(11,121,4004,11.011,400.4,'lmnopqrst','wvutsrq'); |
||
49 | INSERT INTO t1 VALUES(12,132,4004,12.012,400.4,'mnopqrstu','wvutsrq'); |
||
50 | INSERT INTO t1 VALUES(13,143,5005,13.013,500.5,'nopqrstuv','vutsrqp'); |
||
51 | INSERT INTO t1 VALUES(14,154,5005,14.014,500.5,'opqrstuvw','vutsrqp'); |
||
52 | INSERT INTO t1 VALUES(15,165,5005,15.015,500.5,'pqrstuvwx','vutsrqp'); |
||
53 | INSERT INTO t1 VALUES(16,176,6006,16.016,600.6,'qrstuvwxy','vutsrqp'); |
||
54 | INSERT INTO t1 VALUES(17,187,6006,17.017,600.6,'rstuvwxyz','vutsrqp'); |
||
55 | INSERT INTO t1 VALUES(18,198,6006,18.018,600.6,'stuvwxyza','utsrqpo'); |
||
56 | INSERT INTO t1 VALUES(19,209,7007,19.019,700.7,'tuvwxyzab','utsrqpo'); |
||
57 | INSERT INTO t1 VALUES(20,220,7007,20.02,700.7,'uvwxyzabc','utsrqpo'); |
||
58 | INSERT INTO t1 VALUES(21,231,7007,21.021,700.7,'vwxyzabcd','utsrqpo'); |
||
59 | INSERT INTO t1 VALUES(22,242,8008,22.022,800.8,'wxyzabcde','utsrqpo'); |
||
60 | INSERT INTO t1 VALUES(23,253,8008,23.023,800.8,'xyzabcdef','tsrqpon'); |
||
61 | INSERT INTO t1 VALUES(24,264,8008,24.024,800.8,'yzabcdefg','tsrqpon'); |
||
62 | INSERT INTO t1 VALUES(25,275,9009,25.025,900.9,'zabcdefgh','tsrqpon'); |
||
63 | INSERT INTO t1 VALUES(26,286,9009,26.026,900.9,'abcdefghi','tsrqpon'); |
||
64 | INSERT INTO t1 VALUES(27,297,9009,27.027,900.9,'bcdefghij','tsrqpon'); |
||
65 | INSERT INTO t1 VALUES(28,308,10010,28.028,1001.0,'cdefghijk','srqponm'); |
||
66 | INSERT INTO t1 VALUES(29,319,10010,29.029,1001.0,'defghijkl','srqponm'); |
||
67 | INSERT INTO t1 VALUES(30,330,10010,30.03,1001.0,'efghijklm','srqponm'); |
||
68 | INSERT INTO t1 VALUES(31,341,11011,31.031,1101.1,'fghijklmn','srqponm'); |
||
69 | INSERT INTO t1 VALUES(32,352,11011,32.032,1101.1,'ghijklmno','srqponm'); |
||
70 | INSERT INTO t1 VALUES(33,363,11011,33.033,1101.1,'hijklmnop','rqponml'); |
||
71 | INSERT INTO t1 VALUES(34,374,12012,34.034,1201.2,'ijklmnopq','rqponml'); |
||
72 | INSERT INTO t1 VALUES(35,385,12012,35.035,1201.2,'jklmnopqr','rqponml'); |
||
73 | INSERT INTO t1 VALUES(36,396,12012,36.036,1201.2,'klmnopqrs','rqponml'); |
||
74 | INSERT INTO t1 VALUES(37,407,13013,37.037,1301.3,'lmnopqrst','rqponml'); |
||
75 | INSERT INTO t1 VALUES(38,418,13013,38.038,1301.3,'mnopqrstu','qponmlk'); |
||
76 | INSERT INTO t1 VALUES(39,429,13013,39.039,1301.3,'nopqrstuv','qponmlk'); |
||
77 | INSERT INTO t1 VALUES(40,440,14014,40.04,1401.4,'opqrstuvw','qponmlk'); |
||
78 | INSERT INTO t1 VALUES(41,451,14014,41.041,1401.4,'pqrstuvwx','qponmlk'); |
||
79 | INSERT INTO t1 VALUES(42,462,14014,42.042,1401.4,'qrstuvwxy','qponmlk'); |
||
80 | INSERT INTO t1 VALUES(43,473,15015,43.043,1501.5,'rstuvwxyz','ponmlkj'); |
||
81 | INSERT INTO t1 VALUES(44,484,15015,44.044,1501.5,'stuvwxyza','ponmlkj'); |
||
82 | INSERT INTO t1 VALUES(45,495,15015,45.045,1501.5,'tuvwxyzab','ponmlkj'); |
||
83 | INSERT INTO t1 VALUES(46,506,16016,46.046,1601.6,'uvwxyzabc','ponmlkj'); |
||
84 | INSERT INTO t1 VALUES(47,517,16016,47.047,1601.6,'vwxyzabcd','ponmlkj'); |
||
85 | INSERT INTO t1 VALUES(48,528,16016,48.048,1601.6,'wxyzabcde','onmlkji'); |
||
86 | INSERT INTO t1 VALUES(49,539,17017,49.049,1701.7,'xyzabcdef','onmlkji'); |
||
87 | INSERT INTO t1 VALUES(50,550,17017,50.05,1701.7,'yzabcdefg','onmlkji'); |
||
88 | INSERT INTO t1 VALUES(51,561,17017,51.051,1701.7,'zabcdefgh','onmlkji'); |
||
89 | INSERT INTO t1 VALUES(52,572,18018,52.052,1801.8,'abcdefghi','onmlkji'); |
||
90 | INSERT INTO t1 VALUES(53,583,18018,53.053,1801.8,'bcdefghij','nmlkjih'); |
||
91 | INSERT INTO t1 VALUES(54,594,18018,54.054,1801.8,'cdefghijk','nmlkjih'); |
||
92 | INSERT INTO t1 VALUES(55,605,19019,55.055,1901.9,'defghijkl','nmlkjih'); |
||
93 | INSERT INTO t1 VALUES(56,616,19019,56.056,1901.9,'efghijklm','nmlkjih'); |
||
94 | INSERT INTO t1 VALUES(57,627,19019,57.057,1901.9,'fghijklmn','nmlkjih'); |
||
95 | INSERT INTO t1 VALUES(58,638,20020,58.058,2002.0,'ghijklmno','mlkjihg'); |
||
96 | INSERT INTO t1 VALUES(59,649,20020,59.059,2002.0,'hijklmnop','mlkjihg'); |
||
97 | INSERT INTO t1 VALUES(60,660,20020,60.06,2002.0,'ijklmnopq','mlkjihg'); |
||
98 | INSERT INTO t1 VALUES(61,671,21021,61.061,2102.1,'jklmnopqr','mlkjihg'); |
||
99 | INSERT INTO t1 VALUES(62,682,21021,62.062,2102.1,'klmnopqrs','mlkjihg'); |
||
100 | INSERT INTO t1 VALUES(63,693,21021,63.063,2102.1,'lmnopqrst','lkjihgf'); |
||
101 | INSERT INTO t1 VALUES(64,704,22022,64.064,2202.2,'mnopqrstu','lkjihgf'); |
||
102 | INSERT INTO t1 VALUES(65,715,22022,65.065,2202.2,'nopqrstuv','lkjihgf'); |
||
103 | INSERT INTO t1 VALUES(66,726,22022,66.066,2202.2,'opqrstuvw','lkjihgf'); |
||
104 | INSERT INTO t1 VALUES(67,737,23023,67.067,2302.3,'pqrstuvwx','lkjihgf'); |
||
105 | INSERT INTO t1 VALUES(68,748,23023,68.068,2302.3,'qrstuvwxy','kjihgfe'); |
||
106 | INSERT INTO t1 VALUES(69,759,23023,69.069,2302.3,'rstuvwxyz','kjihgfe'); |
||
107 | INSERT INTO t1 VALUES(70,770,24024,70.07,2402.4,'stuvwxyza','kjihgfe'); |
||
108 | INSERT INTO t1 VALUES(71,781,24024,71.071,2402.4,'tuvwxyzab','kjihgfe'); |
||
109 | INSERT INTO t1 VALUES(72,792,24024,72.072,2402.4,'uvwxyzabc','kjihgfe'); |
||
110 | INSERT INTO t1 VALUES(73,803,25025,73.073,2502.5,'vwxyzabcd','jihgfed'); |
||
111 | INSERT INTO t1 VALUES(74,814,25025,74.074,2502.5,'wxyzabcde','jihgfed'); |
||
112 | INSERT INTO t1 VALUES(75,825,25025,75.075,2502.5,'xyzabcdef','jihgfed'); |
||
113 | INSERT INTO t1 VALUES(76,836,26026,76.076,2602.6,'yzabcdefg','jihgfed'); |
||
114 | INSERT INTO t1 VALUES(77,847,26026,77.077,2602.6,'zabcdefgh','jihgfed'); |
||
115 | INSERT INTO t1 VALUES(78,858,26026,78.078,2602.6,'abcdefghi','ihgfedc'); |
||
116 | INSERT INTO t1 VALUES(79,869,27027,79.079,2702.7,'bcdefghij','ihgfedc'); |
||
117 | INSERT INTO t1 VALUES(80,880,27027,80.08,2702.7,'cdefghijk','ihgfedc'); |
||
118 | INSERT INTO t1 VALUES(81,891,27027,81.081,2702.7,'defghijkl','ihgfedc'); |
||
119 | INSERT INTO t1 VALUES(82,902,28028,82.082,2802.8,'efghijklm','ihgfedc'); |
||
120 | INSERT INTO t1 VALUES(83,913,28028,83.083,2802.8,'fghijklmn','hgfedcb'); |
||
121 | INSERT INTO t1 VALUES(84,924,28028,84.084,2802.8,'ghijklmno','hgfedcb'); |
||
122 | INSERT INTO t1 VALUES(85,935,29029,85.085,2902.9,'hijklmnop','hgfedcb'); |
||
123 | INSERT INTO t1 VALUES(86,946,29029,86.086,2902.9,'ijklmnopq','hgfedcb'); |
||
124 | INSERT INTO t1 VALUES(87,957,29029,87.087,2902.9,'jklmnopqr','hgfedcb'); |
||
125 | INSERT INTO t1 VALUES(88,968,30030,88.088,3003.0,'klmnopqrs','gfedcba'); |
||
126 | INSERT INTO t1 VALUES(89,979,30030,89.089,3003.0,'lmnopqrst','gfedcba'); |
||
127 | INSERT INTO t1 VALUES(90,NULL,30030,90.09,3003.0,'mnopqrstu','gfedcba'); |
||
128 | INSERT INTO t1 VALUES(91,1001,NULL,91.091,3103.1,'nopqrstuv','gfedcba'); |
||
129 | INSERT INTO t1 VALUES(92,1012,31031,NULL,3103.1,'opqrstuvw','gfedcba'); |
||
130 | INSERT INTO t1 VALUES(93,1023,31031,93.093,NULL,'pqrstuvwx','fedcbaz'); |
||
131 | INSERT INTO t1 VALUES(94,1034,32032,94.094,3203.2,NULL,'fedcbaz'); |
||
132 | INSERT INTO t1 VALUES(95,1045,32032,95.095,3203.2,'rstuvwxyz',NULL); |
||
133 | INSERT INTO t1 VALUES(96,NULL,NULL,96.096,3203.2,'stuvwxyza','fedcbaz'); |
||
134 | INSERT INTO t1 VALUES(97,1067,33033,NULL,NULL,'tuvwxyzab','fedcbaz'); |
||
135 | INSERT INTO t1 VALUES(98,1078,33033,98.098,3303.3,NULL,NULL); |
||
136 | INSERT INTO t1 VALUES(99,NULL,NULL,NULL,NULL,NULL,NULL); |
||
137 | CREATE INDEX t1b ON t1(b); |
||
138 | CREATE INDEX t1c ON t1(c); |
||
139 | CREATE INDEX t1d ON t1(d); |
||
140 | CREATE INDEX t1e ON t1(e); |
||
141 | CREATE INDEX t1f ON t1(f); |
||
142 | CREATE INDEX t1g ON t1(g); |
||
143 | CREATE TABLE t2(a INTEGER PRIMARY KEY,b,c,d,e,f,g); |
||
144 | INSERT INTO t2 SELECT * FROM t1; |
||
145 | CREATE INDEX t2b ON t2(b,c); |
||
146 | CREATE INDEX t2c ON t2(c,e); |
||
147 | CREATE INDEX t2d ON t2(d,g); |
||
148 | CREATE INDEX t2e ON t2(e,f,g); |
||
149 | CREATE INDEX t2f ON t2(f,b,d,c); |
||
150 | CREATE INDEX t2g ON t2(g,f); |
||
151 | CREATE TABLE t3(x,y); |
||
152 | INSERT INTO t3 VALUES(1,80); |
||
153 | INSERT INTO t3 VALUES(2,80); |
||
154 | CREATE TABLE t4(a INTEGER PRIMARY KEY,b,c,d,e,f,g); |
||
155 | INSERT INTO t4 SELECT * FROM t1; |
||
156 | CREATE INDEX t4b ON t4(b); |
||
157 | CREATE INDEX t4c ON t4(c); |
||
158 | } |
||
159 | } {} |
||
160 | |||
161 | do_test where9-1.2.1 { |
||
162 | count_steps { |
||
163 | SELECT a FROM t1 |
||
164 | WHERE b IS NULL |
||
165 | OR c IS NULL |
||
166 | OR d IS NULL |
||
167 | ORDER BY a |
||
168 | } |
||
169 | } {90 91 92 96 97 99 scan 0 sort 1} |
||
170 | do_test where9-1.2.2 { |
||
171 | count_steps { |
||
172 | SELECT a FROM t1 |
||
173 | WHERE +b IS NULL |
||
174 | OR c IS NULL |
||
175 | OR d IS NULL |
||
176 | ORDER BY a |
||
177 | } |
||
178 | } {90 91 92 96 97 99 scan 98 sort 0} |
||
179 | do_test where9-1.2.3 { |
||
180 | count_steps { |
||
181 | SELECT a FROM t1 |
||
182 | WHERE b IS NULL |
||
183 | OR +c IS NULL |
||
184 | OR d IS NULL |
||
185 | ORDER BY a |
||
186 | } |
||
187 | } {90 91 92 96 97 99 scan 98 sort 0} |
||
188 | do_test where9-1.2.4 { |
||
189 | count_steps { |
||
190 | SELECT a FROM t1 |
||
191 | WHERE b IS NULL |
||
192 | OR c IS NULL |
||
193 | OR +d IS NULL |
||
194 | ORDER BY a |
||
195 | } |
||
196 | } {90 91 92 96 97 99 scan 98 sort 0} |
||
197 | do_test where9-1.2.5 { |
||
198 | count_steps { |
||
199 | SELECT a FROM t4 |
||
200 | WHERE b IS NULL |
||
201 | OR c IS NULL |
||
202 | OR d IS NULL |
||
203 | ORDER BY a |
||
204 | } |
||
205 | } {90 91 92 96 97 99 scan 98 sort 0} |
||
206 | |||
207 | do_test where9-1.3.1 { |
||
208 | count_steps { |
||
209 | SELECT a FROM t1 |
||
210 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
211 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
212 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
213 | ORDER BY a |
||
214 | } |
||
215 | } {90 91 92 97 scan 0 sort 1} |
||
216 | do_test where9-1.3.2 { |
||
217 | count_steps { |
||
218 | SELECT a FROM t4 |
||
219 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
220 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
221 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
222 | ORDER BY a |
||
223 | } |
||
224 | } {90 91 92 97 scan 98 sort 0} |
||
225 | do_test where9-1.3.3 { |
||
226 | count_steps { |
||
227 | SELECT a FROM t4 |
||
228 | WHERE (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
229 | OR (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
230 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
231 | ORDER BY a |
||
232 | } |
||
233 | } {90 91 92 97 scan 98 sort 0} |
||
234 | do_test where9-1.3.4 { |
||
235 | count_steps { |
||
236 | SELECT a FROM t4 |
||
237 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
238 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
239 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
240 | ORDER BY a |
||
241 | } |
||
242 | } {90 91 92 97 scan 98 sort 0} |
||
243 | |||
244 | do_test where9-1.4 { |
||
245 | count_steps { |
||
246 | SELECT a FROM t1 |
||
247 | WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL) |
||
248 | ORDER BY a |
||
249 | } |
||
250 | } {87 88 89 90 91 scan 0 sort 1} |
||
251 | do_test where9-1.5 { |
||
252 | # When this test was originally written, SQLite used a rowset object |
||
253 | # to optimize the "ORDER BY a" clause. Now that it is using a rowhash, |
||
254 | # this is not possible. So we have to comment out one term of the OR |
||
255 | # expression in order to prevent SQLite from deeming a full-table |
||
256 | # scan to be a better strategy than using multiple indexes, which would |
||
257 | # defeat the point of the test. |
||
258 | count_steps { |
||
259 | SELECT a FROM t1 |
||
260 | WHERE a=83 |
||
261 | OR b=913 |
||
262 | OR c=28028 |
||
263 | OR (d>=82 AND d<83) |
||
264 | /* OR (e>2802 AND e<2803) */ |
||
265 | OR f='fghijklmn' |
||
266 | OR g='hgfedcb' |
||
267 | ORDER BY a |
||
268 | } |
||
269 | } {5 31 57 82 83 84 85 86 87 scan 0 sort 1} |
||
270 | do_test where9-1.6 { |
||
271 | count_steps { |
||
272 | SELECT a FROM t1 |
||
273 | WHERE b=1012 |
||
274 | OR (d IS NULL AND e IS NOT NULL) |
||
275 | } |
||
276 | } {92 scan 0 sort 0} |
||
277 | do_test where9-1.7 { |
||
278 | count_steps { |
||
279 | SELECT a FROM t1 |
||
280 | WHERE (b=1012 OR (d IS NULL AND e IS NOT NULL)) |
||
281 | AND f!=g |
||
282 | } |
||
283 | } {92 scan 0 sort 0} |
||
284 | do_test where9-1.8 { |
||
285 | count_steps { |
||
286 | SELECT a FROM t1 |
||
287 | WHERE (b=1012 OR (d IS NULL AND e IS NOT NULL)) |
||
288 | AND f==g |
||
289 | } |
||
290 | } {scan 0 sort 0} |
||
291 | |||
292 | do_test where9-2.1 { |
||
293 | count_steps { |
||
294 | SELECT t2.a FROM t1, t2 |
||
295 | WHERE t1.a=80 |
||
296 | AND (t1.c=t2.c OR t1.d=t2.d) |
||
297 | ORDER BY 1 |
||
298 | } |
||
299 | } {79 80 81 scan 0 sort 1} |
||
300 | do_test where9-2.2 { |
||
301 | count_steps { |
||
302 | SELECT t2.a FROM t1, t2 |
||
303 | WHERE t1.a=80 |
||
304 | AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) |
||
305 | ORDER BY 1 |
||
306 | } |
||
307 | } {2 28 54 80 scan 0 sort 1} |
||
308 | do_test where9-2.3 { |
||
309 | count_steps { |
||
310 | SELECT coalesce(t2.a,9999) |
||
311 | FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f |
||
312 | WHERE t1.a=80 |
||
313 | ORDER BY 1 |
||
314 | } |
||
315 | } {2 28 54 80 scan 0 sort 1} |
||
316 | do_test where9-2.4 { |
||
317 | count_steps { |
||
318 | SELECT coalesce(t2.a,9999) |
||
319 | FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f |
||
320 | WHERE t1.a=80 |
||
321 | ORDER BY 1 |
||
322 | } |
||
323 | } {9999 scan 0 sort 1} |
||
324 | do_test where9-2.5 { |
||
325 | count_steps { |
||
326 | SELECT t1.a, coalesce(t2.a,9999) |
||
327 | FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f |
||
328 | WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80) |
||
329 | ORDER BY 1 |
||
330 | } |
||
331 | } {80 80 80 2 80 28 80 54 scan 0 sort 1} |
||
332 | do_test where9-2.6 { |
||
333 | count_steps { |
||
334 | SELECT t1.a, coalesce(t2.a,9999) |
||
335 | FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f |
||
336 | WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80) |
||
337 | ORDER BY 1 |
||
338 | } |
||
339 | } {80 9999 scan 0 sort 1} |
||
340 | do_test where9-2.7 { |
||
341 | count_steps { |
||
342 | SELECT t3.x, t1.a, coalesce(t2.a,9999) |
||
343 | FROM t3 JOIN |
||
344 | t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f |
||
345 | WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80) |
||
346 | ORDER BY 1, 2 |
||
347 | } |
||
348 | } {1 80 9999 2 80 9999 scan 1 sort 1} |
||
349 | do_test where9-2.8 { |
||
350 | count_steps { |
||
351 | SELECT t3.x, t1.a, coalesce(t2.a,9999) |
||
352 | FROM t3 JOIN |
||
353 | t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f |
||
354 | WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80) |
||
355 | ORDER BY 1, 2, 3 |
||
356 | } |
||
357 | } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1} |
||
358 | |||
359 | |||
360 | ifcapable explain { |
||
361 | do_execsql_test where9-3.1 { |
||
362 | EXPLAIN QUERY PLAN |
||
363 | SELECT t2.a FROM t1, t2 |
||
364 | WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) |
||
365 | } { |
||
366 | |||
367 | |||
368 | |||
369 | } |
||
370 | do_execsql_test where9-3.2 { |
||
371 | EXPLAIN QUERY PLAN |
||
372 | SELECT coalesce(t2.a,9999) |
||
373 | FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f |
||
374 | WHERE t1.a=80 |
||
375 | } { |
||
376 | |||
377 | |||
378 | |||
379 | } |
||
380 | } |
||
381 | |||
382 | # Make sure that INDEXED BY and multi-index OR clauses play well with |
||
383 | # one another. |
||
384 | # |
||
385 | do_test where9-4.1 { |
||
386 | count_steps { |
||
387 | SELECT a FROM t1 |
||
388 | WHERE b>1000 |
||
389 | AND (c=31031 OR d IS NULL) |
||
390 | ORDER BY +a |
||
391 | } |
||
392 | } {92 93 97 scan 0 sort 1} |
||
393 | do_test where9-4.2 { |
||
394 | count_steps { |
||
395 | SELECT a FROM t1 |
||
396 | WHERE b>1000 |
||
397 | AND (c=31031 OR +d IS NULL) |
||
398 | ORDER BY +a |
||
399 | } |
||
400 | } {92 93 97 scan 0 sort 1} |
||
401 | do_test where9-4.3 { |
||
402 | count_steps { |
||
403 | SELECT a FROM t1 |
||
404 | WHERE +b>1000 |
||
405 | AND (c=31031 OR d IS NULL) |
||
406 | ORDER BY +a |
||
407 | } |
||
408 | } {92 93 97 scan 0 sort 1} |
||
409 | do_test where9-4.4 { |
||
410 | count_steps { |
||
411 | SELECT a FROM t1 INDEXED BY t1b |
||
412 | WHERE b>1000 |
||
413 | AND (c=31031 OR d IS NULL) |
||
414 | ORDER BY +a |
||
415 | } |
||
416 | } {92 93 97 scan 0 sort 1} |
||
417 | do_test where9-4.5 { |
||
418 | catchsql { |
||
419 | SELECT a FROM t1 INDEXED BY t1b |
||
420 | WHERE +b>1000 |
||
421 | AND (c=31031 OR d IS NULL) |
||
422 | ORDER BY +a |
||
423 | } |
||
424 | } {1 {cannot use index: t1b}} |
||
425 | do_test where9-4.6 { |
||
426 | count_steps { |
||
427 | SELECT a FROM t1 NOT INDEXED |
||
428 | WHERE b>1000 |
||
429 | AND (c=31031 OR d IS NULL) |
||
430 | ORDER BY +a |
||
431 | } |
||
432 | } {92 93 97 scan 98 sort 1} |
||
433 | do_test where9-4.7 { |
||
434 | catchsql { |
||
435 | SELECT a FROM t1 INDEXED BY t1c |
||
436 | WHERE b>1000 |
||
437 | AND (c=31031 OR d IS NULL) |
||
438 | ORDER BY +a |
||
439 | } |
||
440 | } {1 {cannot use index: t1c}} |
||
441 | do_test where9-4.8 { |
||
442 | catchsql { |
||
443 | SELECT a FROM t1 INDEXED BY t1d |
||
444 | WHERE b>1000 |
||
445 | AND (c=31031 OR d IS NULL) |
||
446 | ORDER BY +a |
||
447 | } |
||
448 | } {1 {cannot use index: t1d}} |
||
449 | |||
450 | ifcapable explain { |
||
451 | # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because |
||
452 | # the former is an equality test which is expected to return fewer rows. |
||
453 | # |
||
454 | do_execsql_test where9-5.1 { |
||
455 | EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) |
||
456 | } { |
||
457 | |||
458 | |||
459 | } |
||
460 | |||
461 | # In contrast, b=1000 is preferred over any OR-clause. |
||
462 | # |
||
463 | do_execsql_test where9-5.2 { |
||
464 | EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) |
||
465 | } { |
||
466 | |||
467 | } |
||
468 | |||
469 | # Likewise, inequalities in an AND are preferred over inequalities in |
||
470 | # an OR. |
||
471 | # |
||
472 | do_execsql_test where9-5.3 { |
||
473 | EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) |
||
474 | } { |
||
475 | |||
476 | } |
||
477 | } |
||
478 | |||
479 | ############################################################################ |
||
480 | # Make sure OR-clauses work correctly on UPDATE and DELETE statements. |
||
481 | |||
482 | do_test where9-6.2.1 { |
||
483 | db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85} |
||
484 | } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99} |
||
485 | |||
486 | do_test where9-6.2.2 { ;# Deletes entries 90 91 92 96 97 99 |
||
487 | count_steps { |
||
488 | BEGIN; |
||
489 | DELETE FROM t1 |
||
490 | WHERE b IS NULL |
||
491 | OR c IS NULL |
||
492 | OR d IS NULL |
||
493 | } |
||
494 | } {scan 0 sort 0} |
||
495 | |||
496 | do_test where9-6.2.3 { |
||
497 | db eval { |
||
498 | SELECT count(*) FROM t1 UNION ALL |
||
499 | SELECT a FROM t1 WHERE a>=85; |
||
500 | ROLLBACK; |
||
501 | } |
||
502 | } {93 85 86 87 88 89 93 94 95 98} |
||
503 | |||
504 | do_test where9-6.2.4 { ;# Deletes entries 90 91 92 96 97 99 |
||
505 | count_steps { |
||
506 | BEGIN; |
||
507 | DELETE FROM t1 |
||
508 | WHERE +b IS NULL |
||
509 | OR c IS NULL |
||
510 | OR d IS NULL |
||
511 | } |
||
512 | } {scan 98 sort 0} |
||
513 | |||
514 | do_test where9-6.2.5 { |
||
515 | db eval { |
||
516 | SELECT count(*) FROM t1 UNION ALL |
||
517 | SELECT a FROM t1 WHERE a>=85; |
||
518 | ROLLBACK; |
||
519 | } |
||
520 | } {93 85 86 87 88 89 93 94 95 98} |
||
521 | |||
522 | do_test where9-6.2.6 { |
||
523 | count_steps { |
||
524 | BEGIN; |
||
525 | UPDATE t1 SET a=a+100 |
||
526 | WHERE (b IS NULL |
||
527 | OR c IS NULL |
||
528 | OR d IS NULL) |
||
529 | AND a!=92 |
||
530 | AND a!=97 |
||
531 | } |
||
532 | } {scan 0 sort 0} ;# Add 100 to entries 90 91 96 99 |
||
533 | |||
534 | do_test where9-6.2.7 { |
||
535 | db eval { |
||
536 | SELECT count(*) FROM t1 UNION ALL |
||
537 | SELECT a FROM t1 WHERE a>=85; |
||
538 | ROLLBACK |
||
539 | } |
||
540 | } {99 85 86 87 88 89 92 93 94 95 97 98 190 191 196 199} |
||
541 | |||
542 | do_test where9-6.2.8 { ;# Deletes entries 90 91 92 97 99 |
||
543 | count_steps { |
||
544 | BEGIN; |
||
545 | DELETE FROM t1 |
||
546 | WHERE (b IS NULL |
||
547 | OR c IS NULL |
||
548 | OR d IS NULL) |
||
549 | AND a!=96 |
||
550 | } |
||
551 | } {scan 0 sort 0} |
||
552 | |||
553 | do_test where9-6.2.9 { |
||
554 | db eval { |
||
555 | SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85; |
||
556 | ROLLBACK; |
||
557 | } |
||
558 | } {94 85 86 87 88 89 93 94 95 96 98} |
||
559 | |||
560 | do_test where9-6.3.1 { |
||
561 | count_steps { |
||
562 | BEGIN; |
||
563 | DELETE FROM t1 |
||
564 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
565 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
566 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
567 | } |
||
568 | } {scan 0 sort 0} ;# DELETEs rows 90 91 92 97 |
||
569 | do_test where9-6.3.2 { |
||
570 | db eval { |
||
571 | SELECT count(*) FROM t1 UNION ALL |
||
572 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
573 | ROLLBACK; |
||
574 | } |
||
575 | } {95 85 86 87 88 89 93 94 95 96 98 99} |
||
576 | |||
577 | do_test where9-6.3.3 { |
||
578 | count_steps { |
||
579 | BEGIN; |
||
580 | UPDATE t1 SET a=a+100 |
||
581 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
582 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
583 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
584 | } |
||
585 | } {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97 |
||
586 | do_test where9-6.3.4 { |
||
587 | db eval { |
||
588 | SELECT count(*) FROM t1 UNION ALL |
||
589 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 200; |
||
590 | ROLLBACK; |
||
591 | } |
||
592 | } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} |
||
593 | |||
594 | do_test where9-6.3.5 { |
||
595 | count_steps { |
||
596 | BEGIN; |
||
597 | DELETE FROM t1 |
||
598 | WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) |
||
599 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
600 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
601 | } |
||
602 | } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97 |
||
603 | do_test where9-6.3.6 { |
||
604 | db eval { |
||
605 | SELECT count(*) FROM t1 UNION ALL |
||
606 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
607 | ROLLBACK; |
||
608 | } |
||
609 | } {95 85 86 87 88 89 93 94 95 96 98 99} |
||
610 | |||
611 | do_test where9-6.3.7 { |
||
612 | count_steps { |
||
613 | BEGIN; |
||
614 | UPDATE t1 SET a=a+100 |
||
615 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
616 | OR (b NOT NULL AND +c IS NULL AND d NOT NULL) |
||
617 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
618 | } |
||
619 | } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97 |
||
620 | do_test where9-6.3.8 { |
||
621 | db eval { |
||
622 | SELECT count(*) FROM t1 UNION ALL |
||
623 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
624 | ROLLBACK; |
||
625 | } |
||
626 | } {99 85 86 87 88 89 93 94 95 96 98 99} |
||
627 | |||
628 | |||
629 | do_test where9-6.4.1 { |
||
630 | count_steps { |
||
631 | BEGIN; |
||
632 | DELETE FROM t1 |
||
633 | WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL) |
||
634 | } |
||
635 | } {scan 0 sort 0} ;# DELETE rows 87 88 89 90 91 |
||
636 | do_test where9-6.4.2 { |
||
637 | db eval { |
||
638 | SELECT count(*) FROM t1 UNION ALL |
||
639 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
640 | ROLLBACK; |
||
641 | } |
||
642 | } {94 85 86 92 93 94 95 96 97 98 99} |
||
643 | do_test where9-6.4.3 { |
||
644 | count_steps { |
||
645 | BEGIN; |
||
646 | UPDATE t1 SET a=a+100 |
||
647 | WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL) |
||
648 | } |
||
649 | } {scan 0 sort 0} ;# Add 100 to rowids 87 88 89 90 91 |
||
650 | do_test where9-6.4.4 { |
||
651 | db eval { |
||
652 | SELECT count(*) FROM t1 UNION ALL |
||
653 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
654 | ROLLBACK; |
||
655 | } |
||
656 | } {99 85 86 92 93 94 95 96 97 98 99} |
||
657 | |||
658 | |||
659 | do_test where9-6.5.1 { |
||
660 | count_steps { |
||
661 | BEGIN; |
||
662 | DELETE FROM t1 |
||
663 | WHERE a=83 |
||
664 | OR b=913 |
||
665 | OR c=28028 |
||
666 | OR (d>=82 AND d<83) |
||
667 | OR (e>2802 AND e<2803) |
||
668 | OR f='fghijklmn' |
||
669 | OR g='hgfedcb' |
||
670 | } |
||
671 | } {scan 0 sort 0} ;# DELETE rows 5 31 57 82 83 84 85 86 87 |
||
672 | do_test where9-6.5.2 { |
||
673 | db eval { |
||
674 | SELECT count(*) FROM t1 UNION ALL |
||
675 | SELECT a FROM t1 WHERE a IN (5,31,57,82,83,84,85,86,87); |
||
676 | ROLLBACK; |
||
677 | } |
||
678 | } {90} |
||
679 | |||
680 | do_test where9-6.5.3 { |
||
681 | count_steps { |
||
682 | BEGIN; |
||
683 | UPDATE t1 SET a=a+100 |
||
684 | WHERE a=83 |
||
685 | OR b=913 |
||
686 | OR c=28028 |
||
687 | OR (d>=82 AND d<83) |
||
688 | OR (e>2802 AND e<2803) |
||
689 | OR f='fghijklmn' |
||
690 | OR g='hgfedcb' |
||
691 | } |
||
692 | } {scan 0 sort 0} ;# Add 100 to rowids 5 31 57 82 83 84 85 86 87 |
||
693 | do_test where9-6.5.4 { |
||
694 | db eval { |
||
695 | SELECT count(*) FROM t1 UNION ALL |
||
696 | SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87); |
||
697 | ROLLBACK; |
||
698 | } |
||
699 | } {99 105 131 157 182 183 184 185 186 187} |
||
700 | |||
701 | do_test where9-6.6.1 { |
||
702 | count_steps { |
||
703 | BEGIN; |
||
704 | DELETE FROM t1 |
||
705 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
706 | OR (b NOT NULL AND +c IS NULL AND d NOT NULL) |
||
707 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
708 | } |
||
709 | } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97 |
||
710 | do_test where9-6.6.2 { |
||
711 | db eval { |
||
712 | SELECT count(*) FROM t1 UNION ALL |
||
713 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
714 | ROLLBACK; |
||
715 | } |
||
716 | } {95 85 86 87 88 89 93 94 95 96 98 99} |
||
717 | |||
718 | do_test where9-6.6.3 { |
||
719 | count_steps { |
||
720 | BEGIN; |
||
721 | UPDATE t1 SET a=a+100 |
||
722 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
723 | OR (b NOT NULL AND +c IS NULL AND d NOT NULL) |
||
724 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
725 | } |
||
726 | } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97 |
||
727 | do_test where9-6.6.4 { |
||
728 | db eval { |
||
729 | SELECT count(*) FROM t1 UNION ALL |
||
730 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 200; |
||
731 | ROLLBACK; |
||
732 | } |
||
733 | } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} |
||
734 | |||
735 | do_test where9-6.7.1 { |
||
736 | count_steps { |
||
737 | BEGIN; |
||
738 | DELETE FROM t1 NOT INDEXED |
||
739 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
740 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
741 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
742 | } |
||
743 | } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97 |
||
744 | do_test where9-6.7.2 { |
||
745 | db eval { |
||
746 | SELECT count(*) FROM t1 UNION ALL |
||
747 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; |
||
748 | ROLLBACK; |
||
749 | } |
||
750 | } {95 85 86 87 88 89 93 94 95 96 98 99} |
||
751 | |||
752 | do_test where9-6.7.3 { |
||
753 | count_steps { |
||
754 | BEGIN; |
||
755 | UPDATE t1 NOT INDEXED SET a=a+100 |
||
756 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
757 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
758 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
759 | } |
||
760 | } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97 |
||
761 | do_test where9-6.7.4 { |
||
762 | db eval { |
||
763 | SELECT count(*) FROM t1 UNION ALL |
||
764 | SELECT a FROM t1 WHERE a BETWEEN 85 AND 200; |
||
765 | ROLLBACK; |
||
766 | } |
||
767 | } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} |
||
768 | |||
769 | do_test where9-6.8.1 { |
||
770 | catchsql { |
||
771 | DELETE FROM t1 INDEXED BY t1b |
||
772 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
773 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
774 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
775 | } |
||
776 | } {1 {cannot use index: t1b}} |
||
777 | do_test where9-6.8.2 { |
||
778 | catchsql { |
||
779 | UPDATE t1 INDEXED BY t1b SET a=a+100 |
||
780 | WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) |
||
781 | OR (b NOT NULL AND c IS NULL AND d NOT NULL) |
||
782 | OR (b NOT NULL AND c NOT NULL AND d IS NULL) |
||
783 | } |
||
784 | } {1 {cannot use index: t1b}} |
||
785 | |||
786 | finish_test |