[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, ? * ?, ?
          FROM books me
        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, ? * ?, ?
          FROM books me
        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, ? * ?, ?
          FROM books me
        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, ? * ?, ?
          FROM books me
        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, bar, baz
        FROM (
          SELECT me.id, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
            FROM (
              SELECT me.id, ? * ? AS bar, ? AS baz
                FROM books me
              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, bar, baz
        FROM (
          SELECT me.id, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__1, ORDER__BY__2 ) AS rno__row__index
            FROM (
              SELECT me.id, ? * ? AS bar, ? AS baz,
                     ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
                FROM books me
              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, bar, baz
          FROM (
            SELECT me.id, ? * ? AS bar, ? AS baz
              FROM books me
            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, bar, baz
            FROM (
              SELECT me.id, bar, baz, ROWNUM rownum__index
                FROM (
                  SELECT me.id, ? * ? AS bar, ? AS baz
                    FROM books me
                  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, bar, baz
            FROM (
              SELECT me.id, bar, baz, ROWNUM rownum__index
                FROM (
                  SELECT me.id, ? * ? AS bar, ? AS baz
                    FROM books me
                  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, ? * ?, ?
          FROM books me
        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, bar, baz
          FROM (
            SELECT me.id, ? * ? AS bar, ? AS baz
              FROM books me
            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, ? * ?, ?
          FROM books me
        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, bar, baz
          FROM (
            SELECT me.id, bar, baz, ORDER__BY__1, ORDER__BY__2
              FROM (
                SELECT me.id, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
                  FROM books me
                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, ? * ?, ?
          FROM books me
        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, bar, baz
          FROM (
            SELECT TOP 7 me.id, ? * ? AS bar, ? AS baz
              FROM books me
            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, ? * ?, ?
          FROM books me
        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, bar, baz
          FROM (
            SELECT TOP 4 me.id, bar, baz, ORDER__BY__1, ORDER__BY__2
              FROM (
                SELECT TOP 7 me.id, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__1, ? AS ORDER__BY__2
                  FROM books me
                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, ? * ?, ?
          FROM books me
        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, bar, baz
          FROM (
            SELECT me.id, ? * ? AS bar, ? AS baz
              FROM books me
            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, bar, baz
          FROM (
            SELECT me.id, ? * ? AS bar, ? AS baz
              FROM books me
            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, bar, baz
          FROM (
            SELECT me.id, ? * ? AS bar, ? AS baz
              FROM books me
            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) {

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