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