8 use DBICTest ':DiffSQL';
10 my $schema = DBICTest->init_schema;
11 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
13 my $where_string = 'me.title = ? AND source != ? AND source = ?';
16 [ {} => 'kama sutra' ],
18 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
21 [ { sqlt_datatype => 'numeric' } => 11 ],
23 [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
32 [ { sqlt_datatype => 'int' } => 1 ],
33 [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
42 SELECT me.id, owner.id, owner.name, ? * ?, ?
45 ON owner.id = me.owner
47 GROUP BY (me.id / ?), owner.id
56 [ { sqlt_datatype => 'integer' } => 4 ],
61 SELECT me.id, owner.id, owner.name, ? * ?, ?
64 ON owner.id = me.owner
66 GROUP BY (me.id / ?), owner.id
76 [ { sqlt_datatype => 'integer' } => 4 ],
77 [ { sqlt_datatype => 'integer' } => 3 ],
82 SELECT me.id, owner.id, owner.name, ? * ?, ?
85 ON owner.id = me.owner
87 GROUP BY (me.id / ?), owner.id
98 [ { sqlt_datatype => 'integer' } => 4 ],
101 ordered_limit_offset => [
103 SELECT me.id, owner.id, owner.name, ? * ?, ?
106 ON owner.id = me.owner
108 GROUP BY (me.id / ?), owner.id
120 [ { sqlt_datatype => 'integer' } => 4 ],
121 [ { sqlt_datatype => 'integer' } => 3 ],
124 limit_offset_prefetch => [
126 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
128 SELECT me.name, me.id
132 LEFT JOIN books books
133 ON books.owner = me.id
136 [ { sqlt_datatype => 'integer' } => 3 ],
137 [ { sqlt_datatype => 'integer' } => 1 ],
143 ordered_limit_offset => [
145 SELECT me.id, owner.id, owner.name, ? * ?, ?
148 ON owner.id = me.owner
150 GROUP BY (me.id / ?), owner.id
161 [ { sqlt_datatype => 'integer' } => 3 ],
162 [ { sqlt_datatype => 'integer' } => 4 ],
165 limit_offset_prefetch => [
167 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
169 SELECT me.name, me.id
173 LEFT JOIN books books
174 ON books.owner = me.id
177 [ { sqlt_datatype => 'integer' } => 1 ],
178 [ { sqlt_datatype => 'integer' } => 3 ],
184 ordered_limit_offset => [
186 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
189 ON owner.id = me.owner
191 GROUP BY (me.id / ?), owner.id
196 [ { sqlt_datatype => 'integer' } => 3 ],
197 [ { sqlt_datatype => 'integer' } => 4 ],
205 limit_offset_prefetch => [
207 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
209 SELECT SKIP ? FIRST ? me.name, me.id
212 LEFT JOIN books books
213 ON books.owner = me.id
216 [ { sqlt_datatype => 'integer' } => 1 ],
217 [ { sqlt_datatype => 'integer' } => 3 ],
223 ordered_limit_offset => [
225 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
228 ON owner.id = me.owner
230 GROUP BY (me.id / ?), owner.id
235 [ { sqlt_datatype => 'integer' } => 4 ],
236 [ { sqlt_datatype => 'integer' } => 3 ],
244 limit_offset_prefetch => [
246 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
248 SELECT FIRST ? SKIP ? me.name, me.id
251 LEFT JOIN books books
252 ON books.owner = me.id
255 [ { sqlt_datatype => 'integer' } => 3 ],
256 [ { sqlt_datatype => 'integer' } => 1 ],
261 RowNumberOver => do {
262 my $unordered_sql = "(
263 SELECT me.id, owner__id, owner__name, bar, baz
265 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
267 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
270 ON owner.id = me.owner
272 GROUP BY (me.id / ?), owner.id
276 WHERE rno__row__index >= ? AND rno__row__index <= ?
280 SELECT me.id, owner__id, owner__name, bar, baz
282 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
284 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
285 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
288 ON owner.id = me.owner
290 GROUP BY (me.id / ?), owner.id
294 WHERE rno__row__index >= ? AND rno__row__index <= ?
298 limit => [$unordered_sql,
304 [ { sqlt_datatype => 'integer' } => 1 ],
305 [ { sqlt_datatype => 'integer' } => 4 ],
308 limit_offset => [$unordered_sql,
314 [ { sqlt_datatype => 'integer' } => 4 ],
315 [ { sqlt_datatype => 'integer' } => 7 ],
318 ordered_limit => [$ordered_sql,
325 [ { sqlt_datatype => 'integer' } => 1 ],
326 [ { sqlt_datatype => 'integer' } => 4 ],
329 ordered_limit_offset => [$ordered_sql,
336 [ { sqlt_datatype => 'integer' } => 4 ],
337 [ { sqlt_datatype => 'integer' } => 7 ],
340 limit_offset_prefetch => [
342 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
344 SELECT me.name, me.id
346 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
348 SELECT me.name, me.id FROM owners me
351 WHERE rno__row__index >= ? AND rno__row__index <= ?
353 LEFT JOIN books books
354 ON books.owner = me.id
357 [ { sqlt_datatype => 'integer' } => 2 ],
358 [ { sqlt_datatype => 'integer' } => 4 ],
365 my $limit_sql = sub {
367 SELECT me.id, owner__id, owner__name, bar, baz
369 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
372 ON owner.id = me.owner
374 GROUP BY (me.id / ?), owner.id
383 limit => [ $limit_sql->(),
389 [ { sqlt_datatype => 'integer' } => 4 ],
394 SELECT me.id, owner__id, owner__name, bar, baz
396 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
398 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
401 ON owner.id = me.owner
403 GROUP BY (me.id / ?), owner.id
407 WHERE rownum__index BETWEEN ? AND ?
414 [ { sqlt_datatype => 'integer' } => 4 ],
415 [ { sqlt_datatype => 'integer' } => 7 ],
418 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
425 [ { sqlt_datatype => 'integer' } => 4 ],
428 ordered_limit_offset => [
430 SELECT me.id, owner__id, owner__name, bar, baz
432 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
434 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
437 ON owner.id = me.owner
439 GROUP BY (me.id / ?), owner.id
445 WHERE rownum__index >= ?
453 [ { sqlt_datatype => 'integer' } => 7 ],
454 [ { sqlt_datatype => 'integer' } => 4 ],
457 limit_offset_prefetch => [
459 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
461 SELECT me.name, me.id
463 SELECT me.name, me.id, ROWNUM AS rownum__index
465 SELECT me.name, me.id
468 ) me WHERE rownum__index BETWEEN ? AND ?
470 LEFT JOIN books books
471 ON books.owner = me.id
474 [ { sqlt_datatype => 'integer' } => 2 ],
475 [ { sqlt_datatype => 'integer' } => 4 ],
484 SELECT me.id, owner.id, owner.name, ? * ?, ?
487 ON owner.id = me.owner
489 GROUP BY (me.id / ?), owner.id
491 FETCH FIRST 4 ROWS ONLY
502 SELECT me.id, owner__id, owner__name, bar, baz
504 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
507 ON owner.id = me.owner
509 GROUP BY (me.id / ?), owner.id
512 FETCH FIRST 7 ROWS ONLY
515 FETCH FIRST 4 ROWS ONLY
526 SELECT me.id, owner.id, owner.name, ? * ?, ?
529 ON owner.id = me.owner
531 GROUP BY (me.id / ?), owner.id
534 FETCH FIRST 4 ROWS ONLY
544 ordered_limit_offset => [
546 SELECT me.id, owner__id, owner__name, bar, baz
548 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
550 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
553 ON owner.id = me.owner
555 GROUP BY (me.id / ?), owner.id
558 FETCH FIRST 7 ROWS ONLY
560 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
561 FETCH FIRST 4 ROWS ONLY
563 ORDER BY ORDER__BY__001, ORDER__BY__002
571 @{ dclone \@order_bind }, # without this is_deeply throws a fit
574 limit_offset_prefetch => [
576 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
578 SELECT me.name, me.id
580 SELECT me.name, me.id
583 FETCH FIRST 4 ROWS ONLY
586 FETCH FIRST 3 ROWS ONLY
588 LEFT JOIN books books
589 ON books.owner = me.id
598 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
601 ON owner.id = me.owner
603 GROUP BY (me.id / ?), owner.id
615 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
617 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
620 ON owner.id = me.owner
622 GROUP BY (me.id / ?), owner.id
637 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
640 ON owner.id = me.owner
642 GROUP BY (me.id / ?), owner.id
654 ordered_limit_offset => [
656 SELECT me.id, owner__id, owner__name, bar, baz
658 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
660 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
663 ON owner.id = me.owner
665 GROUP BY (me.id / ?), owner.id
669 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
671 ORDER BY ORDER__BY__001, ORDER__BY__002
679 @{ dclone \@order_bind }, # without this is_deeply throws a fit
682 limit_offset_prefetch => [
684 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
686 SELECT TOP 3 me.name, me.id
688 SELECT TOP 4 me.name, me.id
694 LEFT JOIN books books
695 ON books.owner = me.id
704 SELECT me.id, owner__id, owner__name, bar, baz
706 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
709 ON owner.id = me.owner
711 GROUP BY (me.id / ?), owner.id
716 FROM books rownum__emulation
718 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
721 rownum__emulation.price > me.price
725 rownum__emulation.price IS NOT NULL
730 me.price = rownum__emulation.price
732 ( me.price IS NULL AND rownum__emulation.price IS NULL )
735 rownum__emulation.id < me.id
738 ORDER BY me.price DESC, me.id ASC
745 [ { sqlt_datatype => 'integer' } => 4 ],
748 ordered_limit_offset => [
750 SELECT me.id, owner__id, owner__name, bar, baz
752 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
755 ON owner.id = me.owner
757 GROUP BY (me.id / ?), owner.id
762 FROM books rownum__emulation
764 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
767 rownum__emulation.price > me.price
771 rownum__emulation.price IS NOT NULL
776 me.price = rownum__emulation.price
778 ( me.price IS NULL AND rownum__emulation.price IS NULL )
781 rownum__emulation.id < me.id
784 ORDER BY me.price DESC, me.id ASC
791 [ { sqlt_datatype => 'integer' } => 3 ],
792 [ { sqlt_datatype => 'integer' } => 6 ],
795 limit_offset_prefetch => [
797 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
799 SELECT me.name, me.id
801 SELECT me.name, me.id
807 FROM owners rownum__emulation
809 rownum__emulation.name < me.name
812 me.name = rownum__emulation.name
814 rownum__emulation.id > me.id
818 ORDER BY me.name ASC, me.id DESC
820 LEFT JOIN books books
821 ON books.owner = me.id
822 ORDER BY me.name ASC, me.id DESC
825 [ { sqlt_datatype => 'integer' } => 1 ],
826 [ { sqlt_datatype => 'integer' } => 3 ],
832 for my $limtype (sort keys %$tests) {
834 Test::Builder->new->is_passing or exit;
836 delete $schema->storage->_sql_maker->{_cached_syntax};
837 $schema->storage->_sql_maker->limit_dialect ($limtype);
839 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
841 # chained search is necessary to exercise the recursive {where} parser
842 my $rs = $schema->resultset('BooksInLibrary')->search(
843 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
845 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
847 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
848 join => 'owner', # single-rel manual prefetch
850 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
851 group_by => \[ '(me.id / ?), owner.id', 21 ],
852 having => \[ '?', 31 ],
857 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
860 # only limit, no offset, no order
861 if ($tests->{$limtype}{limit}) {
865 @{$tests->{$limtype}{limit}},
866 "$limtype: Unordered limit with select/group/having",
869 $rs->all if $can_run;
870 } "Grouped limit under $limtype";
873 # limit + offset, no order
874 if ($tests->{$limtype}{limit_offset}) {
877 my $subrs = $rs->search({}, { offset => 3 });
881 @{$tests->{$limtype}{limit_offset}},
882 "$limtype: Unordered limit+offset with select/group/having",
885 $subrs->all if $can_run;
886 } "Grouped limit+offset runs under $limtype";
889 # order + limit, no offset
890 $rs = $rs->search(undef, {
891 order_by => ( $limtype =~ /GenericSubQ/
892 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
893 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
897 if ($tests->{$limtype}{ordered_limit}) {
902 @{$tests->{$limtype}{ordered_limit}},
903 "$limtype: Ordered limit with select/group/having",
906 $rs->all if $can_run;
907 } "Grouped ordered limit runs under $limtype"
910 # order + limit + offset
911 if ($tests->{$limtype}{ordered_limit_offset}) {
913 my $subrs = $rs->search({}, { offset => 3 });
917 @{$tests->{$limtype}{ordered_limit_offset}},
918 "$limtype: Ordered limit+offset with select/group/having",
921 $subrs->all if $can_run;
922 } "Grouped ordered limit+offset runs under $limtype";
925 # complex prefetch on partial-fetch root with limit
926 my $pref_rs = $schema->resultset('Owners')->search({}, {
929 columns => 'name', # only the owner name, still prefetch all the books
931 ($limtype !~ /GenericSubQ/ ? () : (
932 # needs a same-table stable order to be happy
933 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
940 @{$tests->{$limtype}{limit_offset_prefetch}},
941 "$limtype: Prefetch with limit+offset",
942 ) if $tests->{$limtype}{limit_offset_prefetch};
944 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
946 } "Complex limited prefetch runs under $limtype";