X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fsqlmaker%2Flimit_dialects%2Ftorture.t;fp=t%2Fsqlmaker%2Flimit_dialects%2Ftorture.t;h=7bb116b10c13cab3e05258409cc3d02a978b8259;hb=fe0708a2d68b5d34b6bc6f7e70164c3e569f1dd0;hp=0000000000000000000000000000000000000000;hpb=01272eb81fe3a43e0a2f7befa465cc669945d543;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/sqlmaker/limit_dialects/torture.t b/t/sqlmaker/limit_dialects/torture.t
new file mode 100644
index 0000000..7bb116b
--- /dev/null
+++ b/t/sqlmaker/limit_dialects/torture.t
@@ -0,0 +1,658 @@
+use strict;
+use warnings;
+
+use Test::More;
+use lib qw(t/lib);
+use DBICTest;
+use DBIC::SqlMakerTest;
+
+my $schema = DBICTest->init_schema;
+
+my $attr = {};
+my @where_bind = (
+  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
+  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
+  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+);
+my @select_bind = (
+  [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
+);
+my @group_bind = (
+  [ $attr => 21 ],
+);
+my @having_bind = (
+  [ $attr => 31 ],
+);
+my @order_bind = (
+  [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
+);
+
+my $tests = {
+
+  LimitOffset => {
+    ordered_limit_offset => [
+      '(
+        SELECT me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY ? / ?, ?
+        LIMIT ?
+        OFFSET ?
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ],
+    ],
+  },
+
+  LimitXY => {
+    ordered_limit_offset => [
+      '(
+        SELECT me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY ? / ?, ?
+        LIMIT ?, ?
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 4 ],
+      ],
+    ],
+  },
+
+  SkipFirst => {
+    ordered_limit_offset => [
+      '(
+        SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY ? / ?, ?
+      )',
+      [
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+  },
+
+  FirstSkip => {
+    ordered_limit_offset => [
+      '(
+        SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY ? / ?, ?
+      )',
+      [
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+  },
+
+  RowNumberOver => do {
+    my $unordered_sql = '(
+      SELECT me.id, owner__id, owner__name, bar, baz
+        FROM (
+          SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
+            FROM (
+              SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+                FROM books me
+                JOIN owners owner
+                  ON owner.id = me.owner
+              WHERE source != ? AND me.title = ? AND source = ?
+              GROUP BY avg(me.id / ?)
+              HAVING ?
+            ) me
+      ) me
+      WHERE rno__row__index >= ? AND rno__row__index <= ?
+    )';
+
+    my $ordered_sql = '(
+      SELECT me.id, owner__id, owner__name, bar, baz
+        FROM (
+          SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__1, ORDER__BY__2 ) AS rno__row__index
+            FROM (
+              SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
+                     ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
+                FROM books me
+                JOIN owners owner
+                  ON owner.id = me.owner
+              WHERE source != ? AND me.title = ? AND source = ?
+              GROUP BY avg(me.id / ?)
+              HAVING ?
+            ) me
+      ) me
+      WHERE rno__row__index >= ? AND rno__row__index <= ?
+    )';
+
+    {
+      limit => [$unordered_sql,
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 1 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      limit_offset => [$unordered_sql,
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+          [ { sqlt_datatype => 'integer' } => 7 ],
+        ],
+      ],
+      ordered_limit => [$ordered_sql,
+        [
+          @select_bind,
+          @order_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 1 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      ordered_limit_offset => [$ordered_sql,
+        [
+          @select_bind,
+          @order_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+          [ { sqlt_datatype => 'integer' } => 7 ],
+        ],
+      ],
+    };
+  },
+
+  RowNum => do {
+    my $limit_sql = sub {
+      sprintf '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+              FROM books me
+              JOIN owners owner
+                ON owner.id = me.owner
+            WHERE source != ? AND me.title = ? AND source = ?
+            GROUP BY avg(me.id / ?)
+            HAVING ?
+            %s
+          ) me
+        WHERE ROWNUM <= ?
+      )', $_[0] || '';
+    };
+
+    {
+      limit => [ $limit_sql->(),
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      limit_offset => [
+        '(
+          SELECT me.id, owner__id, owner__name, bar, baz
+            FROM (
+              SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
+                FROM (
+                  SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+                    FROM books me
+                    JOIN owners owner
+                      ON owner.id = me.owner
+                  WHERE source != ? AND me.title = ? AND source = ?
+                  GROUP BY avg(me.id / ?)
+                  HAVING ?
+                ) me
+            ) me
+          WHERE rownum__index BETWEEN ? AND ?
+        )',
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+          [ { sqlt_datatype => 'integer' } => 7 ],
+        ],
+      ],
+      ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          @order_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      ordered_limit_offset => [
+        '(
+          SELECT me.id, owner__id, owner__name, bar, baz
+            FROM (
+              SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
+                FROM (
+                  SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+                    FROM books me
+                    JOIN owners owner
+                      ON owner.id = me.owner
+                  WHERE source != ? AND me.title = ? AND source = ?
+                  GROUP BY avg(me.id / ?)
+                  HAVING ?
+                  ORDER BY ? / ?, ?
+                ) me
+              WHERE ROWNUM <= ?
+            ) me
+          WHERE rownum__index >= ?
+        )',
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          @order_bind,
+          [ { sqlt_datatype => 'integer' } => 7 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+    };
+  },
+
+  FetchFirst => {
+    limit => [
+      '(
+        SELECT me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        FETCH FIRST 4 ROWS ONLY
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    limit_offset => [
+      '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+              FROM books me
+              JOIN owners owner
+                ON owner.id = me.owner
+            WHERE source != ? AND me.title = ? AND source = ?
+            GROUP BY avg(me.id / ?)
+            HAVING ?
+            ORDER BY me.id
+            FETCH FIRST 7 ROWS ONLY
+          ) me
+        ORDER BY me.id DESC
+        FETCH FIRST 4 ROWS ONLY
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    ordered_limit => [
+      '(
+        SELECT me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY ? / ?, ?
+        FETCH FIRST 4 ROWS ONLY
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+    ordered_limit_offset => [
+      '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__1, ORDER__BY__2
+              FROM (
+                SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
+                  FROM books me
+                  JOIN owners owner
+                    ON owner.id = me.owner
+                WHERE source != ? AND me.title = ? AND source = ?
+                GROUP BY avg(me.id / ?)
+                HAVING ?
+                ORDER BY ? / ?, ?
+                FETCH FIRST 7 ROWS ONLY
+              ) me
+            ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
+            FETCH FIRST 4 ROWS ONLY
+          ) me
+        ORDER BY ORDER__BY__1, ORDER__BY__2
+      )',
+      [
+        @select_bind,
+        @order_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
+      ],
+    ],
+  },
+
+  Top => {
+    limit => [
+      '(
+        SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    limit_offset => [
+      '(
+        SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+              FROM books me
+              JOIN owners owner
+                ON owner.id = me.owner
+            WHERE source != ? AND me.title = ? AND source = ?
+            GROUP BY avg(me.id / ?)
+            HAVING ?
+            ORDER BY me.id
+          ) me
+        ORDER BY me.id DESC
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    ordered_limit => [
+      '(
+        SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY ? / ?, ?
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+    ordered_limit_offset => [
+      '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__1, ORDER__BY__2
+              FROM (
+                SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
+                  FROM books me
+                  JOIN owners owner
+                    ON owner.id = me.owner
+                WHERE source != ? AND me.title = ? AND source = ?
+                GROUP BY avg(me.id / ?)
+                HAVING ?
+                ORDER BY ? / ?, ?
+              ) me
+            ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
+          ) me
+        ORDER BY ORDER__BY__1, ORDER__BY__2
+      )',
+      [
+        @select_bind,
+        @order_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
+      ],
+    ],
+  },
+
+  RowCountOrGenericSubQ => {
+    limit => [
+      '(
+        SET ROWCOUNT 4
+        SELECT me.id, owner.id, owner.name, ? * ?, ?
+          FROM books me
+          JOIN owners owner
+            ON owner.id = me.owner
+        WHERE source != ? AND me.title = ? AND source = ?
+        GROUP BY avg(me.id / ?)
+        HAVING ?
+        ORDER BY me.id
+        SET ROWCOUNT 0
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    limit_offset => [
+      '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+              FROM books me
+              JOIN owners owner
+                ON owner.id = me.owner
+            WHERE source != ? AND me.title = ? AND source = ?
+            GROUP BY avg( me.id / ? )
+            HAVING ?
+          ) me
+        WHERE (
+          SELECT COUNT( * )
+            FROM books rownum__emulation
+          WHERE rownum__emulation.id < me.id
+        ) BETWEEN ? AND ?
+        ORDER BY me.id
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 6 ],
+      ],
+    ],
+  },
+
+  GenericSubQ => {
+    limit => [
+      '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+              FROM books me
+              JOIN owners owner
+                ON owner.id = me.owner
+            WHERE source != ? AND me.title = ? AND source = ?
+            GROUP BY avg( me.id / ? )
+            HAVING ?
+          ) me
+        WHERE (
+          SELECT COUNT( * )
+            FROM books rownum__emulation
+          WHERE rownum__emulation.id < me.id
+        ) < ?
+        ORDER BY me.id
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+      ],
+    ],
+    limit_offset => [
+      '(
+        SELECT me.id, owner__id, owner__name, bar, baz
+          FROM (
+            SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
+              FROM books me
+              JOIN owners owner
+                ON owner.id = me.owner
+            WHERE source != ? AND me.title = ? AND source = ?
+            GROUP BY avg( me.id / ? )
+            HAVING ?
+          ) me
+        WHERE (
+          SELECT COUNT( * )
+            FROM books rownum__emulation
+          WHERE rownum__emulation.id < me.id
+        ) BETWEEN ? AND ?
+        ORDER BY me.id
+      )',
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 6 ],
+      ],
+    ],
+  }
+};
+
+for my $limtype (sort keys %$tests) {
+
+  Test::Builder->new->is_passing or exit;
+
+  delete $schema->storage->_sql_maker->{_cached_syntax};
+  $schema->storage->_sql_maker->limit_dialect ($limtype);
+
+  # chained search is necessary to exercise the recursive {where} parser
+  my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
+    columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
+    join => 'owner',  # single-rel manual prefetch
+    rows => 4,
+    '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
+    group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
+    having => \[ '?', [ $attr => 31 ] ],
+    ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
+  });
+
+  #
+  # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
+  #
+
+  # only limit, no offset, no order
+  is_same_sql_bind(
+    $rs->as_query,
+    @{$tests->{$limtype}{limit}},
+    "$limtype: Unordered limit with select/group/having",
+  ) if $tests->{$limtype}{limit};
+
+  # limit + offset, no order
+  is_same_sql_bind(
+    $rs->search({}, { offset => 3 })->as_query,
+    @{$tests->{$limtype}{limit_offset}},
+    "$limtype: Unordered limit+offset with select/group/having",
+  ) if $tests->{$limtype}{limit_offset};
+
+  # order + limit, no offset
+  $rs = $rs->search(undef, {
+    order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
+  });
+
+  is_same_sql_bind(
+    $rs->as_query,
+    @{$tests->{$limtype}{ordered_limit}},
+    "$limtype: Ordered limit with select/group/having",
+  ) if $tests->{$limtype}{ordered_limit};
+
+  # order + limit + offset
+  is_same_sql_bind(
+    $rs->search({}, { offset => 3 })->as_query,
+    @{$tests->{$limtype}{ordered_limit_offset}},
+    "$limtype: Ordered limit+offset with select/group/having",
+  ) if $tests->{$limtype}{ordered_limit_offset};
+}
+
+done_testing;