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
71 [ { sqlt_datatype => 'integer' } => 3 ],
72 [ { sqlt_datatype => 'integer' } => 1 ],
78 ordered_limit_offset => [
80 SELECT me.id, owner.id, owner.name, ? * ?, ?
83 ON owner.id = me.owner
84 WHERE source != ? AND me.title = ? AND source = ?
85 GROUP BY avg(me.id / ?)
96 [ { sqlt_datatype => 'integer' } => 3 ],
97 [ { sqlt_datatype => 'integer' } => 4 ],
100 limit_offset_prefetch => [
102 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
104 SELECT me.name, me.id
108 LEFT JOIN books books
109 ON books.owner = me.id
113 [ { sqlt_datatype => 'integer' } => 1 ],
114 [ { sqlt_datatype => 'integer' } => 3 ],
120 ordered_limit_offset => [
122 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
125 ON owner.id = me.owner
126 WHERE source != ? AND me.title = ? AND source = ?
127 GROUP BY avg(me.id / ?)
132 [ { sqlt_datatype => 'integer' } => 3 ],
133 [ { sqlt_datatype => 'integer' } => 4 ],
141 limit_offset_prefetch => [
143 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
145 SELECT SKIP ? FIRST ? me.name, me.id
148 LEFT JOIN books books
149 ON books.owner = me.id
153 [ { sqlt_datatype => 'integer' } => 1 ],
154 [ { sqlt_datatype => 'integer' } => 3 ],
160 ordered_limit_offset => [
162 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
165 ON owner.id = me.owner
166 WHERE source != ? AND me.title = ? AND source = ?
167 GROUP BY avg(me.id / ?)
172 [ { sqlt_datatype => 'integer' } => 4 ],
173 [ { sqlt_datatype => 'integer' } => 3 ],
181 limit_offset_prefetch => [
183 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
185 SELECT FIRST ? SKIP ? me.name, me.id
188 LEFT JOIN books books
189 ON books.owner = me.id
193 [ { sqlt_datatype => 'integer' } => 3 ],
194 [ { sqlt_datatype => 'integer' } => 1 ],
199 RowNumberOver => do {
200 my $unordered_sql = '(
201 SELECT me.id, owner__id, owner__name, bar, baz
203 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
205 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
208 ON owner.id = me.owner
209 WHERE source != ? AND me.title = ? AND source = ?
210 GROUP BY avg(me.id / ?)
214 WHERE rno__row__index >= ? AND rno__row__index <= ?
218 SELECT me.id, owner__id, owner__name, bar, baz
220 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
222 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
223 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
226 ON owner.id = me.owner
227 WHERE source != ? AND me.title = ? AND source = ?
228 GROUP BY avg(me.id / ?)
232 WHERE rno__row__index >= ? AND rno__row__index <= ?
236 limit => [$unordered_sql,
242 [ { sqlt_datatype => 'integer' } => 1 ],
243 [ { sqlt_datatype => 'integer' } => 4 ],
246 limit_offset => [$unordered_sql,
252 [ { sqlt_datatype => 'integer' } => 4 ],
253 [ { sqlt_datatype => 'integer' } => 7 ],
256 ordered_limit => [$ordered_sql,
263 [ { sqlt_datatype => 'integer' } => 1 ],
264 [ { sqlt_datatype => 'integer' } => 4 ],
267 ordered_limit_offset => [$ordered_sql,
274 [ { sqlt_datatype => 'integer' } => 4 ],
275 [ { sqlt_datatype => 'integer' } => 7 ],
278 limit_offset_prefetch => [
280 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
282 SELECT me.name, me.id
284 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
286 SELECT me.name, me.id FROM owners me
289 WHERE rno__row__index >= ? AND rno__row__index <= ?
291 LEFT JOIN books books
292 ON books.owner = me.id
296 [ { sqlt_datatype => 'integer' } => 2 ],
297 [ { sqlt_datatype => 'integer' } => 4 ],
304 my $limit_sql = sub {
306 SELECT me.id, owner__id, owner__name, bar, baz
308 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
311 ON owner.id = me.owner
312 WHERE source != ? AND me.title = ? AND source = ?
313 GROUP BY avg(me.id / ?)
322 limit => [ $limit_sql->(),
328 [ { sqlt_datatype => 'integer' } => 4 ],
333 SELECT me.id, owner__id, owner__name, bar, baz
335 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
337 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
340 ON owner.id = me.owner
341 WHERE source != ? AND me.title = ? AND source = ?
342 GROUP BY avg(me.id / ?)
346 WHERE rownum__index BETWEEN ? AND ?
353 [ { sqlt_datatype => 'integer' } => 4 ],
354 [ { sqlt_datatype => 'integer' } => 7 ],
357 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
364 [ { sqlt_datatype => 'integer' } => 4 ],
367 ordered_limit_offset => [
369 SELECT me.id, owner__id, owner__name, bar, baz
371 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
373 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
376 ON owner.id = me.owner
377 WHERE source != ? AND me.title = ? AND source = ?
378 GROUP BY avg(me.id / ?)
384 WHERE rownum__index >= ?
392 [ { sqlt_datatype => 'integer' } => 7 ],
393 [ { sqlt_datatype => 'integer' } => 4 ],
396 limit_offset_prefetch => [
398 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
400 SELECT me.name, me.id
402 SELECT me.name, me.id, ROWNUM rownum__index
404 SELECT me.name, me.id
407 ) me WHERE rownum__index BETWEEN ? AND ?
409 LEFT JOIN books books
410 ON books.owner = me.id
414 [ { sqlt_datatype => 'integer' } => 2 ],
415 [ { sqlt_datatype => 'integer' } => 4 ],
424 SELECT me.id, owner.id, owner.name, ? * ?, ?
427 ON owner.id = me.owner
428 WHERE source != ? AND me.title = ? AND source = ?
429 GROUP BY avg(me.id / ?)
431 FETCH FIRST 4 ROWS ONLY
442 SELECT me.id, owner__id, owner__name, bar, baz
444 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
447 ON owner.id = me.owner
448 WHERE source != ? AND me.title = ? AND source = ?
449 GROUP BY avg(me.id / ?)
452 FETCH FIRST 7 ROWS ONLY
455 FETCH FIRST 4 ROWS ONLY
466 SELECT me.id, owner.id, owner.name, ? * ?, ?
469 ON owner.id = me.owner
470 WHERE source != ? AND me.title = ? AND source = ?
471 GROUP BY avg(me.id / ?)
474 FETCH FIRST 4 ROWS ONLY
484 ordered_limit_offset => [
486 SELECT me.id, owner__id, owner__name, bar, baz
488 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
490 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
493 ON owner.id = me.owner
494 WHERE source != ? AND me.title = ? AND source = ?
495 GROUP BY avg(me.id / ?)
498 FETCH FIRST 7 ROWS ONLY
500 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
501 FETCH FIRST 4 ROWS ONLY
503 ORDER BY ORDER__BY__001, ORDER__BY__002
511 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
514 limit_offset_prefetch => [
516 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
518 SELECT me.name, me.id
520 SELECT me.name, me.id
523 FETCH FIRST 4 ROWS ONLY
526 FETCH FIRST 3 ROWS ONLY
528 LEFT JOIN books books
529 ON books.owner = me.id
539 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
542 ON owner.id = me.owner
543 WHERE source != ? AND me.title = ? AND source = ?
544 GROUP BY avg(me.id / ?)
556 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
558 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
561 ON owner.id = me.owner
562 WHERE source != ? AND me.title = ? AND source = ?
563 GROUP BY avg(me.id / ?)
578 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
581 ON owner.id = me.owner
582 WHERE source != ? AND me.title = ? AND source = ?
583 GROUP BY avg(me.id / ?)
595 ordered_limit_offset => [
597 SELECT me.id, owner__id, owner__name, bar, baz
599 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
601 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
604 ON owner.id = me.owner
605 WHERE source != ? AND me.title = ? AND source = ?
606 GROUP BY avg(me.id / ?)
610 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
612 ORDER BY ORDER__BY__001, ORDER__BY__002
620 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
623 limit_offset_prefetch => [
625 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
627 SELECT TOP 3 me.name, me.id
629 SELECT TOP 4 me.name, me.id
635 LEFT JOIN books books
636 ON books.owner = me.id
643 RowCountOrGenericSubQ => {
647 SELECT me.id, owner.id, owner.name, ? * ?, ?
650 ON owner.id = me.owner
651 WHERE source != ? AND me.title = ? AND source = ?
652 GROUP BY avg(me.id / ?)
666 SELECT me.id, owner__id, owner__name, bar, baz
668 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
671 ON owner.id = me.owner
672 WHERE source != ? AND me.title = ? AND source = ?
673 GROUP BY avg( me.id / ? )
678 FROM books rownum__emulation
679 WHERE rownum__emulation.id < me.id
688 [ { sqlt_datatype => 'integer' } => 3 ],
689 [ { sqlt_datatype => 'integer' } => 6 ],
697 SELECT me.id, owner__id, owner__name, bar, baz
699 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
702 ON owner.id = me.owner
703 WHERE source != ? AND me.title = ? AND source = ?
704 GROUP BY avg( me.id / ? )
709 FROM books rownum__emulation
710 WHERE rownum__emulation.id < me.id
719 [ { sqlt_datatype => 'integer' } => 4 ],
724 SELECT me.id, owner__id, owner__name, bar, baz
726 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
729 ON owner.id = me.owner
730 WHERE source != ? AND me.title = ? AND source = ?
731 GROUP BY avg( me.id / ? )
736 FROM books rownum__emulation
737 WHERE rownum__emulation.id < me.id
746 [ { sqlt_datatype => 'integer' } => 3 ],
747 [ { sqlt_datatype => 'integer' } => 6 ],
750 limit_offset_prefetch => [
752 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
754 SELECT me.name, me.id
756 SELECT me.name, me.id FROM owners me
760 FROM owners rownum__emulation
761 WHERE rownum__emulation.id < me.id
765 LEFT JOIN books books
766 ON books.owner = me.id
767 ORDER BY me.id, books.owner
770 [ { sqlt_datatype => 'integer' } => 1 ],
771 [ { sqlt_datatype => 'integer' } => 3 ],
777 for my $limtype (sort keys %$tests) {
779 Test::Builder->new->is_passing or exit;
781 delete $schema->storage->_sql_maker->{_cached_syntax};
782 $schema->storage->_sql_maker->limit_dialect ($limtype);
784 # chained search is necessary to exercise the recursive {where} parser
785 my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
786 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
787 join => 'owner', # single-rel manual prefetch
789 '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
790 group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
791 having => \[ '?', [ $attr => 31 ] ],
792 ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
796 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
799 # only limit, no offset, no order
802 @{$tests->{$limtype}{limit}},
803 "$limtype: Unordered limit with select/group/having",
804 ) if $tests->{$limtype}{limit};
806 # limit + offset, no order
808 $rs->search({}, { offset => 3 })->as_query,
809 @{$tests->{$limtype}{limit_offset}},
810 "$limtype: Unordered limit+offset with select/group/having",
811 ) if $tests->{$limtype}{limit_offset};
813 # order + limit, no offset
814 $rs = $rs->search(undef, {
815 order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
820 @{$tests->{$limtype}{ordered_limit}},
821 "$limtype: Ordered limit with select/group/having",
822 ) if $tests->{$limtype}{ordered_limit};
824 # order + limit + offset
826 $rs->search({}, { offset => 3 })->as_query,
827 @{$tests->{$limtype}{ordered_limit_offset}},
828 "$limtype: Ordered limit+offset with select/group/having",
829 ) if $tests->{$limtype}{ordered_limit_offset};
831 # complex prefetch on partial-fetch root with limit
832 my $pref_rs = $schema->resultset('Owners')->search({}, {
835 columns => 'name', # only the owner name, still prefetch all the books
837 ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
842 @{$tests->{$limtype}{limit_offset_prefetch}},
843 "$limtype: Prefetch with limit+offset",
844 ) if $tests->{$limtype}{limit_offset_prefetch};
846 # we can actually run the query
847 if ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ') {
848 lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limtied prefetch') }
849 "Complex limited prefetch works with supported limit $limtype"