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)))/,
102 todo => '( (x AND y) AND z ) should be reducable to ( x AND y AND z )',
104 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
105 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
106 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
111 todo => '( (x OR y) OR z ) should be reducable to ( x OR y OR z )',
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)/,
119 # WHERE condition - different
123 q/SELECT foo FROM bar WHERE a = 1/,
124 q/SELECT quux FROM bar WHERE a = 1/,
125 q/SELECT foo FROM quux WHERE a = 1/,
126 q/FOOBAR foo FROM bar WHERE a = 1/,
128 q/SELECT foo FROM bar WHERE a = 2/,
129 q/SELECT foo FROM bar WHERE a < 1/,
130 q/SELECT foo FROM bar WHERE b = 1/,
131 q/SELECT foo FROM bar WHERE (c = 1)/,
132 q/SELECT foo FROM bar WHERE (d = 1)/,
134 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
135 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
136 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
137 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
138 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
139 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
140 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
146 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
147 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
148 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
149 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
151 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
152 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
153 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
154 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
155 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
156 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
158 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
159 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
160 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
161 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
162 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
163 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
165 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
166 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
167 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
168 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
170 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
171 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
172 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
173 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
174 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
175 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
176 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
182 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
183 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
184 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
190 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
191 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
192 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
196 # JOIN condition - equal
200 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
201 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
202 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
203 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
204 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
241 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
242 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
243 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
249 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
250 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
251 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
252 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
253 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
254 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
314 # JOIN condition - different
318 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
319 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
320 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
321 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
323 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
324 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
325 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
326 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
327 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
329 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
330 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
331 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
332 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
333 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
334 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
335 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
341 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
342 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
343 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
344 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
346 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
347 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
348 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
349 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
350 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
351 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
353 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
354 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
355 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
356 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
357 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
358 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
360 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
361 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
362 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
363 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
365 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
366 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
367 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
368 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
369 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
370 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
371 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
375 # DISTINCT ON (...) not confused with JOIN ON (...)
379 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
380 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
381 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
382 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
383 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
385 SELECT DISTINCT ON (foo, quux)
394 SELECT DISTINCT ON (foo, quux)
403 SELECT DISTINCT ON (foo, quux)
411 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
412 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
413 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
421 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
422 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
423 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
424 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
430 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
431 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
432 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
433 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
434 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
436 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
437 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
438 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
439 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
440 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
444 # subselects - different
448 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
449 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
450 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
451 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
452 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
453 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
454 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
455 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
456 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
462 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
463 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
464 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
465 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
466 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
468 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
469 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
470 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
471 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
472 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
474 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
475 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
476 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
477 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
478 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
480 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
481 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
482 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
483 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
484 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
570 { foo => 42, bar => 1 },
571 { foo => 42, bar => 1 },
572 { foo => '42', bar => 1 },
576 # blessed object - equal
580 bless(\(local $_ = 42), 'Life::Universe::Everything'),
581 bless(\(local $_ = 42), 'Life::Universe::Everything'),
587 bless([42], 'Life::Universe::Everything'),
588 bless([42], 'Life::Universe::Everything'),
594 bless({ answer => 42 }, 'Life::Universe::Everything'),
595 bless({ answer => 42 }, 'Life::Universe::Everything'),
599 # complex data structure - equal
603 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
604 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
619 # scalarref - different
629 # arrayref - different
639 # hashref - different
660 # complex data structure - different
664 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
665 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
666 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
667 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
668 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
669 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
670 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
671 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
672 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
673 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
674 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
675 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
676 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
683 map { $_ * ($_ - 1) / 2 }
684 map { scalar @{$_->{statements}} }
688 map { $_ * ($_ - 1) / 2 }
689 map { scalar @{$_->{bindvals}} }
694 use_ok('SQL::Abstract::Test', import => [qw(
695 eq_sql_bind eq_sql eq_bind is_same_sql_bind
698 for my $test (@sql_tests) {
699 my $statements = $test->{statements};
700 while (@$statements) {
701 my $sql1 = shift @$statements;
702 foreach my $sql2 (@$statements) {
703 my $equal = eq_sql($sql1, $sql2);
705 local $TODO = $test->{todo} if $test->{todo};
707 if ($test->{equal}) {
708 ok($equal, "equal SQL expressions should have been considered equal");
710 ok(!$equal, "different SQL expressions should have been considered not equal");
713 if ($equal ^ $test->{equal}) {
722 for my $test (@bind_tests) {
723 my $bindvals = $test->{bindvals};
725 my $bind1 = shift @$bindvals;
726 foreach my $bind2 (@$bindvals) {
727 my $equal = eq_bind($bind1, $bind2);
728 if ($test->{equal}) {
729 ok($equal, "equal bind values considered equal");
731 ok(!$equal, "different bind values considered not equal");
734 if ($equal ^ $test->{equal}) {
735 diag("bind1: " . Dumper($bind1));
736 diag("bind2: " . Dumper($bind2));
743 "SELECT * FROM foo WHERE id = ?", [42],
744 "SELECT * FROM foo WHERE (id = ?)", [42],
746 "eq_sql_bind considers equal SQL expressions and bind values equal"
751 "SELECT * FROM foo WHERE id = ?", [42],
752 "SELECT * FROM foo WHERE (id = ?)", [0],
754 "eq_sql_bind considers equal SQL expressions and different bind values different"
758 "SELECT * FROM foo WHERE id = ?", [42],
759 "SELECT * FROM bar WHERE (id = ?)", [42],
761 "eq_sql_bind considers different SQL expressions and equal bind values different"