8 use DBIC::SqlMakerTest;
10 my $schema = DBICTest->init_schema;
11 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
15 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
16 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
17 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
20 [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
29 [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
35 ordered_limit_offset => [
37 SELECT me.id, owner.id, owner.name, ? * ?, ?
40 ON owner.id = me.owner
41 WHERE source != ? AND me.title = ? AND source = ?
42 GROUP BY avg(me.id / ?)
54 [ { sqlt_datatype => 'integer' } => 4 ],
55 [ { sqlt_datatype => 'integer' } => 3 ],
58 limit_offset_prefetch => [
60 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
67 ON books.owner = me.id
70 [ { sqlt_datatype => 'integer' } => 3 ],
71 [ { sqlt_datatype => 'integer' } => 1 ],
77 ordered_limit_offset => [
79 SELECT me.id, owner.id, owner.name, ? * ?, ?
82 ON owner.id = me.owner
83 WHERE source != ? AND me.title = ? AND source = ?
84 GROUP BY avg(me.id / ?)
95 [ { sqlt_datatype => 'integer' } => 3 ],
96 [ { sqlt_datatype => 'integer' } => 4 ],
99 limit_offset_prefetch => [
101 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
103 SELECT me.name, me.id
107 LEFT JOIN books books
108 ON books.owner = me.id
111 [ { sqlt_datatype => 'integer' } => 1 ],
112 [ { sqlt_datatype => 'integer' } => 3 ],
118 ordered_limit_offset => [
120 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
123 ON owner.id = me.owner
124 WHERE source != ? AND me.title = ? AND source = ?
125 GROUP BY avg(me.id / ?)
130 [ { sqlt_datatype => 'integer' } => 3 ],
131 [ { sqlt_datatype => 'integer' } => 4 ],
139 limit_offset_prefetch => [
141 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
143 SELECT SKIP ? FIRST ? me.name, me.id
146 LEFT JOIN books books
147 ON books.owner = me.id
150 [ { sqlt_datatype => 'integer' } => 1 ],
151 [ { sqlt_datatype => 'integer' } => 3 ],
157 ordered_limit_offset => [
159 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
162 ON owner.id = me.owner
163 WHERE source != ? AND me.title = ? AND source = ?
164 GROUP BY avg(me.id / ?)
169 [ { sqlt_datatype => 'integer' } => 4 ],
170 [ { sqlt_datatype => 'integer' } => 3 ],
178 limit_offset_prefetch => [
180 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
182 SELECT FIRST ? SKIP ? me.name, me.id
185 LEFT JOIN books books
186 ON books.owner = me.id
189 [ { sqlt_datatype => 'integer' } => 3 ],
190 [ { sqlt_datatype => 'integer' } => 1 ],
195 RowNumberOver => do {
196 my $unordered_sql = '(
197 SELECT me.id, owner__id, owner__name, bar, baz
199 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
201 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
204 ON owner.id = me.owner
205 WHERE source != ? AND me.title = ? AND source = ?
206 GROUP BY avg(me.id / ?)
210 WHERE rno__row__index >= ? AND rno__row__index <= ?
214 SELECT me.id, owner__id, owner__name, bar, baz
216 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
218 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
219 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
222 ON owner.id = me.owner
223 WHERE source != ? AND me.title = ? AND source = ?
224 GROUP BY avg(me.id / ?)
228 WHERE rno__row__index >= ? AND rno__row__index <= ?
232 limit => [$unordered_sql,
238 [ { sqlt_datatype => 'integer' } => 1 ],
239 [ { sqlt_datatype => 'integer' } => 4 ],
242 limit_offset => [$unordered_sql,
248 [ { sqlt_datatype => 'integer' } => 4 ],
249 [ { sqlt_datatype => 'integer' } => 7 ],
252 ordered_limit => [$ordered_sql,
259 [ { sqlt_datatype => 'integer' } => 1 ],
260 [ { sqlt_datatype => 'integer' } => 4 ],
263 ordered_limit_offset => [$ordered_sql,
270 [ { sqlt_datatype => 'integer' } => 4 ],
271 [ { sqlt_datatype => 'integer' } => 7 ],
274 limit_offset_prefetch => [
276 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
278 SELECT me.name, me.id
280 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
282 SELECT me.name, me.id FROM owners me
285 WHERE rno__row__index >= ? AND rno__row__index <= ?
287 LEFT JOIN books books
288 ON books.owner = me.id
291 [ { sqlt_datatype => 'integer' } => 2 ],
292 [ { sqlt_datatype => 'integer' } => 4 ],
299 my $limit_sql = sub {
301 SELECT me.id, owner__id, owner__name, bar, baz
303 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
306 ON owner.id = me.owner
307 WHERE source != ? AND me.title = ? AND source = ?
308 GROUP BY avg(me.id / ?)
317 limit => [ $limit_sql->(),
323 [ { sqlt_datatype => 'integer' } => 4 ],
328 SELECT me.id, owner__id, owner__name, bar, baz
330 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
332 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
335 ON owner.id = me.owner
336 WHERE source != ? AND me.title = ? AND source = ?
337 GROUP BY avg(me.id / ?)
341 WHERE rownum__index BETWEEN ? AND ?
348 [ { sqlt_datatype => 'integer' } => 4 ],
349 [ { sqlt_datatype => 'integer' } => 7 ],
352 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
359 [ { sqlt_datatype => 'integer' } => 4 ],
362 ordered_limit_offset => [
364 SELECT me.id, owner__id, owner__name, bar, baz
366 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
368 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
371 ON owner.id = me.owner
372 WHERE source != ? AND me.title = ? AND source = ?
373 GROUP BY avg(me.id / ?)
379 WHERE rownum__index >= ?
387 [ { sqlt_datatype => 'integer' } => 7 ],
388 [ { sqlt_datatype => 'integer' } => 4 ],
391 limit_offset_prefetch => [
393 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
395 SELECT me.name, me.id
397 SELECT me.name, me.id, ROWNUM rownum__index
399 SELECT me.name, me.id
402 ) me WHERE rownum__index BETWEEN ? AND ?
404 LEFT JOIN books books
405 ON books.owner = me.id
408 [ { sqlt_datatype => 'integer' } => 2 ],
409 [ { sqlt_datatype => 'integer' } => 4 ],
418 SELECT me.id, owner.id, owner.name, ? * ?, ?
421 ON owner.id = me.owner
422 WHERE source != ? AND me.title = ? AND source = ?
423 GROUP BY avg(me.id / ?)
425 FETCH FIRST 4 ROWS ONLY
436 SELECT me.id, owner__id, owner__name, bar, baz
438 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
441 ON owner.id = me.owner
442 WHERE source != ? AND me.title = ? AND source = ?
443 GROUP BY avg(me.id / ?)
446 FETCH FIRST 7 ROWS ONLY
449 FETCH FIRST 4 ROWS ONLY
460 SELECT me.id, owner.id, owner.name, ? * ?, ?
463 ON owner.id = me.owner
464 WHERE source != ? AND me.title = ? AND source = ?
465 GROUP BY avg(me.id / ?)
468 FETCH FIRST 4 ROWS ONLY
478 ordered_limit_offset => [
480 SELECT me.id, owner__id, owner__name, bar, baz
482 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
484 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
487 ON owner.id = me.owner
488 WHERE source != ? AND me.title = ? AND source = ?
489 GROUP BY avg(me.id / ?)
492 FETCH FIRST 7 ROWS ONLY
494 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
495 FETCH FIRST 4 ROWS ONLY
497 ORDER BY ORDER__BY__001, ORDER__BY__002
505 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
508 limit_offset_prefetch => [
510 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
512 SELECT me.name, me.id
514 SELECT me.name, me.id
517 FETCH FIRST 4 ROWS ONLY
520 FETCH FIRST 3 ROWS ONLY
522 LEFT JOIN books books
523 ON books.owner = me.id
532 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
535 ON owner.id = me.owner
536 WHERE source != ? AND me.title = ? AND source = ?
537 GROUP BY avg(me.id / ?)
549 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
551 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
554 ON owner.id = me.owner
555 WHERE source != ? AND me.title = ? AND source = ?
556 GROUP BY avg(me.id / ?)
571 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
574 ON owner.id = me.owner
575 WHERE source != ? AND me.title = ? AND source = ?
576 GROUP BY avg(me.id / ?)
588 ordered_limit_offset => [
590 SELECT me.id, owner__id, owner__name, bar, baz
592 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
594 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
597 ON owner.id = me.owner
598 WHERE source != ? AND me.title = ? AND source = ?
599 GROUP BY avg(me.id / ?)
603 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
605 ORDER BY ORDER__BY__001, ORDER__BY__002
613 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
616 limit_offset_prefetch => [
618 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
620 SELECT TOP 3 me.name, me.id
622 SELECT TOP 4 me.name, me.id
628 LEFT JOIN books books
629 ON books.owner = me.id
635 RowCountOrGenericSubQ => {
639 SELECT me.id, owner.id, owner.name, ? * ?, ?
642 ON owner.id = me.owner
643 WHERE source != ? AND me.title = ? AND source = ?
644 GROUP BY avg(me.id / ?)
658 SELECT me.id, owner__id, owner__name, bar, baz
660 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
663 ON owner.id = me.owner
664 WHERE source != ? AND me.title = ? AND source = ?
665 GROUP BY avg( me.id / ? )
670 FROM books rownum__emulation
671 WHERE rownum__emulation.id < me.id
680 [ { sqlt_datatype => 'integer' } => 3 ],
681 [ { sqlt_datatype => 'integer' } => 6 ],
689 SELECT me.id, owner__id, owner__name, bar, baz
691 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
694 ON owner.id = me.owner
695 WHERE source != ? AND me.title = ? AND source = ?
696 GROUP BY avg( me.id / ? )
701 FROM books rownum__emulation
702 WHERE rownum__emulation.id < me.id
711 [ { sqlt_datatype => 'integer' } => 4 ],
716 SELECT me.id, owner__id, owner__name, bar, baz
718 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
721 ON owner.id = me.owner
722 WHERE source != ? AND me.title = ? AND source = ?
723 GROUP BY avg( me.id / ? )
728 FROM books rownum__emulation
729 WHERE rownum__emulation.id < me.id
738 [ { sqlt_datatype => 'integer' } => 3 ],
739 [ { sqlt_datatype => 'integer' } => 6 ],
742 limit_offset_prefetch => [
744 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
746 SELECT me.name, me.id
748 SELECT me.name, me.id FROM owners me
752 FROM owners rownum__emulation
753 WHERE rownum__emulation.id < me.id
757 LEFT JOIN books books
758 ON books.owner = me.id
762 [ { sqlt_datatype => 'integer' } => 1 ],
763 [ { sqlt_datatype => 'integer' } => 3 ],
769 for my $limtype (sort keys %$tests) {
771 Test::Builder->new->is_passing or exit;
773 delete $schema->storage->_sql_maker->{_cached_syntax};
774 $schema->storage->_sql_maker->limit_dialect ($limtype);
776 # chained search is necessary to exercise the recursive {where} parser
777 my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
778 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
779 join => 'owner', # single-rel manual prefetch
781 '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
782 group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
783 having => \[ '?', [ $attr => 31 ] ],
784 ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
788 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
791 # only limit, no offset, no order
794 @{$tests->{$limtype}{limit}},
795 "$limtype: Unordered limit with select/group/having",
796 ) if $tests->{$limtype}{limit};
798 # limit + offset, no order
800 $rs->search({}, { offset => 3 })->as_query,
801 @{$tests->{$limtype}{limit_offset}},
802 "$limtype: Unordered limit+offset with select/group/having",
803 ) if $tests->{$limtype}{limit_offset};
805 # order + limit, no offset
806 $rs = $rs->search(undef, {
807 order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
812 @{$tests->{$limtype}{ordered_limit}},
813 "$limtype: Ordered limit with select/group/having",
814 ) if $tests->{$limtype}{ordered_limit};
816 # order + limit + offset
818 $rs->search({}, { offset => 3 })->as_query,
819 @{$tests->{$limtype}{ordered_limit_offset}},
820 "$limtype: Ordered limit+offset with select/group/having",
821 ) if $tests->{$limtype}{ordered_limit_offset};
823 # complex prefetch on partial-fetch root with limit
824 my $pref_rs = $schema->resultset('Owners')->search({}, {
827 columns => 'name', # only the owner name, still prefetch all the books
829 ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
834 @{$tests->{$limtype}{limit_offset_prefetch}},
835 "$limtype: Prefetch with limit+offset",
836 ) if $tests->{$limtype}{limit_offset_prefetch};
838 # we can actually run the query
839 if ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ') {
840 lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limtied prefetch') }
841 "Complex limited prefetch works with supported limit $limtype"