1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
9 use DBICTest ':DiffSQL';
10 use DBIx::Class::_Util 'deep_clone';
12 my $schema = DBICTest->init_schema;
13 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
15 my $where_string = 'me.title = ? AND source != ? AND source = ?';
18 [ {} => 'kama sutra' ],
20 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
23 [ { sqlt_datatype => 'numeric' } => 11 ],
25 [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
34 [ { sqlt_datatype => 'int' } => 1 ],
35 [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
43 "( SELECT me.artistid FROM artist me LIMIT ? )",
45 [ { sqlt_datatype => 'integer' } => 5 ]
50 SELECT me.id, owner.id, owner.name, ? * ?, ?
53 ON owner.id = me.owner
55 GROUP BY (me.id / ?), owner.id
64 [ { sqlt_datatype => 'integer' } => 4 ],
69 SELECT me.id, owner.id, owner.name, ? * ?, ?
72 ON owner.id = me.owner
74 GROUP BY (me.id / ?), owner.id
84 [ { sqlt_datatype => 'integer' } => 4 ],
85 [ { sqlt_datatype => 'integer' } => 3 ],
90 SELECT me.id, owner.id, owner.name, ? * ?, ?
93 ON owner.id = me.owner
95 GROUP BY (me.id / ?), owner.id
106 [ { sqlt_datatype => 'integer' } => 4 ],
109 ordered_limit_offset => [
111 SELECT me.id, owner.id, owner.name, ? * ?, ?
114 ON owner.id = me.owner
116 GROUP BY (me.id / ?), owner.id
128 [ { sqlt_datatype => 'integer' } => 4 ],
129 [ { sqlt_datatype => 'integer' } => 3 ],
132 limit_offset_prefetch => [
134 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
136 SELECT me.name, me.id
140 LEFT JOIN books books
141 ON books.owner = me.id
144 [ { sqlt_datatype => 'integer' } => 3 ],
145 [ { sqlt_datatype => 'integer' } => 1 ],
152 "( SELECT me.artistid FROM artist me LIMIT ? )",
154 [ { sqlt_datatype => 'integer' } => 5 ]
157 ordered_limit_offset => [
159 SELECT me.id, owner.id, owner.name, ? * ?, ?
162 ON owner.id = me.owner
164 GROUP BY (me.id / ?), owner.id
175 [ { sqlt_datatype => 'integer' } => 3 ],
176 [ { sqlt_datatype => 'integer' } => 4 ],
179 limit_offset_prefetch => [
181 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
183 SELECT me.name, me.id
187 LEFT JOIN books books
188 ON books.owner = me.id
191 [ { sqlt_datatype => 'integer' } => 1 ],
192 [ { sqlt_datatype => 'integer' } => 3 ],
199 "( SELECT FIRST ? me.artistid FROM artist me )",
201 [ { sqlt_datatype => 'integer' } => 5 ]
204 ordered_limit_offset => [
206 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
209 ON owner.id = me.owner
211 GROUP BY (me.id / ?), owner.id
216 [ { sqlt_datatype => 'integer' } => 3 ],
217 [ { sqlt_datatype => 'integer' } => 4 ],
225 limit_offset_prefetch => [
227 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
229 SELECT SKIP ? FIRST ? me.name, me.id
232 LEFT JOIN books books
233 ON books.owner = me.id
236 [ { sqlt_datatype => 'integer' } => 1 ],
237 [ { sqlt_datatype => 'integer' } => 3 ],
244 "( SELECT FIRST ? me.artistid FROM artist me )",
246 [ { sqlt_datatype => 'integer' } => 5 ]
249 ordered_limit_offset => [
251 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
254 ON owner.id = me.owner
256 GROUP BY (me.id / ?), owner.id
261 [ { sqlt_datatype => 'integer' } => 4 ],
262 [ { sqlt_datatype => 'integer' } => 3 ],
270 limit_offset_prefetch => [
272 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
274 SELECT FIRST ? SKIP ? me.name, me.id
277 LEFT JOIN books books
278 ON books.owner = me.id
281 [ { sqlt_datatype => 'integer' } => 3 ],
282 [ { sqlt_datatype => 'integer' } => 1 ],
287 RowNumberOver => do {
288 my $unordered_sql = "(
289 SELECT me.id, owner__id, owner__name, bar, baz
291 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
293 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
296 ON owner.id = me.owner
298 GROUP BY (me.id / ?), owner.id
302 WHERE rno__row__index >= ? AND rno__row__index <= ?
306 SELECT me.id, owner__id, owner__name, bar, baz
308 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
310 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
311 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
314 ON owner.id = me.owner
316 GROUP BY (me.id / ?), owner.id
320 WHERE rno__row__index >= ? AND rno__row__index <= ?
328 SELECT me.artistid, ROW_NUMBER() OVER( ) AS rno__row__index
334 WHERE rno__row__index >= ? AND rno__row__index <= ?
337 [ { sqlt_datatype => 'integer' } => 1 ],
338 [ { sqlt_datatype => 'integer' } => 5 ],
341 limit => [$unordered_sql,
347 [ { sqlt_datatype => 'integer' } => 1 ],
348 [ { sqlt_datatype => 'integer' } => 4 ],
351 limit_offset => [$unordered_sql,
357 [ { sqlt_datatype => 'integer' } => 4 ],
358 [ { sqlt_datatype => 'integer' } => 7 ],
361 ordered_limit => [$ordered_sql,
368 [ { sqlt_datatype => 'integer' } => 1 ],
369 [ { sqlt_datatype => 'integer' } => 4 ],
372 ordered_limit_offset => [$ordered_sql,
379 [ { sqlt_datatype => 'integer' } => 4 ],
380 [ { sqlt_datatype => 'integer' } => 7 ],
383 limit_offset_prefetch => [
385 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
387 SELECT me.name, me.id
389 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
391 SELECT me.name, me.id FROM owners me
394 WHERE rno__row__index >= ? AND rno__row__index <= ?
396 LEFT JOIN books books
397 ON books.owner = me.id
400 [ { sqlt_datatype => 'integer' } => 2 ],
401 [ { sqlt_datatype => 'integer' } => 4 ],
408 my $limit_sql = sub {
410 SELECT me.id, owner__id, owner__name, bar, baz
412 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
415 ON owner.id = me.owner
417 GROUP BY (me.id / ?), owner.id
436 [ { sqlt_datatype => 'integer' } => 5 ],
439 limit => [ $limit_sql->(),
445 [ { sqlt_datatype => 'integer' } => 4 ],
450 SELECT me.id, owner__id, owner__name, bar, baz
452 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
454 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
457 ON owner.id = me.owner
459 GROUP BY (me.id / ?), owner.id
463 WHERE rownum__index BETWEEN ? AND ?
470 [ { sqlt_datatype => 'integer' } => 4 ],
471 [ { sqlt_datatype => 'integer' } => 7 ],
474 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
481 [ { sqlt_datatype => 'integer' } => 4 ],
484 ordered_limit_offset => [
486 SELECT me.id, owner__id, owner__name, bar, baz
488 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
490 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
493 ON owner.id = me.owner
495 GROUP BY (me.id / ?), owner.id
501 WHERE rownum__index >= ?
509 [ { sqlt_datatype => 'integer' } => 7 ],
510 [ { sqlt_datatype => 'integer' } => 4 ],
513 limit_offset_prefetch => [
515 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
517 SELECT me.name, me.id
519 SELECT me.name, me.id, ROWNUM AS rownum__index
521 SELECT me.name, me.id
524 ) me WHERE rownum__index BETWEEN ? AND ?
526 LEFT JOIN books books
527 ON books.owner = me.id
530 [ { sqlt_datatype => 'integer' } => 2 ],
531 [ { sqlt_datatype => 'integer' } => 4 ],
539 "( SELECT me.artistid FROM artist me FETCH FIRST 5 ROWS ONLY )",
544 SELECT me.id, owner.id, owner.name, ? * ?, ?
547 ON owner.id = me.owner
549 GROUP BY (me.id / ?), owner.id
551 FETCH FIRST 4 ROWS ONLY
562 SELECT me.id, owner__id, owner__name, bar, baz
564 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
567 ON owner.id = me.owner
569 GROUP BY (me.id / ?), owner.id
572 FETCH FIRST 7 ROWS ONLY
575 FETCH FIRST 4 ROWS ONLY
586 SELECT me.id, owner.id, owner.name, ? * ?, ?
589 ON owner.id = me.owner
591 GROUP BY (me.id / ?), owner.id
594 FETCH FIRST 4 ROWS ONLY
604 ordered_limit_offset => [
606 SELECT me.id, owner__id, owner__name, bar, baz
608 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
610 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
613 ON owner.id = me.owner
615 GROUP BY (me.id / ?), owner.id
618 FETCH FIRST 7 ROWS ONLY
620 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
621 FETCH FIRST 4 ROWS ONLY
623 ORDER BY ORDER__BY__001, ORDER__BY__002
631 @{ deep_clone \@order_bind }, # without this is_deeply throws a fit
634 limit_offset_prefetch => [
636 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
638 SELECT me.name, me.id
640 SELECT me.name, me.id
643 FETCH FIRST 4 ROWS ONLY
646 FETCH FIRST 3 ROWS ONLY
648 LEFT JOIN books books
649 ON books.owner = me.id
657 "( SELECT TOP 5 me.artistid FROM artist me )",
662 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
665 ON owner.id = me.owner
667 GROUP BY (me.id / ?), owner.id
679 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
681 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
684 ON owner.id = me.owner
686 GROUP BY (me.id / ?), owner.id
701 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
704 ON owner.id = me.owner
706 GROUP BY (me.id / ?), owner.id
718 ordered_limit_offset => [
720 SELECT me.id, owner__id, owner__name, bar, baz
722 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
724 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
727 ON owner.id = me.owner
729 GROUP BY (me.id / ?), owner.id
733 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
735 ORDER BY ORDER__BY__001, ORDER__BY__002
743 @{ deep_clone \@order_bind }, # without this is_deeply throws a fit
746 limit_offset_prefetch => [
748 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
750 SELECT TOP 3 me.name, me.id
752 SELECT TOP 4 me.name, me.id
758 LEFT JOIN books books
759 ON books.owner = me.id
776 FROM artist rownum__emulation
777 WHERE rownum__emulation.artistid < me.artistid
779 ORDER BY me.artistid ASC
782 [ { sqlt_datatype => 'integer' } => 5 ]
787 SELECT me.id, owner__id, owner__name, bar, baz
789 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
792 ON owner.id = me.owner
794 GROUP BY (me.id / ?), owner.id
799 FROM books rownum__emulation
801 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
804 rownum__emulation.price > me.price
808 rownum__emulation.price IS NOT NULL
813 me.price = rownum__emulation.price
815 ( me.price IS NULL AND rownum__emulation.price IS NULL )
818 rownum__emulation.id < me.id
821 ORDER BY me.price DESC, me.id ASC
828 [ { sqlt_datatype => 'integer' } => 4 ],
831 ordered_limit_offset => [
833 SELECT me.id, owner__id, owner__name, bar, baz
835 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
838 ON owner.id = me.owner
840 GROUP BY (me.id / ?), owner.id
845 FROM books rownum__emulation
847 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
850 rownum__emulation.price > me.price
854 rownum__emulation.price IS NOT NULL
859 me.price = rownum__emulation.price
861 ( me.price IS NULL AND rownum__emulation.price IS NULL )
864 rownum__emulation.id < me.id
867 ORDER BY me.price DESC, me.id ASC
874 [ { sqlt_datatype => 'integer' } => 3 ],
875 [ { sqlt_datatype => 'integer' } => 6 ],
878 limit_offset_prefetch => [
880 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
882 SELECT me.name, me.id
884 SELECT me.name, me.id
890 FROM owners rownum__emulation
892 rownum__emulation.name < me.name
895 me.name = rownum__emulation.name
897 rownum__emulation.id > me.id
901 ORDER BY me.name ASC, me.id DESC
903 LEFT JOIN books books
904 ON books.owner = me.id
905 ORDER BY me.name ASC, me.id DESC
908 [ { sqlt_datatype => 'integer' } => 1 ],
909 [ { sqlt_datatype => 'integer' } => 3 ],
915 for my $limtype (sort keys %$tests) {
917 Test::Builder->new->is_passing or exit;
919 delete $schema->storage->_sql_maker->{_cached_syntax};
920 $schema->storage->_sql_maker->limit_dialect ($limtype);
922 # do the simplest thing possible first
923 if ($tests->{$limtype}{limit_plain}) {
925 $schema->resultset('Artist')->search(
926 [ -and => [ {}, [] ], -or => [ {}, [] ] ],
928 columns => 'artistid',
929 join => [ {}, [ [ {}, {} ] ], {} ],
930 prefetch => [ [ [ {}, [] ], {} ], {}, [ {} ] ],
931 order_by => ( $limtype eq 'GenericSubQ' ? 'artistid' : [] ),
937 @{$tests->{$limtype}{limit_plain}},
938 "$limtype: Plain unordered ungrouped select with limit and no offset",
942 # chained search is necessary to exercise the recursive {where} parser
943 my $rs = $schema->resultset('BooksInLibrary')->search(
944 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
946 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
948 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
949 join => 'owner', # single-rel manual prefetch
951 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
952 group_by => \[ '(me.id / ?), owner.id', 21 ],
953 having => \[ '?', 31 ],
958 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
960 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
962 # only limit, no offset, no order
963 if ($tests->{$limtype}{limit}) {
967 @{$tests->{$limtype}{limit}},
968 "$limtype: Unordered limit with select/group/having",
971 $rs->all if $can_run;
972 } "Grouped limit under $limtype";
975 # limit + offset, no order
976 if ($tests->{$limtype}{limit_offset}) {
979 my $subrs = $rs->search({}, { offset => 3 });
983 @{$tests->{$limtype}{limit_offset}},
984 "$limtype: Unordered limit+offset with select/group/having",
987 $subrs->all if $can_run;
988 } "Grouped limit+offset runs under $limtype";
991 # order + limit, no offset
992 $rs = $rs->search(undef, {
993 order_by => ( $limtype =~ /GenericSubQ/
994 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
995 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
999 if ($tests->{$limtype}{ordered_limit}) {
1004 @{$tests->{$limtype}{ordered_limit}},
1005 "$limtype: Ordered limit with select/group/having",
1008 $rs->all if $can_run;
1009 } "Grouped ordered limit runs under $limtype"
1012 # order + limit + offset
1013 if ($tests->{$limtype}{ordered_limit_offset}) {
1015 my $subrs = $rs->search({}, { offset => 3 });
1019 @{$tests->{$limtype}{ordered_limit_offset}},
1020 "$limtype: Ordered limit+offset with select/group/having",
1023 $subrs->all if $can_run;
1024 } "Grouped ordered limit+offset runs under $limtype";
1027 # complex prefetch on partial-fetch root with limit
1028 my $pref_rs = $schema->resultset('Owners')->search({}, {
1031 columns => 'name', # only the owner name, still prefetch all the books
1032 prefetch => 'books',
1033 ($limtype !~ /GenericSubQ/ ? () : (
1034 # needs a same-table stable order to be happy
1035 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
1042 @{$tests->{$limtype}{limit_offset_prefetch}},
1043 "$limtype: Prefetch with limit+offset",
1044 ) if $tests->{$limtype}{limit_offset_prefetch};
1046 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
1048 } "Complex limited prefetch runs under $limtype";