Commit | Line | Data |
32c34379 |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
32c34379 |
5 | |
6 | use Test::More; |
7 | |
2fadf08e |
8 | use SQL::Abstract::Test import => [qw( |
9 | eq_sql_bind eq_sql eq_bind is_same_sql_bind dumper $sql_differ |
10 | )]; |
54629227 |
11 | |
25823711 |
12 | my @sql_tests = ( |
0769ac0e |
13 | # WHERE condition - equal |
25823711 |
14 | { |
15 | equal => 1, |
16 | statements => [ |
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/SELECT foo FROM bar WHERE (a=1)/, |
21 | q/SELECT foo FROM bar WHERE ( a = 1 )/, |
22 | q/ |
23 | SELECT |
24 | foo |
25 | FROM |
26 | bar |
27 | WHERE |
28 | a = 1 |
29 | /, |
30 | q/ |
31 | SELECT |
32 | foo |
33 | FROM |
34 | bar |
35 | WHERE |
36 | (a = 1) |
37 | /, |
38 | q/ |
39 | SELECT |
40 | foo |
41 | FROM |
42 | bar |
43 | WHERE |
44 | ( a = 1 ) |
45 | /, |
46 | q/SELECT foo FROM bar WHERE ((a = 1))/, |
47 | q/SELECT foo FROM bar WHERE ( (a = 1) )/, |
48 | q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/, |
49 | ] |
50 | }, |
51 | { |
52 | equal => 1, |
53 | statements => [ |
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/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/, |
59 | q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/, |
60 | q/ |
61 | SELECT |
62 | foo |
63 | FROM |
64 | bar |
65 | WHERE |
66 | a = 1 |
67 | AND |
68 | b = 1 |
69 | /, |
70 | q/ |
71 | SELECT |
72 | foo |
73 | FROM |
74 | bar |
75 | WHERE |
76 | (a = 1 |
77 | AND |
78 | b = 1) |
79 | /, |
80 | q/ |
81 | SELECT |
82 | foo |
83 | FROM |
84 | bar |
85 | WHERE |
86 | (a = 1) |
87 | AND |
88 | (b = 1) |
89 | /, |
90 | q/ |
91 | SELECT |
92 | foo |
93 | FROM |
94 | bar |
95 | WHERE |
96 | ((a = 1) |
97 | AND |
98 | (b = 1)) |
99 | /, |
100 | ] |
101 | }, |
56c0595d |
102 | { |
103 | equal => 1, |
56c0595d |
104 | statements => [ |
105 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, |
e40f5df9 |
106 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, |
56c0595d |
107 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/, |
108 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/, |
1b17d1b0 |
109 | q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/, |
56c0595d |
110 | ] |
111 | }, |
112 | { |
113 | equal => 1, |
56c0595d |
114 | statements => [ |
115 | q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, |
116 | q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, |
117 | q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/, |
1b17d1b0 |
118 | q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/, |
119 | ] |
120 | }, |
121 | { |
122 | equal => 1, |
123 | statements => [ |
124 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, |
125 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, |
126 | q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /, |
56c0595d |
127 | ] |
128 | }, |
e40f5df9 |
129 | { |
9e8dab3f |
130 | equal => 1, |
131 | statements => [ |
132 | q/SELECT foo FROM bar WHERE (a) AND (b = 2)/, |
133 | q/SELECT foo FROM bar WHERE (a AND b = 2)/, |
134 | q/SELECT foo FROM bar WHERE (a AND (b = 2))/, |
135 | q/SELECT foo FROM bar WHERE a AND (b = 2)/, |
136 | ] |
137 | }, |
138 | { |
277b5d3f |
139 | equal => 1, |
140 | statements => [ |
141 | q/SELECT foo FROM bar WHERE ((NOT a) AND b = 2)/, |
142 | q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/, |
143 | q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/, |
144 | ], |
145 | }, |
146 | { |
9e8dab3f |
147 | equal => 0, |
148 | statements => [ |
149 | q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/, |
150 | q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/, |
151 | ] |
152 | }, |
153 | { |
e40f5df9 |
154 | equal => 0, |
3b9d807e |
155 | opts => { parenthesis_significant => 1 }, |
e40f5df9 |
156 | statements => [ |
157 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, |
158 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, |
159 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/, |
160 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/, |
161 | q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/, |
162 | ] |
163 | }, |
164 | { |
165 | equal => 0, |
3b9d807e |
166 | opts => { parenthesis_significant => 1 }, |
e40f5df9 |
167 | statements => [ |
168 | q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, |
169 | q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, |
170 | q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/, |
171 | q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/, |
172 | ] |
173 | }, |
174 | { |
175 | equal => 0, |
3b9d807e |
176 | opts => { parenthesis_significant => 1 }, |
e40f5df9 |
177 | statements => [ |
178 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, |
179 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, |
180 | q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /, |
181 | ] |
182 | }, |
25823711 |
183 | |
184 | # WHERE condition - different |
185 | { |
186 | equal => 0, |
187 | statements => [ |
188 | q/SELECT foo FROM bar WHERE a = 1/, |
189 | q/SELECT quux FROM bar WHERE a = 1/, |
190 | q/SELECT foo FROM quux WHERE a = 1/, |
191 | q/FOOBAR foo FROM bar WHERE a = 1/, |
192 | |
193 | q/SELECT foo FROM bar WHERE a = 2/, |
194 | q/SELECT foo FROM bar WHERE a < 1/, |
195 | q/SELECT foo FROM bar WHERE b = 1/, |
196 | q/SELECT foo FROM bar WHERE (c = 1)/, |
197 | q/SELECT foo FROM bar WHERE (d = 1)/, |
198 | |
199 | q/SELECT foo FROM bar WHERE a = 1 AND quux/, |
200 | q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/, |
201 | q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/, |
202 | q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/, |
203 | q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/, |
204 | q/SELECT foo FROM bar JOIN quux WHERE a = 1/, |
205 | q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/, |
206 | ] |
207 | }, |
208 | { |
209 | equal => 0, |
210 | statements => [ |
211 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1/, |
212 | q/SELECT quux FROM bar WHERE a = 1 AND b = 1/, |
213 | q/SELECT foo FROM quux WHERE a = 1 AND b = 1/, |
214 | q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/, |
215 | |
216 | q/SELECT foo FROM bar WHERE a = 2 AND b = 1/, |
217 | q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/, |
218 | q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/, |
219 | q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/, |
220 | q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/, |
221 | q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/, |
222 | |
223 | q/SELECT foo FROM bar WHERE a = 1 AND b = 2/, |
224 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/, |
225 | q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/, |
226 | q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/, |
227 | q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/, |
228 | q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/, |
229 | |
230 | q/SELECT foo FROM bar WHERE a < 1 AND b = 1/, |
231 | q/SELECT foo FROM bar WHERE b = 1 AND b = 1/, |
232 | q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/, |
233 | q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/, |
234 | |
235 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/, |
236 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/, |
237 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/, |
238 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/, |
239 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/, |
240 | q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/, |
241 | q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/, |
242 | ] |
243 | }, |
56c0595d |
244 | { |
245 | equal => 0, |
246 | statements => [ |
247 | q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/, |
248 | q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/, |
249 | q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/, |
250 | ] |
251 | }, |
252 | { |
253 | equal => 0, |
254 | statements => [ |
255 | q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/, |
256 | q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/, |
257 | q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/, |
258 | ] |
259 | }, |
b6c8b01b |
260 | { |
261 | equal => 0, |
3b9d807e |
262 | opts => { parenthesis_significant => 1 }, |
14190bdf |
263 | statements => [ |
264 | q/SELECT foo FROM bar WHERE a IN (1,2,3)/, |
265 | q/SELECT foo FROM bar WHERE a IN (1,3,2)/, |
266 | q/SELECT foo FROM bar WHERE a IN ((1,2,3))/, |
267 | ] |
268 | }, |
269 | { |
270 | equal => 0, |
b6c8b01b |
271 | statements => [ |
01b64cb7 |
272 | # BETWEEN with/without parenthesis around itself/RHS is a sticky business |
273 | # if I made a mistake here, simply rewrite the special BETWEEN handling in |
274 | # _recurse_parse() |
275 | # |
276 | # by RIBASUSHI |
b6c8b01b |
277 | q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/, |
01b64cb7 |
278 | q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/, |
279 | q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/, |
280 | q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/, |
b6c8b01b |
281 | ] |
282 | }, |
25823711 |
283 | |
b3b79607 |
284 | # IS NULL (special LHS-only op) |
285 | { |
286 | equal => 1, |
287 | statements => [ |
288 | q/WHERE a IS NOT NULL AND b IS NULL/, |
289 | q/WHERE (a IS NOT NULL) AND b IS NULL/, |
290 | q/WHERE a IS NOT NULL AND (b IS NULL)/, |
291 | q/WHERE (a IS NOT NULL) AND ((b IS NULL))/, |
292 | ], |
293 | }, |
294 | |
25823711 |
295 | # JOIN condition - equal |
296 | { |
297 | equal => 1, |
298 | statements => [ |
299 | q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/, |
300 | q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/, |
301 | q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/, |
302 | q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/, |
303 | q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/, |
304 | q/ |
305 | SELECT |
306 | foo |
307 | FROM |
308 | bar |
309 | JOIN |
310 | baz |
311 | ON |
312 | a = 1 |
313 | WHERE |
314 | x = 1 |
315 | /, |
316 | q/ |
317 | SELECT |
318 | foo |
319 | FROM |
320 | bar |
321 | JOIN |
322 | baz |
323 | ON |
324 | (a = 1) |
325 | WHERE |
326 | x = 1 |
327 | /, |
328 | q/ |
329 | SELECT |
330 | foo |
331 | FROM |
332 | bar |
333 | JOIN |
334 | baz |
335 | ON |
336 | ( a = 1 ) |
337 | WHERE |
338 | x = 1 |
339 | /, |
340 | q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/, |
341 | q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/, |
342 | q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/, |
343 | ] |
344 | }, |
345 | { |
346 | equal => 1, |
347 | statements => [ |
348 | q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/, |
349 | q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/, |
350 | q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/, |
351 | q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/, |
352 | q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/, |
353 | q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/, |
354 | q/ |
355 | SELECT |
356 | foo |
357 | FROM |
358 | bar |
359 | JOIN |
360 | baz |
361 | ON |
362 | a = 1 |
363 | AND |
364 | b = 1 |
365 | WHERE |
366 | x = 1 |
367 | /, |
368 | q/ |
369 | SELECT |
370 | foo |
371 | FROM |
372 | bar |
373 | JOIN |
374 | baz |
375 | ON |
376 | (a = 1 |
377 | AND |
378 | b = 1) |
379 | WHERE |
380 | x = 1 |
381 | /, |
382 | q/ |
383 | SELECT |
384 | foo |
385 | FROM |
386 | bar |
387 | JOIN |
388 | baz |
389 | ON |
390 | (a = 1) |
391 | AND |
392 | (b = 1) |
393 | WHERE |
394 | x = 1 |
395 | /, |
396 | q/ |
397 | SELECT |
398 | foo |
399 | FROM |
400 | bar |
401 | JOIN |
402 | baz |
403 | ON |
404 | ((a = 1) |
405 | AND |
406 | (b = 1)) |
407 | WHERE |
408 | x = 1 |
409 | /, |
410 | ] |
411 | }, |
412 | |
413 | # JOIN condition - different |
414 | { |
415 | equal => 0, |
416 | statements => [ |
417 | q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/, |
418 | q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/, |
419 | q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/, |
420 | q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/, |
421 | |
422 | q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/, |
423 | q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/, |
424 | q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/, |
425 | q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/, |
426 | q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/, |
427 | |
428 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/, |
429 | q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/, |
430 | q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/, |
431 | q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/, |
432 | q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/, |
433 | q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/, |
434 | q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/, |
435 | ] |
436 | }, |
437 | { |
438 | equal => 0, |
439 | statements => [ |
440 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
441 | q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
442 | q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
443 | q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, |
444 | |
445 | q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/, |
446 | q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/, |
447 | q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/, |
448 | q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/, |
449 | q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/, |
450 | q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/, |
451 | |
452 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/, |
453 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/, |
454 | q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/, |
455 | q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/, |
456 | q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/, |
457 | q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/, |
458 | |
459 | q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/, |
460 | q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/, |
461 | q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/, |
462 | q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/, |
463 | |
464 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/, |
465 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/, |
466 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/, |
467 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/, |
468 | q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/, |
469 | q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/, |
470 | q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/, |
471 | ] |
472 | }, |
473 | |
474 | # DISTINCT ON (...) not confused with JOIN ON (...) |
475 | { |
476 | equal => 1, |
477 | statements => [ |
478 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/, |
479 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/, |
480 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/, |
481 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/, |
482 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/, |
483 | q/ |
484 | SELECT DISTINCT ON (foo, quux) |
485 | foo, |
486 | quux |
487 | FROM |
488 | bar |
489 | WHERE |
490 | a = 1 |
491 | /, |
492 | q/ |
493 | SELECT DISTINCT ON (foo, quux) |
494 | foo, |
495 | quux |
496 | FROM |
497 | bar |
498 | WHERE |
499 | (a = 1) |
500 | /, |
501 | q/ |
502 | SELECT DISTINCT ON (foo, quux) |
503 | foo, |
504 | quux |
505 | FROM |
506 | bar |
507 | WHERE |
508 | ( a = 1 ) |
509 | /, |
510 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/, |
511 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/, |
512 | q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/, |
513 | ] |
514 | }, |
515 | |
516 | # subselects - equal |
517 | { |
518 | equal => 1, |
519 | statements => [ |
520 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, |
521 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/, |
522 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/, |
523 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/, |
524 | ] |
525 | }, |
526 | { |
527 | equal => 1, |
528 | statements => [ |
529 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/, |
530 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/, |
531 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/, |
532 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/, |
533 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/, |
534 | |
535 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/, |
536 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/, |
537 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/, |
538 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/, |
539 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/, |
540 | ] |
541 | }, |
542 | |
543 | # subselects - different |
544 | { |
545 | equal => 0, |
546 | statements => [ |
54629227 |
547 | q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/, |
548 | q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/, |
549 | ], |
550 | }, |
551 | { |
552 | equal => 0, |
553 | statements => [ |
25823711 |
554 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, |
555 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/, |
556 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/, |
557 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/, |
558 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/, |
559 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/, |
560 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/, |
561 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/, |
562 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/, |
563 | ] |
564 | }, |
565 | { |
566 | equal => 0, |
567 | statements => [ |
568 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/, |
569 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/, |
570 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/, |
571 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/, |
572 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/, |
573 | |
574 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/, |
575 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/, |
576 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/, |
577 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/, |
578 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/, |
579 | |
580 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/, |
581 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/, |
582 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/, |
583 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/, |
584 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/, |
585 | |
586 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/, |
587 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/, |
588 | q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/, |
589 | q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/, |
590 | q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/, |
591 | ] |
592 | }, |
0769ac0e |
593 | |
0c2de280 |
594 | # order by |
595 | { |
596 | equal => 1, |
597 | statements => [ |
598 | q/SELECT * FROM foo ORDER BY bar/, |
599 | q/SELECT * FROM foo ORDER BY bar ASC/, |
600 | q/SELECT * FROM foo ORDER BY bar asc/, |
601 | ], |
602 | }, |
603 | { |
604 | equal => 1, |
605 | statements => [ |
606 | q/SELECT * FROM foo ORDER BY bar, baz ASC/, |
607 | q/SELECT * FROM foo ORDER BY bar ASC, baz/, |
608 | q/SELECT * FROM foo ORDER BY bar asc, baz ASC/, |
609 | q/SELECT * FROM foo ORDER BY bar, baz/, |
610 | ], |
611 | }, |
612 | { |
c84a4321 |
613 | equal => 1, |
614 | statements => [ |
615 | q/ORDER BY colA, colB LIKE ? DESC, colC LIKE ?/, |
616 | q/ORDER BY colA ASC, colB LIKE ? DESC, colC LIKE ? ASC/, |
617 | ], |
618 | }, |
619 | { |
b4085a1a |
620 | equal => 1, |
621 | statements => [ |
622 | q/ORDER BY name + ?, [me].[id]/, |
623 | q/ORDER BY name + ? ASC, [me].[id]/, |
624 | ], |
625 | }, |
626 | { |
0c2de280 |
627 | equal => 0, |
628 | opts => { order_by_asc_significant => 1 }, |
629 | statements => [ |
630 | q/SELECT * FROM foo ORDER BY bar/, |
631 | q/SELECT * FROM foo ORDER BY bar ASC/, |
632 | q/SELECT * FROM foo ORDER BY bar desc/, |
633 | ], |
634 | }, |
635 | |
0769ac0e |
636 | # list permutations |
9a5b64c9 |
637 | { |
f8135ff3 |
638 | equal => 0, |
639 | statements => [ |
640 | 'SELECT a,b,c FROM foo', |
641 | 'SELECT a,c,b FROM foo', |
642 | 'SELECT b,a,c FROM foo', |
643 | 'SELECT b,c,a FROM foo', |
644 | 'SELECT c,a,b FROM foo', |
645 | 'SELECT c,b,a FROM foo', |
646 | ], |
9a5b64c9 |
647 | }, |
648 | { |
f8135ff3 |
649 | equal => 0, |
650 | statements => [ |
651 | 'SELECT * FROM foo WHERE a IN (1,2,3)', |
652 | 'SELECT * FROM foo WHERE a IN (1,3,2)', |
653 | 'SELECT * FROM foo WHERE a IN (2,1,3)', |
654 | 'SELECT * FROM foo WHERE a IN (2,3,1)', |
655 | 'SELECT * FROM foo WHERE a IN (3,1,2)', |
656 | 'SELECT * FROM foo WHERE a IN (3,2,1)', |
657 | ] |
76f10e1c |
658 | }, |
7cc47319 |
659 | |
660 | # list consistency |
661 | { |
662 | equal => 0, |
663 | statements => [ |
664 | 'SELECT a,b FROM foo', |
665 | 'SELECT a,,b FROM foo', |
666 | 'SELECT a,b, FROM foo', |
667 | 'SELECT ,a,b, FROM foo', |
668 | 'SELECT ,a,,b, FROM foo', |
669 | ], |
670 | }, |
671 | |
672 | # misc func |
76f10e1c |
673 | { |
f8135ff3 |
674 | equal => 0, |
675 | statements => [ |
676 | 'SELECT count(*) FROM foo', |
677 | 'SELECT count(*) AS bar FROM foo', |
678 | 'SELECT count(*) AS "bar" FROM foo', |
679 | 'SELECT count(a) FROM foo', |
680 | 'SELECT count(1) FROM foo', |
681 | ] |
9a5b64c9 |
682 | }, |
0769ac0e |
683 | { |
684 | equal => 1, |
685 | statements => [ |
686 | 'SELECT foo() bar FROM baz', |
687 | 'SELECT foo ( )bar FROM baz', |
688 | 'SELECT foo (())bar FROM baz', |
689 | 'SELECT foo(( ) ) bar FROM baz', |
690 | ] |
691 | }, |
692 | { |
693 | equal => 0, |
694 | statements => [ |
695 | 'SELECT foo() FROM bar', |
696 | 'SELECT foo FROM bar', |
697 | 'SELECT foo FROM bar ()', |
698 | ] |
699 | }, |
b3b79607 |
700 | { |
6e9a377b |
701 | equal => 0, |
b3b79607 |
702 | statements => [ |
6e9a377b |
703 | 'SELECT COUNT * FROM foo', |
704 | 'SELECT COUNT( * ) FROM foo', |
b3b79607 |
705 | ] |
706 | }, |
6e9a377b |
707 | # single ? of unknown funcs do not unroll unless |
708 | # explicitly allowed (e.g. Like) |
b3b79607 |
709 | { |
6e9a377b |
710 | equal => 0, |
b3b79607 |
711 | statements => [ |
6e9a377b |
712 | 'SELECT foo FROM bar WHERE bar > foo ?', |
713 | 'SELECT foo FROM bar WHERE bar > foo( ? )', |
b3b79607 |
714 | ] |
715 | }, |
716 | { |
717 | equal => 1, |
718 | statements => [ |
6e9a377b |
719 | 'SELECT foo FROM bar WHERE bar LIKE ?', |
720 | 'SELECT foo FROM bar WHERE bar LiKe (?)', |
721 | 'SELECT foo FROM bar WHERE bar lIkE( (?))', |
b3b79607 |
722 | ] |
723 | }, |
6e9a377b |
724 | # test multival |
69cc1bd8 |
725 | { |
726 | equal => 0, |
727 | statements => [ |
728 | 'SELECT foo FROM bar WHERE foo IN (?, ?)', |
729 | 'SELECT foo FROM bar WHERE foo IN ?, ?', |
730 | ] |
731 | }, |
0769ac0e |
732 | # math |
733 | { |
734 | equal => 0, |
735 | statements => [ |
736 | 'SELECT * FROM foo WHERE 1 = ( a > b)', |
737 | 'SELECT * FROM foo WHERE 1 = a > b', |
738 | 'SELECT * FROM foo WHERE (1 = a) > b', |
739 | ] |
740 | }, |
741 | { |
742 | equal => 1, |
743 | statements => [ |
744 | 'SELECT * FROM foo WHERE bar = baz(buzz)', |
745 | 'SELECT * FROM foo WHERE bar = (baz( buzz ))', |
746 | ] |
747 | }, |
6f2a5b66 |
748 | # oddballs |
749 | { |
750 | equal => 1, |
751 | statements => [ |
752 | 'WHERE ( foo GLOB ? )', |
753 | 'WHERE foo GLOB ?', |
754 | ], |
1de1d085 |
755 | }, |
756 | { |
757 | equal => 1, |
758 | statements => [ |
759 | 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner] |
760 | FROM [books] [me] |
761 | WHERE ( ( (EXISTS ( |
762 | SELECT FIRST ? SKIP ? [owner].[id] |
763 | FROM [owners] [owner] |
764 | WHERE ( [books].[owner] = [owner].[id] ) |
765 | )) AND [source] = ? ) )', |
766 | 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner] |
767 | FROM [books] [me] |
768 | WHERE ( ( EXISTS ( |
769 | SELECT FIRST ? SKIP ? [owner].[id] |
770 | FROM [owners] [owner] |
771 | WHERE ( [books].[owner] = [owner].[id] ) |
772 | ) AND [source] = ? ) )', |
773 | ], |
774 | }, |
81b3e585 |
775 | { |
776 | equal => 1, |
777 | statements => [ |
778 | 'WHERE foo = ? FETCH FIRST 1 ROWS ONLY', |
779 | 'WHERE ( foo = ? ) FETCH FIRST 1 ROWS ONLY', |
780 | 'WHERE (( foo = ? )) FETCH FIRST 1 ROWS ONLY', |
781 | ], |
782 | }, |
25823711 |
783 | ); |
784 | |
32c34379 |
785 | my @bind_tests = ( |
786 | # scalar - equal |
787 | { |
788 | equal => 1, |
789 | bindvals => [ |
790 | undef, |
791 | undef, |
792 | ] |
793 | }, |
794 | { |
795 | equal => 1, |
796 | bindvals => [ |
797 | 'foo', |
798 | 'foo', |
799 | ] |
800 | }, |
801 | { |
802 | equal => 1, |
803 | bindvals => [ |
804 | 42, |
805 | 42, |
806 | '42', |
807 | ] |
808 | }, |
809 | |
810 | # scalarref - equal |
811 | { |
812 | equal => 1, |
813 | bindvals => [ |
814 | \'foo', |
815 | \'foo', |
816 | ] |
817 | }, |
818 | { |
819 | equal => 1, |
820 | bindvals => [ |
821 | \42, |
822 | \42, |
823 | \'42', |
824 | ] |
825 | }, |
826 | |
827 | # arrayref - equal |
828 | { |
829 | equal => 1, |
830 | bindvals => [ |
831 | [], |
832 | [] |
833 | ] |
834 | }, |
835 | { |
836 | equal => 1, |
837 | bindvals => [ |
838 | [42], |
839 | [42], |
840 | ['42'], |
841 | ] |
842 | }, |
843 | { |
844 | equal => 1, |
845 | bindvals => [ |
846 | [1, 42], |
847 | [1, 42], |
848 | ['1', 42], |
849 | [1, '42'], |
850 | ['1', '42'], |
851 | ] |
852 | }, |
853 | |
854 | # hashref - equal |
855 | { |
856 | equal => 1, |
857 | bindvals => [ |
858 | { foo => 42 }, |
859 | { foo => 42 }, |
860 | { foo => '42' }, |
861 | ] |
862 | }, |
863 | { |
864 | equal => 1, |
865 | bindvals => [ |
866 | { foo => 42, bar => 1 }, |
867 | { foo => 42, bar => 1 }, |
868 | { foo => '42', bar => 1 }, |
869 | ] |
870 | }, |
871 | |
872 | # blessed object - equal |
873 | { |
874 | equal => 1, |
875 | bindvals => [ |
876 | bless(\(local $_ = 42), 'Life::Universe::Everything'), |
877 | bless(\(local $_ = 42), 'Life::Universe::Everything'), |
878 | ] |
879 | }, |
880 | { |
881 | equal => 1, |
882 | bindvals => [ |
883 | bless([42], 'Life::Universe::Everything'), |
884 | bless([42], 'Life::Universe::Everything'), |
885 | ] |
886 | }, |
887 | { |
888 | equal => 1, |
889 | bindvals => [ |
890 | bless({ answer => 42 }, 'Life::Universe::Everything'), |
891 | bless({ answer => 42 }, 'Life::Universe::Everything'), |
892 | ] |
893 | }, |
894 | |
895 | # complex data structure - equal |
896 | { |
897 | equal => 1, |
898 | bindvals => [ |
899 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
900 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
901 | ] |
902 | }, |
903 | |
904 | |
905 | # scalar - different |
906 | { |
907 | equal => 0, |
908 | bindvals => [ |
909 | undef, |
910 | 'foo', |
911 | 42, |
912 | ] |
913 | }, |
914 | |
915 | # scalarref - different |
916 | { |
917 | equal => 0, |
918 | bindvals => [ |
919 | \undef, |
920 | \'foo', |
921 | \42, |
922 | ] |
923 | }, |
924 | |
925 | # arrayref - different |
926 | { |
927 | equal => 0, |
928 | bindvals => [ |
929 | [undef], |
930 | ['foo'], |
931 | [42], |
932 | ] |
933 | }, |
934 | |
935 | # hashref - different |
936 | { |
937 | equal => 0, |
938 | bindvals => [ |
939 | { foo => undef }, |
940 | { foo => 'bar' }, |
941 | { foo => 42 }, |
942 | ] |
943 | }, |
944 | |
945 | # different types |
946 | { |
947 | equal => 0, |
948 | bindvals => [ |
949 | 'foo', |
950 | \'foo', |
951 | ['foo'], |
952 | { foo => 'bar' }, |
953 | ] |
954 | }, |
955 | |
956 | # complex data structure - different |
957 | { |
958 | equal => 0, |
959 | bindvals => [ |
960 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
961 | [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
962 | [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
963 | [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
964 | [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
965 | [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ], |
966 | [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ], |
967 | [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ], |
968 | [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ], |
969 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ], |
970 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ], |
971 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ], |
972 | [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ], |
973 | ] |
974 | }, |
975 | ); |
976 | |
b4085a1a |
977 | for my $test ( @sql_tests ) { |
3b9d807e |
978 | |
979 | # this does not work on 5.8.8 and earlier :( |
980 | #local @{*SQL::Abstract::Test::}{keys %{$test->{opts}}} = map { \$_ } values %{$test->{opts}} |
981 | # if $test->{opts}; |
982 | |
983 | my %restore_globals; |
984 | |
985 | for (keys %{$test->{opts} || {} }) { |
986 | $restore_globals{$_} = ${${*SQL::Abstract::Test::}{$_}}; |
987 | ${*SQL::Abstract::Test::}{$_} = \ do { my $cp = $test->{opts}{$_} }; |
988 | } |
989 | |
25823711 |
990 | my $statements = $test->{statements}; |
991 | while (@$statements) { |
992 | my $sql1 = shift @$statements; |
993 | foreach my $sql2 (@$statements) { |
e40f5df9 |
994 | |
25823711 |
995 | my $equal = eq_sql($sql1, $sql2); |
e40f5df9 |
996 | |
56c0595d |
997 | TODO: { |
998 | local $TODO = $test->{todo} if $test->{todo}; |
999 | |
1000 | if ($test->{equal}) { |
1001 | ok($equal, "equal SQL expressions should have been considered equal"); |
1002 | } else { |
1003 | ok(!$equal, "different SQL expressions should have been considered not equal"); |
1004 | } |
1005 | |
1006 | if ($equal ^ $test->{equal}) { |
0769ac0e |
1007 | my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2); |
2fadf08e |
1008 | $_ = dumper($_) for ($ast1, $ast2); |
0769ac0e |
1009 | |
b3b79607 |
1010 | diag "sql1: $sql1"; |
1011 | diag "sql2: $sql2"; |
2fadf08e |
1012 | note $sql_differ || 'No differences found'; |
b3b79607 |
1013 | note "ast1: $ast1"; |
1014 | note "ast2: $ast2"; |
56c0595d |
1015 | } |
25823711 |
1016 | } |
1017 | } |
1018 | } |
3b9d807e |
1019 | |
1020 | ${*SQL::Abstract::Test::}{$_} = \$restore_globals{$_} |
1021 | for keys %restore_globals; |
25823711 |
1022 | } |
1023 | |
32c34379 |
1024 | for my $test (@bind_tests) { |
1025 | my $bindvals = $test->{bindvals}; |
1026 | while (@$bindvals) { |
1027 | my $bind1 = shift @$bindvals; |
1028 | foreach my $bind2 (@$bindvals) { |
1029 | my $equal = eq_bind($bind1, $bind2); |
1030 | if ($test->{equal}) { |
1031 | ok($equal, "equal bind values considered equal"); |
1032 | } else { |
1033 | ok(!$equal, "different bind values considered not equal"); |
1034 | } |
1035 | |
1036 | if ($equal ^ $test->{equal}) { |
2fadf08e |
1037 | diag("bind1: " . dumper($bind1)); |
1038 | diag("bind2: " . dumper($bind2)); |
32c34379 |
1039 | } |
1040 | } |
1041 | } |
1042 | } |
e7827ba2 |
1043 | |
1044 | ok(eq_sql_bind( |
1045 | "SELECT * FROM foo WHERE id = ?", [42], |
1046 | "SELECT * FROM foo WHERE (id = ?)", [42], |
1047 | ), |
1048 | "eq_sql_bind considers equal SQL expressions and bind values equal" |
1049 | ); |
1050 | |
1051 | |
1052 | ok(!eq_sql_bind( |
1053 | "SELECT * FROM foo WHERE id = ?", [42], |
1054 | "SELECT * FROM foo WHERE (id = ?)", [0], |
1055 | ), |
1056 | "eq_sql_bind considers equal SQL expressions and different bind values different" |
1057 | ); |
1058 | |
1059 | ok(!eq_sql_bind( |
1060 | "SELECT * FROM foo WHERE id = ?", [42], |
1061 | "SELECT * FROM bar WHERE (id = ?)", [42], |
1062 | ), |
1063 | "eq_sql_bind considers different SQL expressions and equal bind values different" |
1064 | ); |
6f2a5b66 |
1065 | |
1066 | # test diag string |
1067 | ok (! eq_sql ( |
1068 | 'SELECT owner_name FROM books me WHERE ( source = ? )', |
1069 | 'SELECT owner_name FROM books me WHERE ( sUOrce = ? )', |
1070 | )); |
1071 | like( |
2fadf08e |
1072 | $sql_differ, |
6f2a5b66 |
1073 | qr/\Q[ source ] != [ sUOrce ]/, |
1074 | 'expected debug of literal diff', |
1075 | ); |
1076 | |
1077 | ok (! eq_sql ( |
1078 | 'SELECT owner_name FROM books me ORDER BY owner_name', |
1079 | 'SELECT owner_name FROM books me GROUP BY owner_name', |
1080 | )); |
1081 | like( |
2fadf08e |
1082 | $sql_differ, |
6f2a5b66 |
1083 | qr/\QOP [ORDER BY] != [GROUP BY]/, |
1084 | 'expected debug of op diff', |
1085 | ); |
1086 | |
1087 | ok (! eq_sql ( |
1088 | 'SELECT owner_name FROM books WHERE ( source = ? )', |
1089 | 'SELECT owner_name FROM books' |
1090 | )); |
1091 | |
1092 | like( |
2fadf08e |
1093 | $sql_differ, |
6f2a5b66 |
1094 | qr|\Q[WHERE source = ?] != [N/A]|, |
1095 | 'expected debug of missing branch', |
1096 | ); |
1097 | |
10e6c946 |
1098 | done_testing; |