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 ],
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 (me.id / ?), owner.id
51 [ { sqlt_datatype => 'integer' } => 4 ],
56 SELECT me.id, owner.id, owner.name, ? * ?, ?
59 ON owner.id = me.owner
60 WHERE source != ? AND me.title = ? AND source = ?
61 GROUP BY (me.id / ?), owner.id
71 [ { sqlt_datatype => 'integer' } => 4 ],
72 [ { sqlt_datatype => 'integer' } => 3 ],
77 SELECT me.id, owner.id, owner.name, ? * ?, ?
80 ON owner.id = me.owner
81 WHERE source != ? AND me.title = ? AND source = ?
82 GROUP BY (me.id / ?), owner.id
93 [ { sqlt_datatype => 'integer' } => 4 ],
96 ordered_limit_offset => [
98 SELECT me.id, owner.id, owner.name, ? * ?, ?
101 ON owner.id = me.owner
102 WHERE source != ? AND me.title = ? AND source = ?
103 GROUP BY (me.id / ?), owner.id
115 [ { sqlt_datatype => 'integer' } => 4 ],
116 [ { sqlt_datatype => 'integer' } => 3 ],
119 limit_offset_prefetch => [
121 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
123 SELECT me.name, me.id
127 LEFT JOIN books books
128 ON books.owner = me.id
131 [ { sqlt_datatype => 'integer' } => 3 ],
132 [ { sqlt_datatype => 'integer' } => 1 ],
138 ordered_limit_offset => [
140 SELECT me.id, owner.id, owner.name, ? * ?, ?
143 ON owner.id = me.owner
144 WHERE source != ? AND me.title = ? AND source = ?
145 GROUP BY (me.id / ?), owner.id
156 [ { sqlt_datatype => 'integer' } => 3 ],
157 [ { sqlt_datatype => 'integer' } => 4 ],
160 limit_offset_prefetch => [
162 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
164 SELECT me.name, me.id
168 LEFT JOIN books books
169 ON books.owner = me.id
172 [ { sqlt_datatype => 'integer' } => 1 ],
173 [ { sqlt_datatype => 'integer' } => 3 ],
179 ordered_limit_offset => [
181 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
184 ON owner.id = me.owner
185 WHERE source != ? AND me.title = ? AND source = ?
186 GROUP BY (me.id / ?), owner.id
191 [ { sqlt_datatype => 'integer' } => 3 ],
192 [ { sqlt_datatype => 'integer' } => 4 ],
200 limit_offset_prefetch => [
202 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
204 SELECT SKIP ? FIRST ? me.name, me.id
207 LEFT JOIN books books
208 ON books.owner = me.id
211 [ { sqlt_datatype => 'integer' } => 1 ],
212 [ { sqlt_datatype => 'integer' } => 3 ],
218 ordered_limit_offset => [
220 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
223 ON owner.id = me.owner
224 WHERE source != ? AND me.title = ? AND source = ?
225 GROUP BY (me.id / ?), owner.id
230 [ { sqlt_datatype => 'integer' } => 4 ],
231 [ { sqlt_datatype => 'integer' } => 3 ],
239 limit_offset_prefetch => [
241 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
243 SELECT FIRST ? SKIP ? me.name, me.id
246 LEFT JOIN books books
247 ON books.owner = me.id
250 [ { sqlt_datatype => 'integer' } => 3 ],
251 [ { sqlt_datatype => 'integer' } => 1 ],
256 RowNumberOver => do {
257 my $unordered_sql = '(
258 SELECT me.id, owner__id, owner__name, bar, baz
260 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
262 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
265 ON owner.id = me.owner
266 WHERE source != ? AND me.title = ? AND source = ?
267 GROUP BY (me.id / ?), owner.id
271 WHERE rno__row__index >= ? AND rno__row__index <= ?
275 SELECT me.id, owner__id, owner__name, bar, baz
277 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
279 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
280 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
283 ON owner.id = me.owner
284 WHERE source != ? AND me.title = ? AND source = ?
285 GROUP BY (me.id / ?), owner.id
289 WHERE rno__row__index >= ? AND rno__row__index <= ?
293 limit => [$unordered_sql,
299 [ { sqlt_datatype => 'integer' } => 1 ],
300 [ { sqlt_datatype => 'integer' } => 4 ],
303 limit_offset => [$unordered_sql,
309 [ { sqlt_datatype => 'integer' } => 4 ],
310 [ { sqlt_datatype => 'integer' } => 7 ],
313 ordered_limit => [$ordered_sql,
320 [ { sqlt_datatype => 'integer' } => 1 ],
321 [ { sqlt_datatype => 'integer' } => 4 ],
324 ordered_limit_offset => [$ordered_sql,
331 [ { sqlt_datatype => 'integer' } => 4 ],
332 [ { sqlt_datatype => 'integer' } => 7 ],
335 limit_offset_prefetch => [
337 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
339 SELECT me.name, me.id
341 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
343 SELECT me.name, me.id FROM owners me
346 WHERE rno__row__index >= ? AND rno__row__index <= ?
348 LEFT JOIN books books
349 ON books.owner = me.id
352 [ { sqlt_datatype => 'integer' } => 2 ],
353 [ { sqlt_datatype => 'integer' } => 4 ],
360 my $limit_sql = sub {
362 SELECT me.id, owner__id, owner__name, bar, baz
364 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
367 ON owner.id = me.owner
368 WHERE source != ? AND me.title = ? AND source = ?
369 GROUP BY (me.id / ?), owner.id
378 limit => [ $limit_sql->(),
384 [ { sqlt_datatype => 'integer' } => 4 ],
389 SELECT me.id, owner__id, owner__name, bar, baz
391 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
393 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
396 ON owner.id = me.owner
397 WHERE source != ? AND me.title = ? AND source = ?
398 GROUP BY (me.id / ?), owner.id
402 WHERE rownum__index BETWEEN ? AND ?
409 [ { sqlt_datatype => 'integer' } => 4 ],
410 [ { sqlt_datatype => 'integer' } => 7 ],
413 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
420 [ { sqlt_datatype => 'integer' } => 4 ],
423 ordered_limit_offset => [
425 SELECT me.id, owner__id, owner__name, bar, baz
427 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
429 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
432 ON owner.id = me.owner
433 WHERE source != ? AND me.title = ? AND source = ?
434 GROUP BY (me.id / ?), owner.id
440 WHERE rownum__index >= ?
448 [ { sqlt_datatype => 'integer' } => 7 ],
449 [ { sqlt_datatype => 'integer' } => 4 ],
452 limit_offset_prefetch => [
454 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
456 SELECT me.name, me.id
458 SELECT me.name, me.id, ROWNUM rownum__index
460 SELECT me.name, me.id
463 ) me WHERE rownum__index BETWEEN ? AND ?
465 LEFT JOIN books books
466 ON books.owner = me.id
469 [ { sqlt_datatype => 'integer' } => 2 ],
470 [ { sqlt_datatype => 'integer' } => 4 ],
479 SELECT me.id, owner.id, owner.name, ? * ?, ?
482 ON owner.id = me.owner
483 WHERE source != ? AND me.title = ? AND source = ?
484 GROUP BY (me.id / ?), owner.id
486 FETCH FIRST 4 ROWS ONLY
497 SELECT me.id, owner__id, owner__name, bar, baz
499 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
502 ON owner.id = me.owner
503 WHERE source != ? AND me.title = ? AND source = ?
504 GROUP BY (me.id / ?), owner.id
507 FETCH FIRST 7 ROWS ONLY
510 FETCH FIRST 4 ROWS ONLY
521 SELECT me.id, owner.id, owner.name, ? * ?, ?
524 ON owner.id = me.owner
525 WHERE source != ? AND me.title = ? AND source = ?
526 GROUP BY (me.id / ?), owner.id
529 FETCH FIRST 4 ROWS ONLY
539 ordered_limit_offset => [
541 SELECT me.id, owner__id, owner__name, bar, baz
543 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
545 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
548 ON owner.id = me.owner
549 WHERE source != ? AND me.title = ? AND source = ?
550 GROUP BY (me.id / ?), owner.id
553 FETCH FIRST 7 ROWS ONLY
555 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
556 FETCH FIRST 4 ROWS ONLY
558 ORDER BY ORDER__BY__001, ORDER__BY__002
566 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
569 limit_offset_prefetch => [
571 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
573 SELECT me.name, me.id
575 SELECT me.name, me.id
578 FETCH FIRST 4 ROWS ONLY
581 FETCH FIRST 3 ROWS ONLY
583 LEFT JOIN books books
584 ON books.owner = me.id
593 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
596 ON owner.id = me.owner
597 WHERE source != ? AND me.title = ? AND source = ?
598 GROUP BY (me.id / ?), owner.id
610 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
612 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
615 ON owner.id = me.owner
616 WHERE source != ? AND me.title = ? AND source = ?
617 GROUP BY (me.id / ?), owner.id
632 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
635 ON owner.id = me.owner
636 WHERE source != ? AND me.title = ? AND source = ?
637 GROUP BY (me.id / ?), owner.id
649 ordered_limit_offset => [
651 SELECT me.id, owner__id, owner__name, bar, baz
653 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
655 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
658 ON owner.id = me.owner
659 WHERE source != ? AND me.title = ? AND source = ?
660 GROUP BY (me.id / ?), owner.id
664 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
666 ORDER BY ORDER__BY__001, ORDER__BY__002
674 (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
677 limit_offset_prefetch => [
679 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
681 SELECT TOP 3 me.name, me.id
683 SELECT TOP 4 me.name, me.id
689 LEFT JOIN books books
690 ON books.owner = me.id
699 SELECT me.id, owner__id, owner__name, bar, baz
701 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
704 ON owner.id = me.owner
705 WHERE source != ? AND me.title = ? AND source = ?
706 GROUP BY (me.id / ?), owner.id
711 FROM books rownum__emulation
713 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
716 rownum__emulation.price > me.price
720 rownum__emulation.price IS NOT NULL
725 me.price = rownum__emulation.price
727 ( me.price IS NULL AND rownum__emulation.price IS NULL )
730 rownum__emulation.id < me.id
733 ORDER BY me.price DESC, me.id ASC
740 [ { sqlt_datatype => 'integer' } => 4 ],
743 ordered_limit_offset => [
745 SELECT me.id, owner__id, owner__name, bar, baz
747 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
750 ON owner.id = me.owner
751 WHERE source != ? AND me.title = ? AND source = ?
752 GROUP BY (me.id / ?), owner.id
757 FROM books rownum__emulation
759 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
762 rownum__emulation.price > me.price
766 rownum__emulation.price IS NOT NULL
771 me.price = rownum__emulation.price
773 ( me.price IS NULL AND rownum__emulation.price IS NULL )
776 rownum__emulation.id < me.id
779 ORDER BY me.price DESC, me.id ASC
786 [ { sqlt_datatype => 'integer' } => 3 ],
787 [ { sqlt_datatype => 'integer' } => 6 ],
790 limit_offset_prefetch => [
792 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
794 SELECT me.name, me.id
796 SELECT me.name, me.id
802 FROM owners rownum__emulation
804 rownum__emulation.name < me.name
807 me.name = rownum__emulation.name
809 rownum__emulation.id > me.id
813 ORDER BY me.name ASC, me.id DESC
815 LEFT JOIN books books
816 ON books.owner = me.id
817 ORDER BY me.name ASC, me.id DESC
820 [ { sqlt_datatype => 'integer' } => 1 ],
821 [ { sqlt_datatype => 'integer' } => 3 ],
827 for my $limtype (sort keys %$tests) {
829 Test::Builder->new->is_passing or exit;
831 delete $schema->storage->_sql_maker->{_cached_syntax};
832 $schema->storage->_sql_maker->limit_dialect ($limtype);
834 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
836 # chained search is necessary to exercise the recursive {where} parser
837 my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
838 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
839 join => 'owner', # single-rel manual prefetch
841 '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
842 group_by => \[ '(me.id / ?), owner.id', [ $attr => 21 ] ],
843 having => \[ '?', [ $attr => 31 ] ],
847 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
850 # only limit, no offset, no order
851 if ($tests->{$limtype}{limit}) {
854 @{$tests->{$limtype}{limit}},
855 "$limtype: Unordered limit with select/group/having",
858 lives_ok { $rs->all } "Grouped limit runs under $limtype"
862 # limit + offset, no order
863 if ($tests->{$limtype}{limit_offset}) {
864 my $subrs = $rs->search({}, { offset => 3 });
867 @{$tests->{$limtype}{limit_offset}},
868 "$limtype: Unordered limit+offset with select/group/having",
871 lives_ok { $subrs->all } "Grouped limit+offset runs under $limtype"
875 # order + limit, no offset
876 $rs = $rs->search(undef, {
877 order_by => ( $limtype =~ /GenericSubQ/
878 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', [ {} => 'bah' ] ] ] # needs a same-table stable order to be happy
879 : [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ]
883 if ($tests->{$limtype}{ordered_limit}) {
886 @{$tests->{$limtype}{ordered_limit}},
887 "$limtype: Ordered limit with select/group/having",
890 lives_ok { $rs->all } "Grouped ordered limit runs under $limtype"
894 # order + limit + offset
895 if ($tests->{$limtype}{ordered_limit_offset}) {
896 my $subrs = $rs->search({}, { offset => 3 });
899 @{$tests->{$limtype}{ordered_limit_offset}},
900 "$limtype: Ordered limit+offset with select/group/having",
903 lives_ok { $subrs->all } "Grouped ordered limit+offset runs under $limtype"
907 # complex prefetch on partial-fetch root with limit
908 my $pref_rs = $schema->resultset('Owners')->search({}, {
911 columns => 'name', # only the owner name, still prefetch all the books
913 ($limtype !~ /GenericSubQ/ ? () : (
914 # needs a same-table stable order to be happy
915 order_by => [ { -asc => 'me.name' }, \'me.id DESC' ]
921 @{$tests->{$limtype}{limit_offset_prefetch}},
922 "$limtype: Prefetch with limit+offset",
923 ) if $tests->{$limtype}{limit_offset_prefetch};
926 lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch') }
927 "Complex limited prefetch runs under $limtype"