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