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