[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / torture.t

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__001, ORDER__BY__002 ) 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__001, ? AS ORDER__BY__002
                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__001, ORDER__BY__002
              FROM (
                SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
                  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__001 DESC, ORDER__BY__002 DESC
            FETCH FIRST 4 ROWS ONLY
          ) me
        ORDER BY ORDER__BY__001, ORDER__BY__002
      )',
      [
        @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__001, ORDER__BY__002
              FROM (
                SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
                  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__001 DESC, ORDER__BY__002 DESC
          ) me
        ORDER BY ORDER__BY__001, ORDER__BY__002
      )',
      [
        @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;
Commit	Line	Data
27a7c422	1	use strict;
	2	use warnings;
	3
	4	use Test::More;
	5	use lib qw(t/lib);
	6	use DBICTest;
	7	use DBIC::SqlMakerTest;
	8
	9	my $schema = DBICTest->init_schema;
	10
	11	my $attr = {};
	12	my @where_bind = (
	13	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
	14	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
	15	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
	16	);
	17	my @select_bind = (
	18	[ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
	19	);
	20	my @group_bind = (
	21	[ $attr => 21 ],
	22	);
	23	my @having_bind = (
	24	[ $attr => 31 ],
	25	);
	26	my @order_bind = (
	27	[ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
	28	);
	29
	30	my $tests = {
4b8da207	31
27a7c422	32	LimitOffset => {
	33	ordered_limit_offset => [
	34	'(
4b8da207	35	SELECT me.id, owner.id, owner.name, ? * ?, ?
27a7c422	36	FROM books me
4b8da207	37	JOIN owners owner
4b8da207	38	ON owner.id = me.owner
27a7c422	39	WHERE source != ? AND me.title = ? AND source = ?
	40	GROUP BY avg(me.id / ?)
	41	HAVING ?
	42	ORDER BY ? / ?, ?
	43	LIMIT ?
	44	OFFSET ?
	45	)',
	46	[
	47	@select_bind,
	48	@where_bind,
	49	@group_bind,
	50	@having_bind,
	51	@order_bind,
	52	[ { sqlt_datatype => 'integer' } => 4 ],
	53	[ { sqlt_datatype => 'integer' } => 3 ],
	54	],
	55	],
	56	},
	57
	58	LimitXY => {
	59	ordered_limit_offset => [
	60	'(
4b8da207	61	SELECT me.id, owner.id, owner.name, ? * ?, ?
27a7c422	62	FROM books me
4b8da207	63	JOIN owners owner
4b8da207	64	ON owner.id = me.owner
27a7c422	65	WHERE source != ? AND me.title = ? AND source = ?
	66	GROUP BY avg(me.id / ?)
	67	HAVING ?
	68	ORDER BY ? / ?, ?
	69	LIMIT ?, ?
	70	)',
	71	[
	72	@select_bind,
	73	@where_bind,
	74	@group_bind,
	75	@having_bind,
	76	@order_bind,
	77	[ { sqlt_datatype => 'integer' } => 3 ],
	78	[ { sqlt_datatype => 'integer' } => 4 ],
	79	],
	80	],
	81	},
	82
	83	SkipFirst => {
	84	ordered_limit_offset => [
	85	'(
4b8da207	86	SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
27a7c422	87	FROM books me
4b8da207	88	JOIN owners owner
4b8da207	89	ON owner.id = me.owner
27a7c422	90	WHERE source != ? AND me.title = ? AND source = ?
	91	GROUP BY avg(me.id / ?)
	92	HAVING ?
	93	ORDER BY ? / ?, ?
	94	)',
	95	[
	96	[ { sqlt_datatype => 'integer' } => 3 ],
	97	[ { sqlt_datatype => 'integer' } => 4 ],
	98	@select_bind,
	99	@where_bind,
	100	@group_bind,
	101	@having_bind,
	102	@order_bind,
	103	],
	104	],
	105	},
	106
	107	FirstSkip => {
	108	ordered_limit_offset => [
	109	'(
4b8da207	110	SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
27a7c422	111	FROM books me
4b8da207	112	JOIN owners owner
4b8da207	113	ON owner.id = me.owner
27a7c422	114	WHERE source != ? AND me.title = ? AND source = ?
	115	GROUP BY avg(me.id / ?)
	116	HAVING ?
	117	ORDER BY ? / ?, ?
	118	)',
	119	[
	120	[ { sqlt_datatype => 'integer' } => 4 ],
	121	[ { sqlt_datatype => 'integer' } => 3 ],
	122	@select_bind,
	123	@where_bind,
	124	@group_bind,
	125	@having_bind,
	126	@order_bind,
	127	],
	128	],
	129	},
	130
	131	RowNumberOver => do {
	132	my $unordered_sql = '(
4b8da207	133	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	134	FROM (
4b8da207	135	SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
27a7c422	136	FROM (
4b8da207	137	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	138	FROM books me
4b8da207	139	JOIN owners owner
4b8da207	140	ON owner.id = me.owner
27a7c422	141	WHERE source != ? AND me.title = ? AND source = ?
	142	GROUP BY avg(me.id / ?)
	143	HAVING ?
	144	) me
	145	) me
	146	WHERE rno__row__index >= ? AND rno__row__index <= ?
	147	)';
	148
	149	my $ordered_sql = '(
4b8da207	150	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	151	FROM (
08a1eaad	152	SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
27a7c422	153	FROM (
4b8da207	154	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
08a1eaad	155	? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
27a7c422	156	FROM books me
4b8da207	157	JOIN owners owner
4b8da207	158	ON owner.id = me.owner
27a7c422	159	WHERE source != ? AND me.title = ? AND source = ?
	160	GROUP BY avg(me.id / ?)
	161	HAVING ?
	162	) me
	163	) me
	164	WHERE rno__row__index >= ? AND rno__row__index <= ?
	165	)';
	166
	167	{
	168	limit => [$unordered_sql,
	169	[
	170	@select_bind,
	171	@where_bind,
	172	@group_bind,
	173	@having_bind,
	174	[ { sqlt_datatype => 'integer' } => 1 ],
	175	[ { sqlt_datatype => 'integer' } => 4 ],
	176	],
	177	],
	178	limit_offset => [$unordered_sql,
	179	[
	180	@select_bind,
	181	@where_bind,
	182	@group_bind,
	183	@having_bind,
	184	[ { sqlt_datatype => 'integer' } => 4 ],
	185	[ { sqlt_datatype => 'integer' } => 7 ],
	186	],
	187	],
	188	ordered_limit => [$ordered_sql,
	189	[
	190	@select_bind,
	191	@order_bind,
	192	@where_bind,
	193	@group_bind,
	194	@having_bind,
	195	[ { sqlt_datatype => 'integer' } => 1 ],
	196	[ { sqlt_datatype => 'integer' } => 4 ],
	197	],
	198	],
	199	ordered_limit_offset => [$ordered_sql,
	200	[
	201	@select_bind,
	202	@order_bind,
	203	@where_bind,
	204	@group_bind,
	205	@having_bind,
	206	[ { sqlt_datatype => 'integer' } => 4 ],
	207	[ { sqlt_datatype => 'integer' } => 7 ],
	208	],
	209	],
	210	};
	211	},
	212
	213	RowNum => do {
	214	my $limit_sql = sub {
	215	sprintf '(
4b8da207	216	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	217	FROM (
4b8da207	218	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	219	FROM books me
4b8da207	220	JOIN owners owner
4b8da207	221	ON owner.id = me.owner
27a7c422	222	WHERE source != ? AND me.title = ? AND source = ?
	223	GROUP BY avg(me.id / ?)
	224	HAVING ?
	225	%s
	226	) me
	227	WHERE ROWNUM <= ?
	228	)', $_[0] \|\| '';
	229	};
	230
	231	{
	232	limit => [ $limit_sql->(),
	233	[
	234	@select_bind,
	235	@where_bind,
	236	@group_bind,
	237	@having_bind,
	238	[ { sqlt_datatype => 'integer' } => 4 ],
	239	],
	240	],
	241	limit_offset => [
	242	'(
4b8da207	243	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	244	FROM (
4b8da207	245	SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
27a7c422	246	FROM (
4b8da207	247	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	248	FROM books me
4b8da207	249	JOIN owners owner
4b8da207	250	ON owner.id = me.owner
27a7c422	251	WHERE source != ? AND me.title = ? AND source = ?
	252	GROUP BY avg(me.id / ?)
	253	HAVING ?
	254	) me
	255	) me
	256	WHERE rownum__index BETWEEN ? AND ?
	257	)',
	258	[
	259	@select_bind,
	260	@where_bind,
	261	@group_bind,
	262	@having_bind,
	263	[ { sqlt_datatype => 'integer' } => 4 ],
	264	[ { sqlt_datatype => 'integer' } => 7 ],
	265	],
	266	],
	267	ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
	268	[
	269	@select_bind,
	270	@where_bind,
	271	@group_bind,
	272	@having_bind,
	273	@order_bind,
	274	[ { sqlt_datatype => 'integer' } => 4 ],
	275	],
	276	],
	277	ordered_limit_offset => [
	278	'(
4b8da207	279	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	280	FROM (
4b8da207	281	SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
27a7c422	282	FROM (
4b8da207	283	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	284	FROM books me
4b8da207	285	JOIN owners owner
4b8da207	286	ON owner.id = me.owner
27a7c422	287	WHERE source != ? AND me.title = ? AND source = ?
	288	GROUP BY avg(me.id / ?)
	289	HAVING ?
	290	ORDER BY ? / ?, ?
	291	) me
	292	WHERE ROWNUM <= ?
	293	) me
	294	WHERE rownum__index >= ?
	295	)',
	296	[
	297	@select_bind,
	298	@where_bind,
	299	@group_bind,
	300	@having_bind,
	301	@order_bind,
	302	[ { sqlt_datatype => 'integer' } => 7 ],
	303	[ { sqlt_datatype => 'integer' } => 4 ],
	304	],
	305	],
	306	};
	307	},
	308
27a7c422	309	FetchFirst => {
	310	limit => [
	311	'(
4b8da207	312	SELECT me.id, owner.id, owner.name, ? * ?, ?
27a7c422	313	FROM books me
4b8da207	314	JOIN owners owner
4b8da207	315	ON owner.id = me.owner
27a7c422	316	WHERE source != ? AND me.title = ? AND source = ?
	317	GROUP BY avg(me.id / ?)
	318	HAVING ?
	319	FETCH FIRST 4 ROWS ONLY
	320	)',
	321	[
	322	@select_bind,
	323	@where_bind,
	324	@group_bind,
	325	@having_bind,
	326	],
	327	],
	328	limit_offset => [
	329	'(
4b8da207	330	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	331	FROM (
4b8da207	332	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	333	FROM books me
4b8da207	334	JOIN owners owner
4b8da207	335	ON owner.id = me.owner
27a7c422	336	WHERE source != ? AND me.title = ? AND source = ?
	337	GROUP BY avg(me.id / ?)
	338	HAVING ?
	339	ORDER BY me.id
	340	FETCH FIRST 7 ROWS ONLY
	341	) me
	342	ORDER BY me.id DESC
	343	FETCH FIRST 4 ROWS ONLY
	344	)',
	345	[
	346	@select_bind,
	347	@where_bind,
	348	@group_bind,
	349	@having_bind,
	350	],
	351	],
	352	ordered_limit => [
	353	'(
4b8da207	354	SELECT me.id, owner.id, owner.name, ? * ?, ?
27a7c422	355	FROM books me
4b8da207	356	JOIN owners owner
4b8da207	357	ON owner.id = me.owner
27a7c422	358	WHERE source != ? AND me.title = ? AND source = ?
	359	GROUP BY avg(me.id / ?)
	360	HAVING ?
	361	ORDER BY ? / ?, ?
	362	FETCH FIRST 4 ROWS ONLY
	363	)',
	364	[
	365	@select_bind,
	366	@where_bind,
	367	@group_bind,
	368	@having_bind,
	369	@order_bind,
	370	],
	371	],
	372	ordered_limit_offset => [
	373	'(
4b8da207	374	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	375	FROM (
08a1eaad	376	SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
27a7c422	377	FROM (
08a1eaad	378	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
27a7c422	379	FROM books me
4b8da207	380	JOIN owners owner
4b8da207	381	ON owner.id = me.owner
27a7c422	382	WHERE source != ? AND me.title = ? AND source = ?
	383	GROUP BY avg(me.id / ?)
	384	HAVING ?
	385	ORDER BY ? / ?, ?
	386	FETCH FIRST 7 ROWS ONLY
	387	) me
08a1eaad	388	ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
27a7c422	389	FETCH FIRST 4 ROWS ONLY
27a7c422	390	) me
08a1eaad	391	ORDER BY ORDER__BY__001, ORDER__BY__002
27a7c422	392	)',
	393	[
	394	@select_bind,
	395	@order_bind,
	396	@where_bind,
	397	@group_bind,
	398	@having_bind,
	399	(map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
	400	],
	401	],
	402	},
	403
	404	Top => {
	405	limit => [
	406	'(
4b8da207	407	SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
27a7c422	408	FROM books me
4b8da207	409	JOIN owners owner
4b8da207	410	ON owner.id = me.owner
27a7c422	411	WHERE source != ? AND me.title = ? AND source = ?
	412	GROUP BY avg(me.id / ?)
	413	HAVING ?
	414	)',
	415	[
	416	@select_bind,
	417	@where_bind,
	418	@group_bind,
	419	@having_bind,
	420	],
	421	],
	422	limit_offset => [
	423	'(
4b8da207	424	SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
27a7c422	425	FROM (
4b8da207	426	SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	427	FROM books me
4b8da207	428	JOIN owners owner
4b8da207	429	ON owner.id = me.owner
27a7c422	430	WHERE source != ? AND me.title = ? AND source = ?
	431	GROUP BY avg(me.id / ?)
	432	HAVING ?
	433	ORDER BY me.id
	434	) me
	435	ORDER BY me.id DESC
	436	)',
	437	[
	438	@select_bind,
	439	@where_bind,
	440	@group_bind,
	441	@having_bind,
	442	],
	443	],
	444	ordered_limit => [
	445	'(
4b8da207	446	SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
27a7c422	447	FROM books me
4b8da207	448	JOIN owners owner
4b8da207	449	ON owner.id = me.owner
27a7c422	450	WHERE source != ? AND me.title = ? AND source = ?
	451	GROUP BY avg(me.id / ?)
	452	HAVING ?
	453	ORDER BY ? / ?, ?
	454	)',
	455	[
	456	@select_bind,
	457	@where_bind,
	458	@group_bind,
	459	@having_bind,
	460	@order_bind,
	461	],
	462	],
	463	ordered_limit_offset => [
	464	'(
4b8da207	465	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	466	FROM (
08a1eaad	467	SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
27a7c422	468	FROM (
08a1eaad	469	SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
27a7c422	470	FROM books me
4b8da207	471	JOIN owners owner
4b8da207	472	ON owner.id = me.owner
27a7c422	473	WHERE source != ? AND me.title = ? AND source = ?
	474	GROUP BY avg(me.id / ?)
	475	HAVING ?
	476	ORDER BY ? / ?, ?
	477	) me
08a1eaad	478	ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
27a7c422	479	) me
08a1eaad	480	ORDER BY ORDER__BY__001, ORDER__BY__002
27a7c422	481	)',
	482	[
	483	@select_bind,
	484	@order_bind,
	485	@where_bind,
	486	@group_bind,
	487	@having_bind,
	488	(map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
	489	],
	490	],
	491	},
	492
	493	RowCountOrGenericSubQ => {
	494	limit => [
	495	'(
	496	SET ROWCOUNT 4
4b8da207	497	SELECT me.id, owner.id, owner.name, ? * ?, ?
27a7c422	498	FROM books me
4b8da207	499	JOIN owners owner
4b8da207	500	ON owner.id = me.owner
27a7c422	501	WHERE source != ? AND me.title = ? AND source = ?
	502	GROUP BY avg(me.id / ?)
	503	HAVING ?
	504	ORDER BY me.id
	505	SET ROWCOUNT 0
	506	)',
	507	[
	508	@select_bind,
	509	@where_bind,
	510	@group_bind,
	511	@having_bind,
	512	],
	513	],
	514	limit_offset => [
	515	'(
4b8da207	516	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	517	FROM (
4b8da207	518	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	519	FROM books me
4b8da207	520	JOIN owners owner
4b8da207	521	ON owner.id = me.owner
27a7c422	522	WHERE source != ? AND me.title = ? AND source = ?
	523	GROUP BY avg( me.id / ? )
	524	HAVING ?
	525	) me
	526	WHERE (
	527	SELECT COUNT( * )
	528	FROM books rownum__emulation
	529	WHERE rownum__emulation.id < me.id
	530	) BETWEEN ? AND ?
	531	ORDER BY me.id
	532	)',
	533	[
	534	@select_bind,
	535	@where_bind,
	536	@group_bind,
	537	@having_bind,
	538	[ { sqlt_datatype => 'integer' } => 3 ],
	539	[ { sqlt_datatype => 'integer' } => 6 ],
	540	],
	541	],
	542	},
	543
	544	GenericSubQ => {
	545	limit => [
	546	'(
4b8da207	547	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	548	FROM (
4b8da207	549	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	550	FROM books me
4b8da207	551	JOIN owners owner
4b8da207	552	ON owner.id = me.owner
27a7c422	553	WHERE source != ? AND me.title = ? AND source = ?
	554	GROUP BY avg( me.id / ? )
	555	HAVING ?
	556	) me
	557	WHERE (
	558	SELECT COUNT( * )
	559	FROM books rownum__emulation
	560	WHERE rownum__emulation.id < me.id
	561	) < ?
	562	ORDER BY me.id
	563	)',
	564	[
	565	@select_bind,
	566	@where_bind,
	567	@group_bind,
	568	@having_bind,
	569	[ { sqlt_datatype => 'integer' } => 4 ],
	570	],
	571	],
	572	limit_offset => [
	573	'(
4b8da207	574	SELECT me.id, owner__id, owner__name, bar, baz
27a7c422	575	FROM (
4b8da207	576	SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
27a7c422	577	FROM books me
4b8da207	578	JOIN owners owner
4b8da207	579	ON owner.id = me.owner
27a7c422	580	WHERE source != ? AND me.title = ? AND source = ?
	581	GROUP BY avg( me.id / ? )
	582	HAVING ?
	583	) me
	584	WHERE (
	585	SELECT COUNT( * )
	586	FROM books rownum__emulation
	587	WHERE rownum__emulation.id < me.id
	588	) BETWEEN ? AND ?
	589	ORDER BY me.id
	590	)',
	591	[
	592	@select_bind,
	593	@where_bind,
	594	@group_bind,
	595	@having_bind,
	596	[ { sqlt_datatype => 'integer' } => 3 ],
	597	[ { sqlt_datatype => 'integer' } => 6 ],
	598	],
	599	],
	600	}
	601	};
	602
	603	for my $limtype (sort keys %$tests) {
	604
4b8da207	605	Test::Builder->new->is_passing or exit;
4b8da207	606
27a7c422	607	delete $schema->storage->_sql_maker->{_cached_syntax};
	608	$schema->storage->_sql_maker->limit_dialect ($limtype);
	609
	610	# chained search is necessary to exercise the recursive {where} parser
	611	my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
4b8da207	612	columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
4b8da207	613	join => 'owner', # single-rel manual prefetch
27a7c422	614	rows => 4,
	615	'+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
	616	group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
	617	having => \[ '?', [ $attr => 31 ] ],
	618	($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
	619	});
	620
	621	#
	622	# not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
	623	#
	624
	625	# only limit, no offset, no order
	626	is_same_sql_bind(
	627	$rs->as_query,
	628	@{$tests->{$limtype}{limit}},
	629	"$limtype: Unordered limit with select/group/having",
	630	) if $tests->{$limtype}{limit};
	631
	632	# limit + offset, no order
	633	is_same_sql_bind(
	634	$rs->search({}, { offset => 3 })->as_query,
	635	@{$tests->{$limtype}{limit_offset}},
	636	"$limtype: Unordered limit+offset with select/group/having",
	637	) if $tests->{$limtype}{limit_offset};
	638
	639	# order + limit, no offset
	640	$rs = $rs->search(undef, {
	641	order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
	642	});
	643
	644	is_same_sql_bind(
	645	$rs->as_query,
	646	@{$tests->{$limtype}{ordered_limit}},
	647	"$limtype: Ordered limit with select/group/having",
	648	) if $tests->{$limtype}{ordered_limit};
	649
	650	# order + limit + offset
	651	is_same_sql_bind(
	652	$rs->search({}, { offset => 3 })->as_query,
	653	@{$tests->{$limtype}{ordered_limit_offset}},
	654	"$limtype: Ordered limit+offset with select/group/having",
	655	) if $tests->{$limtype}{ordered_limit_offset};
	656	}
	657
	658	done_testing;