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