5 use List::Util qw(sum);
11 # WHERE condition - equal
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 )/,
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 ) )/,
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)))/,
101 # WHERE condition - different
105 q/SELECT foo FROM bar WHERE a = 1/,
106 q/SELECT quux FROM bar WHERE a = 1/,
107 q/SELECT foo FROM quux WHERE a = 1/,
108 q/FOOBAR foo FROM bar WHERE a = 1/,
110 q/SELECT foo FROM bar WHERE a = 2/,
111 q/SELECT foo FROM bar WHERE a < 1/,
112 q/SELECT foo FROM bar WHERE b = 1/,
113 q/SELECT foo FROM bar WHERE (c = 1)/,
114 q/SELECT foo FROM bar WHERE (d = 1)/,
116 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
117 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
118 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
119 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
120 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
121 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
122 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
128 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
129 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
130 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
131 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
133 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
134 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
135 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
136 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
137 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
138 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
140 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
141 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
142 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
143 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
144 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
145 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
147 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
148 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
149 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
150 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
152 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
153 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
154 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
155 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
156 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
157 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
158 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
162 # JOIN condition - equal
166 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
167 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
168 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
169 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
170 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
207 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
208 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
209 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
215 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
216 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
217 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
218 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
219 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
220 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
280 # JOIN condition - different
284 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
285 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
286 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
287 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
289 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
290 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
291 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
292 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
293 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
295 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
296 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
297 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
298 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
299 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
300 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
301 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
307 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
308 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
309 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
310 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
312 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
313 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
314 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
315 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
316 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
317 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
319 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
320 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
321 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
322 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
323 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
324 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
326 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
327 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
328 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
329 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
331 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
332 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
333 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
334 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
335 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
336 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
337 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
341 # DISTINCT ON (...) not confused with JOIN ON (...)
345 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
346 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
347 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
348 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
349 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
351 SELECT DISTINCT ON (foo, quux)
360 SELECT DISTINCT ON (foo, quux)
369 SELECT DISTINCT ON (foo, quux)
377 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
378 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
379 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
387 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
388 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
389 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
390 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
396 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
397 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
398 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
399 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
400 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
402 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
403 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
404 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
405 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
406 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
410 # subselects - different
414 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
415 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
416 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
417 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
418 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
419 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
420 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
421 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
422 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
428 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
429 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
430 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
431 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
432 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
434 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
435 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
436 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
437 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
438 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
440 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
441 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
442 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
443 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
444 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
446 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
447 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
448 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
449 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
450 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
536 { foo => 42, bar => 1 },
537 { foo => 42, bar => 1 },
538 { foo => '42', bar => 1 },
542 # blessed object - equal
546 bless(\(local $_ = 42), 'Life::Universe::Everything'),
547 bless(\(local $_ = 42), 'Life::Universe::Everything'),
553 bless([42], 'Life::Universe::Everything'),
554 bless([42], 'Life::Universe::Everything'),
560 bless({ answer => 42 }, 'Life::Universe::Everything'),
561 bless({ answer => 42 }, 'Life::Universe::Everything'),
565 # complex data structure - equal
569 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
570 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
585 # scalarref - different
595 # arrayref - different
605 # hashref - different
626 # complex data structure - different
630 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
631 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
632 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
633 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
634 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
635 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
636 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
637 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
638 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
639 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
640 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
641 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
642 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
649 map { $_ * ($_ - 1) / 2 }
650 map { scalar @{$_->{statements}} }
654 map { $_ * ($_ - 1) / 2 }
655 map { scalar @{$_->{bindvals}} }
659 use_ok('SQL::Abstract::Test', import => [qw(eq_sql eq_bind is_same_sql_bind)]);
661 for my $test (@sql_tests) {
662 my $statements = $test->{statements};
663 while (@$statements) {
664 my $sql1 = shift @$statements;
665 foreach my $sql2 (@$statements) {
666 my $equal = eq_sql($sql1, $sql2);
667 if ($test->{equal}) {
668 ok($equal, "equal SQL expressions considered equal");
670 ok(!$equal, "different SQL expressions considered not equal");
673 if ($equal ^ $test->{equal}) {
681 for my $test (@bind_tests) {
682 my $bindvals = $test->{bindvals};
684 my $bind1 = shift @$bindvals;
685 foreach my $bind2 (@$bindvals) {
686 my $equal = eq_bind($bind1, $bind2);
687 if ($test->{equal}) {
688 ok($equal, "equal bind values considered equal");
690 ok(!$equal, "different bind values considered not equal");
693 if ($equal ^ $test->{equal}) {
694 diag("bind1: " . Dumper($bind1));
695 diag("bind2: " . Dumper($bind2));