[dbsrgits/SQL-Abstract.git] / t / 10test.t

#!/usr/bin/perl

use strict;
use warnings;
use List::Util qw(sum);

use Test::More;


my @sql_tests = (
      # WHERE condition - equal
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1/,
          q/SELECT foo FROM bar WHERE a=1/,
          q/SELECT foo FROM bar WHERE (a = 1)/,
          q/SELECT foo FROM bar WHERE (a=1)/,
          q/SELECT foo FROM bar WHERE ( a = 1 )/,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              a = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              (a = 1)
          /,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              ( a = 1 )
          /,
          q/SELECT foo FROM bar WHERE ((a = 1))/,
          q/SELECT foo FROM bar WHERE ( (a = 1) )/,
          q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/,
        ]
      },
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
          q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/,
          q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/,
          q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/,
          q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
          q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              a = 1
              AND
              b = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              (a = 1
              AND
              b = 1)
          /,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              (a = 1)
              AND
              (b = 1)
          /,
          q/
            SELECT
              foo
            FROM
              bar
            WHERE
              ((a = 1)
              AND
              (b = 1))
          /,
        ]
      },
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
          q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
          q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
          q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
        ]
      },
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
          q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
          q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
          q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
        ]
      },
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
          q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
          q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
        ]
      },

      # WHERE condition - different
      {
        equal => 0,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1/,
          q/SELECT quux FROM bar WHERE a = 1/,
          q/SELECT foo FROM quux WHERE a = 1/,
          q/FOOBAR foo FROM bar WHERE a = 1/,

          q/SELECT foo FROM bar WHERE a = 2/,
          q/SELECT foo FROM bar WHERE a < 1/,
          q/SELECT foo FROM bar WHERE b = 1/,
          q/SELECT foo FROM bar WHERE (c = 1)/,
          q/SELECT foo FROM bar WHERE (d = 1)/,

          q/SELECT foo FROM bar WHERE a = 1 AND quux/,
          q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
          q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
          q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
          q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
          q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
        ]
      },
      {
        equal => 0,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
          q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
          q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
          q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,

          q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
          q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
          q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
          q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
          q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
          q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,

          q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
          q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
          q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
          q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
          q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
          q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,

          q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
          q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
          q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
          q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,

          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
          q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
        ]
      },
      {
        equal => 0,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
          q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
          q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
        ]
      },
      {
        equal => 0,
        statements => [
          q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
          q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
          q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
        ]
      },
      {
        equal => 0,
        statements => [
          # BETWEEN with/without parenthesis around itself/RHS is a sticky business
          # if I made a mistake here, simply rewrite the special BETWEEN handling in
          # _recurse_parse()
          #
          # by RIBASUSHI
          q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
          q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
          q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/,
          q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
        ]
      },

      # JOIN condition - equal
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              a = 1
            WHERE
              x = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              (a = 1)
            WHERE
              x = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              ( a = 1 )
            WHERE
              x = 1
          /,
          q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
        ]
      },
      {
        equal => 1,
        statements => [
          q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
          q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              a = 1
              AND
              b = 1
            WHERE
              x = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              (a = 1
              AND
              b = 1)
            WHERE
              x = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              (a = 1)
              AND
              (b = 1)
            WHERE
              x = 1
          /,
          q/
            SELECT
              foo
            FROM
              bar
            JOIN
              baz
            ON
              ((a = 1)
              AND
              (b = 1))
            WHERE
              x = 1
          /,
        ]
      },

      # JOIN condition - different
      {
        equal => 0,
        statements => [
          q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
          q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
          q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
          q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,

          q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,

          q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
        ]
      },
      {
        equal => 0,
        statements => [
          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
          q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
          q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
          q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,

          q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,

          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,

          q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
          q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,

          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
          q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
          q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
        ]
      },

      # DISTINCT ON (...) not confused with JOIN ON (...)
      {
        equal => 1,
        statements => [
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
          q/
            SELECT DISTINCT ON (foo, quux)
              foo,
              quux
            FROM
              bar
            WHERE
              a = 1
          /,
          q/
            SELECT DISTINCT ON (foo, quux)
              foo,
              quux
            FROM
              bar
            WHERE
              (a = 1)
          /,
          q/
            SELECT DISTINCT ON (foo, quux)
              foo,
              quux
            FROM
              bar
            WHERE
              ( a = 1 )
          /,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
          q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
        ]
      },

      # subselects - equal
      {
        equal => 1,
        statements => [
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
        ]
      },
      {
        equal => 1,
        statements => [
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,

          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
        ]
      },

      # subselects - different
      {
        equal => 0,
        statements => [
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
        ]
      },
      {
        equal => 0,
        statements => [
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
          q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,

          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,

          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
          q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,

          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
          q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
        ]
      },
);

my @bind_tests = (
  # scalar - equal
  {
    equal => 1,
    bindvals => [
      undef,
      undef,
    ]
  },
  {
    equal => 1,
    bindvals => [
      'foo',
      'foo',
    ]
  },
  {
    equal => 1,
    bindvals => [
      42,
      42,
      '42',
    ]
  },

  # scalarref - equal
  {
    equal => 1,
    bindvals => [
      \'foo',
      \'foo',
    ]
  },
  {
    equal => 1,
    bindvals => [
      \42,
      \42,
      \'42',
    ]
  },

  # arrayref - equal
  {
    equal => 1,
    bindvals => [
      [],
      []
    ]
  },
  {
    equal => 1,
    bindvals => [
      [42],
      [42],
      ['42'],
    ]
  },
  {
    equal => 1,
    bindvals => [
      [1, 42],
      [1, 42],
      ['1', 42],
      [1, '42'],
      ['1', '42'],
    ]
  },

  # hashref - equal
  {
    equal => 1,
    bindvals => [
      { foo => 42 },
      { foo => 42 },
      { foo => '42' },
    ]
  },
  {
    equal => 1,
    bindvals => [
      { foo => 42, bar => 1 },
      { foo => 42, bar => 1 },
      { foo => '42', bar => 1 },
    ]
  },

  # blessed object - equal
  {
    equal => 1,
    bindvals => [
      bless(\(local $_ = 42), 'Life::Universe::Everything'),
      bless(\(local $_ = 42), 'Life::Universe::Everything'),
    ]
  },
  {
    equal => 1,
    bindvals => [
      bless([42], 'Life::Universe::Everything'),
      bless([42], 'Life::Universe::Everything'),
    ]
  },
  {
    equal => 1,
    bindvals => [
      bless({ answer => 42 }, 'Life::Universe::Everything'),
      bless({ answer => 42 }, 'Life::Universe::Everything'),
    ]
  },

  # complex data structure - equal
  {
    equal => 1,
    bindvals => [
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
    ]
  },


  # scalar - different
  {
    equal => 0,
    bindvals => [
      undef,
      'foo',
      42,
    ]
  },

  # scalarref - different
  {
    equal => 0,
    bindvals => [
      \undef,
      \'foo',
      \42,
    ]
  },

  # arrayref - different
  {
    equal => 0,
    bindvals => [
      [undef],
      ['foo'],
      [42],
    ]
  },

  # hashref - different
  {
    equal => 0,
    bindvals => [
      { foo => undef },
      { foo => 'bar' },
      { foo => 42 },
    ]
  },

  # different types
  {
    equal => 0,
    bindvals => [
      'foo',
      \'foo',
      ['foo'],
      { foo => 'bar' },
    ]
  },

  # complex data structure - different
  {
    equal => 0,
    bindvals => [
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
      [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
    ]
  },
);

plan tests => 1 +
  sum(
    map { $_ * ($_ - 1) / 2 }
      map { scalar @{$_->{statements}} }
        @sql_tests
  ) +
  sum(
    map { $_ * ($_ - 1) / 2 }
      map { scalar @{$_->{bindvals}} }
        @bind_tests
  ) +
  3;

use_ok('SQL::Abstract::Test', import => [qw(
  eq_sql_bind eq_sql eq_bind is_same_sql_bind
)]);

for my $test (@sql_tests) {
  my $statements = $test->{statements};
  while (@$statements) {
    my $sql1 = shift @$statements;
    foreach my $sql2 (@$statements) {
      my $equal = eq_sql($sql1, $sql2);
      TODO: {
        local $TODO = $test->{todo} if $test->{todo};

        if ($test->{equal}) {
          ok($equal, "equal SQL expressions should have been considered equal");
        } else {
          ok(!$equal, "different SQL expressions should have been considered not equal");
        }

        if ($equal ^ $test->{equal}) {
          diag("sql1: $sql1");
          diag("sql2: $sql2");
        }
      }
    }
  }
}

for my $test (@bind_tests) {
  my $bindvals = $test->{bindvals};
  while (@$bindvals) {
    my $bind1 = shift @$bindvals;
    foreach my $bind2 (@$bindvals) {
      my $equal = eq_bind($bind1, $bind2);
      if ($test->{equal}) {
        ok($equal, "equal bind values considered equal");
      } else {
        ok(!$equal, "different bind values considered not equal");
      }

      if ($equal ^ $test->{equal}) {
        diag("bind1: " . Dumper($bind1));
        diag("bind2: " . Dumper($bind2));
      }
    }
  }
}

ok(eq_sql_bind(
    "SELECT * FROM foo WHERE id = ?", [42],
    "SELECT * FROM foo WHERE (id = ?)", [42],
  ),
  "eq_sql_bind considers equal SQL expressions and bind values equal"
);


ok(!eq_sql_bind(
    "SELECT * FROM foo WHERE id = ?", [42],
    "SELECT * FROM foo WHERE (id = ?)", [0],
  ),
  "eq_sql_bind considers equal SQL expressions and different bind values different"
);

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