[scpubgit/Q-Branch.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)/,
        ]
      },

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