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