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