Commit | Line | Data |
32c34379 |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
5 | use List::Util qw(sum); |
32c34379 |
6 | |
7 | use Test::More; |
8 | |
7823ae89 |
9 | # equivalent to $Module::Install::AUTHOR |
10 | my $author = ( |
11 | ( not -d './inc' ) |
12 | or |
13 | ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') ) |
14 | ); |
15 | |
16 | if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) { |
17 | plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run'; |
18 | } |
19 | |
32c34379 |
20 | |
25823711 |
21 | my @sql_tests = ( |
22 | # WHERE condition - equal |
23 | { |
24 | equal => 1, |
25 | statements => [ |
26 | q/SELECT foo FROM bar WHERE a = 1/, |
27 | q/SELECT foo FROM bar WHERE a=1/, |
28 | q/SELECT foo FROM bar WHERE (a = 1)/, |
29 | q/SELECT foo FROM bar WHERE (a=1)/, |
30 | q/SELECT foo FROM bar WHERE ( a = 1 )/, |
31 | q/ |
32 | SELECT |
33 | foo |
34 | FROM |
35 | bar |
36 | WHERE |
37 | a = 1 |
38 | /, |
39 | q/ |
40 | SELECT |
41 | foo |
42 | FROM |
43 | bar |
44 | WHERE |
45 | (a = 1) |
46 | /, |
47 | q/ |
48 | SELECT |
49 | foo |
50 | FROM |
51 | bar |
52 | WHERE |
53 | ( a = 1 ) |
54 | /, |
55 | q/SELECT foo FROM bar WHERE ((a = 1))/, |
56 | q/SELECT foo FROM bar WHERE ( (a = 1) )/, |
57 | q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/, |
58 | ] |
59 | }, |
60 | { |
61 | equal => 1, |
62 | statements => [ |
63 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1/, |
64 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/, |
65 | q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/, |
66 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/, |
67 | q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/, |
68 | q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/, |
69 | q/ |
70 | SELECT |
71 | foo |
72 | FROM |
73 | bar |
74 | WHERE |
75 | a = 1 |
76 | AND |
77 | b = 1 |
78 | /, |
79 | q/ |
80 | SELECT |
81 | foo |
82 | FROM |
83 | bar |
84 | WHERE |
85 | (a = 1 |
86 | AND |
87 | b = 1) |
88 | /, |
89 | q/ |
90 | SELECT |
91 | foo |
92 | FROM |
93 | bar |
94 | WHERE |
95 | (a = 1) |
96 | AND |
97 | (b = 1) |
98 | /, |
99 | q/ |
100 | SELECT |
101 | foo |
102 | FROM |
103 | bar |
104 | WHERE |
105 | ((a = 1) |
106 | AND |
107 | (b = 1)) |
108 | /, |
109 | ] |
110 | }, |
56c0595d |
111 | { |
112 | equal => 1, |
56c0595d |
113 | statements => [ |
114 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, |
e40f5df9 |
115 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, |
56c0595d |
116 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/, |
117 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/, |
1b17d1b0 |
118 | q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/, |
56c0595d |
119 | ] |
120 | }, |
121 | { |
122 | equal => 1, |
56c0595d |
123 | statements => [ |
124 | q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, |
125 | q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, |
126 | q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/, |
1b17d1b0 |
127 | q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/, |
128 | ] |
129 | }, |
130 | { |
131 | equal => 1, |
132 | statements => [ |
133 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, |
134 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, |
135 | q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /, |
56c0595d |
136 | ] |
137 | }, |
e40f5df9 |
138 | { |
139 | equal => 0, |
140 | parenthesis_significant => 1, |
141 | statements => [ |
142 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, |
143 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, |
144 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/, |
145 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/, |
146 | q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/, |
147 | ] |
148 | }, |
149 | { |
150 | equal => 0, |
151 | parenthesis_significant => 1, |
152 | statements => [ |
153 | q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, |
154 | q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, |
155 | q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/, |
156 | q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/, |
157 | ] |
158 | }, |
159 | { |
160 | equal => 0, |
161 | parenthesis_significant => 1, |
162 | statements => [ |
163 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, |
164 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, |
165 | q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /, |
166 | ] |
167 | }, |
25823711 |
168 | |
169 | # WHERE condition - different |
170 | { |
171 | equal => 0, |
172 | statements => [ |
173 | q/SELECT foo FROM bar WHERE a = 1/, |
174 | q/SELECT quux FROM bar WHERE a = 1/, |
175 | q/SELECT foo FROM quux WHERE a = 1/, |
176 | q/FOOBAR foo FROM bar WHERE a = 1/, |
177 | |
178 | q/SELECT foo FROM bar WHERE a = 2/, |
179 | q/SELECT foo FROM bar WHERE a < 1/, |
180 | q/SELECT foo FROM bar WHERE b = 1/, |
181 | q/SELECT foo FROM bar WHERE (c = 1)/, |
182 | q/SELECT foo FROM bar WHERE (d = 1)/, |
183 | |
184 | q/SELECT foo FROM bar WHERE a = 1 AND quux/, |
185 | q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/, |
186 | q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/, |
187 | q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/, |
188 | q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/, |
189 | q/SELECT foo FROM bar JOIN quux WHERE a = 1/, |
190 | q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/, |
191 | ] |
192 | }, |
193 | { |
194 | equal => 0, |
195 | statements => [ |
196 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1/, |
197 | q/SELECT quux FROM bar WHERE a = 1 AND b = 1/, |
198 | q/SELECT foo FROM quux WHERE a = 1 AND b = 1/, |
199 | q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/, |
200 | |
201 | q/SELECT foo FROM bar WHERE a = 2 AND b = 1/, |
202 | q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/, |
203 | q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/, |
204 | q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/, |
205 | q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/, |
206 | q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/, |
207 | |
208 | q/SELECT foo FROM bar WHERE a = 1 AND b = 2/, |
209 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/, |
210 | q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/, |
211 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/, |
212 | q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/, |
213 | q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/, |
214 | |
215 | q/SELECT foo FROM bar WHERE a < 1 AND b = 1/, |
216 | q/SELECT foo FROM bar WHERE b = 1 AND b = 1/, |
217 | q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/, |
218 | q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/, |
219 | |
220 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/, |
221 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/, |
222 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/, |
223 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/, |
224 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/, |
225 | q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/, |
226 | q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/, |
227 | ] |
228 | }, |
56c0595d |
229 | { |
230 | equal => 0, |
231 | statements => [ |
232 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/, |
233 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/, |
234 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/, |
235 | ] |
236 | }, |
237 | { |
238 | equal => 0, |
239 | statements => [ |
240 | q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/, |
241 | q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/, |
242 | q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/, |
243 | ] |
244 | }, |
b6c8b01b |
245 | { |
246 | equal => 0, |
247 | statements => [ |
01b64cb7 |
248 | # BETWEEN with/without parenthesis around itself/RHS is a sticky business |
249 | # if I made a mistake here, simply rewrite the special BETWEEN handling in |
250 | # _recurse_parse() |
251 | # |
252 | # by RIBASUSHI |
b6c8b01b |
253 | q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/, |
01b64cb7 |
254 | q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/, |
255 | q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/, |
256 | q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/, |
b6c8b01b |
257 | ] |
258 | }, |
25823711 |
259 | |
260 | # JOIN condition - equal |
261 | { |
262 | equal => 1, |
263 | statements => [ |
264 | q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/, |
265 | q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/, |
266 | q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/, |
267 | q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/, |
268 | q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/, |
269 | q/ |
270 | SELECT |
271 | foo |
272 | FROM |
273 | bar |
274 | JOIN |
275 | baz |
276 | ON |
277 | a = 1 |
278 | WHERE |
279 | x = 1 |
280 | /, |
281 | q/ |
282 | SELECT |
283 | foo |
284 | FROM |
285 | bar |
286 | JOIN |
287 | baz |
288 | ON |
289 | (a = 1) |
290 | WHERE |
291 | x = 1 |
292 | /, |
293 | q/ |
294 | SELECT |
295 | foo |
296 | FROM |
297 | bar |
298 | JOIN |
299 | baz |
300 | ON |
301 | ( a = 1 ) |
302 | WHERE |
303 | x = 1 |
304 | /, |
305 | q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/, |
306 | q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/, |
307 | q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/, |
308 | ] |
309 | }, |
310 | { |
311 | equal => 1, |
312 | statements => [ |
313 | q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/, |
314 | q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/, |
315 | q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/, |
316 | q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/, |
317 | q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/, |
318 | q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/, |
319 | q/ |
320 | SELECT |
321 | foo |
322 | FROM |
323 | bar |
324 | JOIN |
325 | baz |
326 | ON |
327 | a = 1 |
328 | AND |
329 | b = 1 |
330 | WHERE |
331 | x = 1 |
332 | /, |
333 | q/ |
334 | SELECT |
335 | foo |
336 | FROM |
337 | bar |
338 | JOIN |
339 | baz |
340 | ON |
341 | (a = 1 |
342 | AND |
343 | b = 1) |
344 | WHERE |
345 | x = 1 |
346 | /, |
347 | q/ |
348 | SELECT |
349 | foo |
350 | FROM |
351 | bar |
352 | JOIN |
353 | baz |
354 | ON |
355 | (a = 1) |
356 | AND |
357 | (b = 1) |
358 | WHERE |
359 | x = 1 |
360 | /, |
361 | q/ |
362 | SELECT |
363 | foo |
364 | FROM |
365 | bar |
366 | JOIN |
367 | baz |
368 | ON |
369 | ((a = 1) |
370 | AND |
371 | (b = 1)) |
372 | WHERE |
373 | x = 1 |
374 | /, |
375 | ] |
376 | }, |
377 | |
378 | # JOIN condition - different |
379 | { |
380 | equal => 0, |
381 | statements => [ |
382 | q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/, |
383 | q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/, |
384 | q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/, |
385 | q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/, |
386 | |
387 | q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/, |
388 | q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/, |
389 | q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/, |
390 | q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/, |
391 | q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/, |
392 | |
393 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/, |
394 | q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/, |
395 | q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/, |
396 | q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/, |
397 | q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/, |
398 | q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/, |
399 | q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/, |
400 | ] |
401 | }, |
402 | { |
403 | equal => 0, |
404 | statements => [ |
405 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
406 | q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
407 | q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
408 | q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
409 | |
410 | q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/, |
411 | q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/, |
412 | q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/, |
413 | q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/, |
414 | q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/, |
415 | q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/, |
416 | |
417 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/, |
418 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/, |
419 | q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/, |
420 | q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/, |
421 | q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/, |
422 | q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/, |
423 | |
424 | q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/, |
425 | q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/, |
426 | q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/, |
427 | q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/, |
428 | |
429 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/, |
430 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/, |
431 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/, |
432 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/, |
433 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/, |
434 | q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/, |
435 | q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/, |
436 | ] |
437 | }, |
438 | |
439 | # DISTINCT ON (...) not confused with JOIN ON (...) |
440 | { |
441 | equal => 1, |
442 | statements => [ |
443 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/, |
444 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/, |
445 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/, |
446 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/, |
447 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/, |
448 | q/ |
449 | SELECT DISTINCT ON (foo, quux) |
450 | foo, |
451 | quux |
452 | FROM |
453 | bar |
454 | WHERE |
455 | a = 1 |
456 | /, |
457 | q/ |
458 | SELECT DISTINCT ON (foo, quux) |
459 | foo, |
460 | quux |
461 | FROM |
462 | bar |
463 | WHERE |
464 | (a = 1) |
465 | /, |
466 | q/ |
467 | SELECT DISTINCT ON (foo, quux) |
468 | foo, |
469 | quux |
470 | FROM |
471 | bar |
472 | WHERE |
473 | ( a = 1 ) |
474 | /, |
475 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/, |
476 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/, |
477 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/, |
478 | ] |
479 | }, |
480 | |
481 | # subselects - equal |
482 | { |
483 | equal => 1, |
484 | statements => [ |
485 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, |
486 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/, |
487 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/, |
488 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/, |
489 | ] |
490 | }, |
491 | { |
492 | equal => 1, |
493 | statements => [ |
494 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/, |
495 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/, |
496 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/, |
497 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/, |
498 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/, |
499 | |
500 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/, |
501 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/, |
502 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/, |
503 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/, |
504 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/, |
505 | ] |
506 | }, |
507 | |
508 | # subselects - different |
509 | { |
510 | equal => 0, |
511 | statements => [ |
512 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, |
513 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/, |
514 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/, |
515 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/, |
516 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/, |
517 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/, |
518 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/, |
519 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/, |
520 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/, |
521 | ] |
522 | }, |
523 | { |
524 | equal => 0, |
525 | statements => [ |
526 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/, |
527 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/, |
528 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/, |
529 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/, |
530 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/, |
531 | |
532 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/, |
533 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/, |
534 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/, |
535 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/, |
536 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/, |
537 | |
538 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/, |
539 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/, |
540 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/, |
541 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/, |
542 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/, |
543 | |
544 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/, |
545 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/, |
546 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/, |
547 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/, |
548 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/, |
549 | ] |
550 | }, |
551 | ); |
552 | |
32c34379 |
553 | my @bind_tests = ( |
554 | # scalar - equal |
555 | { |
556 | equal => 1, |
557 | bindvals => [ |
558 | undef, |
559 | undef, |
560 | ] |
561 | }, |
562 | { |
563 | equal => 1, |
564 | bindvals => [ |
565 | 'foo', |
566 | 'foo', |
567 | ] |
568 | }, |
569 | { |
570 | equal => 1, |
571 | bindvals => [ |
572 | 42, |
573 | 42, |
574 | '42', |
575 | ] |
576 | }, |
577 | |
578 | # scalarref - equal |
579 | { |
580 | equal => 1, |
581 | bindvals => [ |
582 | \'foo', |
583 | \'foo', |
584 | ] |
585 | }, |
586 | { |
587 | equal => 1, |
588 | bindvals => [ |
589 | \42, |
590 | \42, |
591 | \'42', |
592 | ] |
593 | }, |
594 | |
595 | # arrayref - equal |
596 | { |
597 | equal => 1, |
598 | bindvals => [ |
599 | [], |
600 | [] |
601 | ] |
602 | }, |
603 | { |
604 | equal => 1, |
605 | bindvals => [ |
606 | [42], |
607 | [42], |
608 | ['42'], |
609 | ] |
610 | }, |
611 | { |
612 | equal => 1, |
613 | bindvals => [ |
614 | [1, 42], |
615 | [1, 42], |
616 | ['1', 42], |
617 | [1, '42'], |
618 | ['1', '42'], |
619 | ] |
620 | }, |
621 | |
622 | # hashref - equal |
623 | { |
624 | equal => 1, |
625 | bindvals => [ |
626 | { foo => 42 }, |
627 | { foo => 42 }, |
628 | { foo => '42' }, |
629 | ] |
630 | }, |
631 | { |
632 | equal => 1, |
633 | bindvals => [ |
634 | { foo => 42, bar => 1 }, |
635 | { foo => 42, bar => 1 }, |
636 | { foo => '42', bar => 1 }, |
637 | ] |
638 | }, |
639 | |
640 | # blessed object - equal |
641 | { |
642 | equal => 1, |
643 | bindvals => [ |
644 | bless(\(local $_ = 42), 'Life::Universe::Everything'), |
645 | bless(\(local $_ = 42), 'Life::Universe::Everything'), |
646 | ] |
647 | }, |
648 | { |
649 | equal => 1, |
650 | bindvals => [ |
651 | bless([42], 'Life::Universe::Everything'), |
652 | bless([42], 'Life::Universe::Everything'), |
653 | ] |
654 | }, |
655 | { |
656 | equal => 1, |
657 | bindvals => [ |
658 | bless({ answer => 42 }, 'Life::Universe::Everything'), |
659 | bless({ answer => 42 }, 'Life::Universe::Everything'), |
660 | ] |
661 | }, |
662 | |
663 | # complex data structure - equal |
664 | { |
665 | equal => 1, |
666 | bindvals => [ |
667 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
668 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
669 | ] |
670 | }, |
671 | |
672 | |
673 | # scalar - different |
674 | { |
675 | equal => 0, |
676 | bindvals => [ |
677 | undef, |
678 | 'foo', |
679 | 42, |
680 | ] |
681 | }, |
682 | |
683 | # scalarref - different |
684 | { |
685 | equal => 0, |
686 | bindvals => [ |
687 | \undef, |
688 | \'foo', |
689 | \42, |
690 | ] |
691 | }, |
692 | |
693 | # arrayref - different |
694 | { |
695 | equal => 0, |
696 | bindvals => [ |
697 | [undef], |
698 | ['foo'], |
699 | [42], |
700 | ] |
701 | }, |
702 | |
703 | # hashref - different |
704 | { |
705 | equal => 0, |
706 | bindvals => [ |
707 | { foo => undef }, |
708 | { foo => 'bar' }, |
709 | { foo => 42 }, |
710 | ] |
711 | }, |
712 | |
713 | # different types |
714 | { |
715 | equal => 0, |
716 | bindvals => [ |
717 | 'foo', |
718 | \'foo', |
719 | ['foo'], |
720 | { foo => 'bar' }, |
721 | ] |
722 | }, |
723 | |
724 | # complex data structure - different |
725 | { |
726 | equal => 0, |
727 | bindvals => [ |
728 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
729 | [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
730 | [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
731 | [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
732 | [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
733 | [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
734 | [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ], |
735 | [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ], |
736 | [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ], |
737 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ], |
738 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ], |
739 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ], |
740 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ], |
741 | ] |
742 | }, |
743 | ); |
744 | |
25823711 |
745 | plan tests => 1 + |
746 | sum( |
747 | map { $_ * ($_ - 1) / 2 } |
748 | map { scalar @{$_->{statements}} } |
749 | @sql_tests |
750 | ) + |
751 | sum( |
752 | map { $_ * ($_ - 1) / 2 } |
753 | map { scalar @{$_->{bindvals}} } |
754 | @bind_tests |
e7827ba2 |
755 | ) + |
756 | 3; |
32c34379 |
757 | |
e7827ba2 |
758 | use_ok('SQL::Abstract::Test', import => [qw( |
759 | eq_sql_bind eq_sql eq_bind is_same_sql_bind |
760 | )]); |
32c34379 |
761 | |
25823711 |
762 | for my $test (@sql_tests) { |
763 | my $statements = $test->{statements}; |
764 | while (@$statements) { |
765 | my $sql1 = shift @$statements; |
766 | foreach my $sql2 (@$statements) { |
e40f5df9 |
767 | |
768 | no warnings qw/once/; # perl 5.10 is dumb |
769 | local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant} |
770 | if $test->{parenthesis_significant}; |
25823711 |
771 | my $equal = eq_sql($sql1, $sql2); |
e40f5df9 |
772 | |
56c0595d |
773 | TODO: { |
774 | local $TODO = $test->{todo} if $test->{todo}; |
775 | |
776 | if ($test->{equal}) { |
777 | ok($equal, "equal SQL expressions should have been considered equal"); |
778 | } else { |
779 | ok(!$equal, "different SQL expressions should have been considered not equal"); |
780 | } |
781 | |
782 | if ($equal ^ $test->{equal}) { |
783 | diag("sql1: $sql1"); |
784 | diag("sql2: $sql2"); |
785 | } |
25823711 |
786 | } |
787 | } |
788 | } |
789 | } |
790 | |
32c34379 |
791 | for my $test (@bind_tests) { |
792 | my $bindvals = $test->{bindvals}; |
793 | while (@$bindvals) { |
794 | my $bind1 = shift @$bindvals; |
795 | foreach my $bind2 (@$bindvals) { |
796 | my $equal = eq_bind($bind1, $bind2); |
797 | if ($test->{equal}) { |
798 | ok($equal, "equal bind values considered equal"); |
799 | } else { |
800 | ok(!$equal, "different bind values considered not equal"); |
801 | } |
802 | |
803 | if ($equal ^ $test->{equal}) { |
804 | diag("bind1: " . Dumper($bind1)); |
805 | diag("bind2: " . Dumper($bind2)); |
806 | } |
807 | } |
808 | } |
809 | } |
e7827ba2 |
810 | |
811 | ok(eq_sql_bind( |
812 | "SELECT * FROM foo WHERE id = ?", [42], |
813 | "SELECT * FROM foo WHERE (id = ?)", [42], |
814 | ), |
815 | "eq_sql_bind considers equal SQL expressions and bind values equal" |
816 | ); |
817 | |
818 | |
819 | ok(!eq_sql_bind( |
820 | "SELECT * FROM foo WHERE id = ?", [42], |
821 | "SELECT * FROM foo WHERE (id = ?)", [0], |
822 | ), |
823 | "eq_sql_bind considers equal SQL expressions and different bind values different" |
824 | ); |
825 | |
826 | ok(!eq_sql_bind( |
827 | "SELECT * FROM foo WHERE id = ?", [42], |
828 | "SELECT * FROM bar WHERE (id = ?)", [42], |
829 | ), |
830 | "eq_sql_bind considers different SQL expressions and equal bind values different" |
831 | ); |