[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 => [
          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 => [
	206	q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
	207	q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN ? AND ?) AND status = ? )/,
	208	]
	209	},
25823711	210
	211	# JOIN condition - equal
	212	{
	213	equal => 1,
	214	statements => [
	215	q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
	216	q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
	217	q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
	218	q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
	219	q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
	220	q/
	221	SELECT
	222	foo
	223	FROM
	224	bar
	225	JOIN
	226	baz
	227	ON
	228	a = 1
	229	WHERE
	230	x = 1
	231	/,
	232	q/
	233	SELECT
	234	foo
	235	FROM
	236	bar
	237	JOIN
	238	baz
	239	ON
	240	(a = 1)
	241	WHERE
	242	x = 1
	243	/,
	244	q/
	245	SELECT
	246	foo
	247	FROM
	248	bar
	249	JOIN
	250	baz
	251	ON
	252	( a = 1 )
	253	WHERE
	254	x = 1
	255	/,
	256	q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
	257	q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
	258	q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
	259	]
	260	},
	261	{
	262	equal => 1,
	263	statements => [
	264	q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
	265	q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
	266	q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
	267	q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
	268	q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
	269	q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
	270	q/
	271	SELECT
	272	foo
	273	FROM
274	bar
275	JOIN
276	baz
277	ON
278	a = 1
279	AND
280	b = 1
281	WHERE
282	x = 1
283	/,
284	q/
285	SELECT
286	foo
287	FROM
288	bar
289	JOIN
290	baz
291	ON
292	(a = 1
293	AND
294	b = 1)
295	WHERE
296	x = 1
297	/,
298	q/
299	SELECT
300	foo
301	FROM
302	bar
303	JOIN
304	baz
305	ON
306	(a = 1)
307	AND
308	(b = 1)
309	WHERE
310	x = 1
311	/,
312	q/
313	SELECT
314	foo
315	FROM
316	bar
317	JOIN
318	baz
319	ON
320	((a = 1)
321	AND
322	(b = 1))
323	WHERE
324	x = 1
325	/,
326	]
327	},
328
329	# JOIN condition - different
330	{
331	equal => 0,
332	statements => [
333	q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
334	q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
335	q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
336	q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
337
338	q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
339	q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
340	q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
341	q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
342	q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
343
344	q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
345	q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
346	q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
347	q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
348	q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
349	q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
350	q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
351	]
352	},
353	{
354	equal => 0,
355	statements => [
356	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
357	q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
358	q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
359	q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
360
361	q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
362	q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
363	q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
364	q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
365	q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
366	q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
367
368	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
369	q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
370	q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
371	q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
372	q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
373	q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
374
375	q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
376	q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
377	q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
378	q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
379
380	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
381	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
382	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
383	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
384	q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
385	q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
386	q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
387	]
388	},
389
390	# DISTINCT ON (...) not confused with JOIN ON (...)
391	{
392	equal => 1,
393	statements => [
394	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
395	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
396	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
397	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
398	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
399	q/
400	SELECT DISTINCT ON (foo, quux)
401	foo,
402	quux
403	FROM
404	bar
405	WHERE
406	a = 1
407	/,
408	q/
409	SELECT DISTINCT ON (foo, quux)
410	foo,
411	quux
412	FROM
413	bar
414	WHERE
415	(a = 1)
416	/,
417	q/
418	SELECT DISTINCT ON (foo, quux)
419	foo,
420	quux
421	FROM
422	bar
423	WHERE
424	( a = 1 )
425	/,
426	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
427	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
428	q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
429	]
430	},
431
432	# subselects - equal
433	{
434	equal => 1,
435	statements => [
436	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
437	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
438	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
439	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
440	]
441	},
442	{
443	equal => 1,
444	statements => [
445	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
446	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
447	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
448	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
449	q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
450
451	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
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	]
457	},
458
459	# subselects - different
460	{
461	equal => 0,
462	statements => [
463	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
464	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
465	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
466	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
467	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
468	q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
469	q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
470	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
471	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
472	]
473	},
474	{
475	equal => 0,
476	statements => [
477	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
478	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
479	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
480	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
481	q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
482
483	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
484	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
485	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
486	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
487	q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
488
489	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
490	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
491	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
492	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
493	q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
494
495	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
496	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
497	q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
498	q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
499	q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
500	]
501	},
502	);
503
32c34379	504	my @bind_tests = (
	505	# scalar - equal
	506	{
	507	equal => 1,
	508	bindvals => [
	509	undef,
	510	undef,
	511	]
	512	},
	513	{
	514	equal => 1,
	515	bindvals => [
	516	'foo',
	517	'foo',
	518	]
	519	},
	520	{
	521	equal => 1,
	522	bindvals => [
	523	42,
	524	42,
	525	'42',
	526	]
	527	},
	528
	529	# scalarref - equal
	530	{
	531	equal => 1,
	532	bindvals => [
	533	\'foo',
	534	\'foo',
	535	]
	536	},
	537	{
	538	equal => 1,
	539	bindvals => [
	540	\42,
	541	\42,
	542	\'42',
	543	]
	544	},
	545
	546	# arrayref - equal
	547	{
	548	equal => 1,
	549	bindvals => [
	550	[],
	551	[]
	552	]
	553	},
	554	{
	555	equal => 1,
	556	bindvals => [
	557	[42],
	558	[42],
	559	['42'],
	560	]
	561	},
	562	{
	563	equal => 1,
	564	bindvals => [
	565	[1, 42],
	566	[1, 42],
	567	['1', 42],
568	[1, '42'],
569	['1', '42'],
570	]
571	},
572
573	# hashref - equal
574	{
575	equal => 1,
576	bindvals => [
577	{ foo => 42 },
578	{ foo => 42 },
579	{ foo => '42' },
580	]
581	},
582	{
583	equal => 1,
584	bindvals => [
585	{ foo => 42, bar => 1 },
586	{ foo => 42, bar => 1 },
587	{ foo => '42', bar => 1 },
588	]
589	},
590
591	# blessed object - equal
592	{
593	equal => 1,
594	bindvals => [
595	bless(\(local $_ = 42), 'Life::Universe::Everything'),
596	bless(\(local $_ = 42), 'Life::Universe::Everything'),
597	]
598	},
599	{
600	equal => 1,
601	bindvals => [
602	bless([42], 'Life::Universe::Everything'),
603	bless([42], 'Life::Universe::Everything'),
604	]
605	},
606	{
607	equal => 1,
608	bindvals => [
609	bless({ answer => 42 }, 'Life::Universe::Everything'),
610	bless({ answer => 42 }, 'Life::Universe::Everything'),
611	]
612	},
613
614	# complex data structure - equal
615	{
616	equal => 1,
617	bindvals => [
618	[42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
619	[42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
620	]
621	},
622
623
624	# scalar - different
625	{
626	equal => 0,
627	bindvals => [
628	undef,
629	'foo',
630	42,
631	]
632	},
633
634	# scalarref - different
635	{
636	equal => 0,
637	bindvals => [
638	\undef,
639	\'foo',
640	\42,
641	]
642	},
643
644	# arrayref - different
645	{
646	equal => 0,
647	bindvals => [
648	[undef],
649	['foo'],
650	[42],
651	]
652	},
653
654	# hashref - different
655	{
656	equal => 0,
657	bindvals => [
658	{ foo => undef },
659	{ foo => 'bar' },
660	{ foo => 42 },
661	]
662	},
663
664	# different types
665	{
666	equal => 0,
667	bindvals => [
668	'foo',
669	\'foo',
670	['foo'],
671	{ foo => 'bar' },
672	]
673	},
674
675	# complex data structure - different
676	{
677	equal => 0,
678	bindvals => [
679	[42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
680	[43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
681	[42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
682	[42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
683	[42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
684	[42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
685	[42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
686	[42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
687	[42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
688	[42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
689	[42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
690	[42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
691	[42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
692	]
693	},
694	);
695
25823711	696	plan tests => 1 +
	697	sum(
	698	map { $_ * ($_ - 1) / 2 }
	699	map { scalar @{$_->{statements}} }
	700	@sql_tests
	701	) +
	702	sum(
	703	map { $_ * ($_ - 1) / 2 }
	704	map { scalar @{$_->{bindvals}} }
	705	@bind_tests
e7827ba2	706	) +
e7827ba2	707	3;
32c34379	708
e7827ba2	709	use_ok('SQL::Abstract::Test', import => [qw(
	710	eq_sql_bind eq_sql eq_bind is_same_sql_bind
	711	)]);
32c34379	712
25823711	713	for my $test (@sql_tests) {
	714	my $statements = $test->{statements};
	715	while (@$statements) {
	716	my $sql1 = shift @$statements;
	717	foreach my $sql2 (@$statements) {
	718	my $equal = eq_sql($sql1, $sql2);
56c0595d	719	TODO: {
	720	local $TODO = $test->{todo} if $test->{todo};
	721
	722	if ($test->{equal}) {
	723	ok($equal, "equal SQL expressions should have been considered equal");
	724	} else {
	725	ok(!$equal, "different SQL expressions should have been considered not equal");
	726	}
	727
	728	if ($equal ^ $test->{equal}) {
	729	diag("sql1: $sql1");
	730	diag("sql2: $sql2");
	731	}
25823711	732	}
	733	}
	734	}
	735	}
	736
32c34379	737	for my $test (@bind_tests) {
	738	my $bindvals = $test->{bindvals};
	739	while (@$bindvals) {
	740	my $bind1 = shift @$bindvals;
	741	foreach my $bind2 (@$bindvals) {
	742	my $equal = eq_bind($bind1, $bind2);
	743	if ($test->{equal}) {
	744	ok($equal, "equal bind values considered equal");
	745	} else {
	746	ok(!$equal, "different bind values considered not equal");
	747	}
	748
	749	if ($equal ^ $test->{equal}) {
	750	diag("bind1: " . Dumper($bind1));
	751	diag("bind2: " . Dumper($bind2));
	752	}
	753	}
	754	}
	755	}
e7827ba2	756
	757	ok(eq_sql_bind(
	758	"SELECT * FROM foo WHERE id = ?", [42],
	759	"SELECT * FROM foo WHERE (id = ?)", [42],
	760	),
	761	"eq_sql_bind considers equal SQL expressions and bind values equal"
	762	);
	763
	764
	765	ok(!eq_sql_bind(
	766	"SELECT * FROM foo WHERE id = ?", [42],
	767	"SELECT * FROM foo WHERE (id = ?)", [0],
	768	),
	769	"eq_sql_bind considers equal SQL expressions and different bind values different"
	770	);
	771
	772	ok(!eq_sql_bind(
	773	"SELECT * FROM foo WHERE id = ?", [42],
	774	"SELECT * FROM bar WHERE (id = ?)", [42],
	775	),
	776	"eq_sql_bind considers different SQL expressions and equal bind values different"
	777	);