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