7 use DBICTest ':DiffSQL';
8 use DBIx::Class::_Util 'deep_clone';
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 ],
41 "( SELECT me.artistid FROM artist me LIMIT ? )",
43 [ { sqlt_datatype => 'integer' } => 5 ]
48 SELECT me.id, owner.id, owner.name, ? * ?, ?
51 ON owner.id = me.owner
53 GROUP BY (me.id / ?), owner.id
62 [ { sqlt_datatype => 'integer' } => 4 ],
67 SELECT me.id, owner.id, owner.name, ? * ?, ?
70 ON owner.id = me.owner
72 GROUP BY (me.id / ?), owner.id
82 [ { sqlt_datatype => 'integer' } => 4 ],
83 [ { sqlt_datatype => 'integer' } => 3 ],
88 SELECT me.id, owner.id, owner.name, ? * ?, ?
91 ON owner.id = me.owner
93 GROUP BY (me.id / ?), owner.id
104 [ { sqlt_datatype => 'integer' } => 4 ],
107 ordered_limit_offset => [
109 SELECT me.id, owner.id, owner.name, ? * ?, ?
112 ON owner.id = me.owner
114 GROUP BY (me.id / ?), owner.id
126 [ { sqlt_datatype => 'integer' } => 4 ],
127 [ { sqlt_datatype => 'integer' } => 3 ],
130 limit_offset_prefetch => [
132 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
134 SELECT me.name, me.id
138 LEFT JOIN books books
139 ON books.owner = me.id
142 [ { sqlt_datatype => 'integer' } => 3 ],
143 [ { sqlt_datatype => 'integer' } => 1 ],
150 "( SELECT me.artistid FROM artist me LIMIT ? )",
152 [ { sqlt_datatype => 'integer' } => 5 ]
155 ordered_limit_offset => [
157 SELECT me.id, owner.id, owner.name, ? * ?, ?
160 ON owner.id = me.owner
162 GROUP BY (me.id / ?), owner.id
173 [ { sqlt_datatype => 'integer' } => 3 ],
174 [ { sqlt_datatype => 'integer' } => 4 ],
177 limit_offset_prefetch => [
179 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
181 SELECT me.name, me.id
185 LEFT JOIN books books
186 ON books.owner = me.id
189 [ { sqlt_datatype => 'integer' } => 1 ],
190 [ { sqlt_datatype => 'integer' } => 3 ],
197 "( SELECT FIRST ? me.artistid FROM artist me )",
199 [ { sqlt_datatype => 'integer' } => 5 ]
202 ordered_limit_offset => [
204 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
207 ON owner.id = me.owner
209 GROUP BY (me.id / ?), owner.id
214 [ { sqlt_datatype => 'integer' } => 3 ],
215 [ { sqlt_datatype => 'integer' } => 4 ],
223 limit_offset_prefetch => [
225 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
227 SELECT SKIP ? FIRST ? me.name, me.id
230 LEFT JOIN books books
231 ON books.owner = me.id
234 [ { sqlt_datatype => 'integer' } => 1 ],
235 [ { sqlt_datatype => 'integer' } => 3 ],
242 "( SELECT FIRST ? me.artistid FROM artist me )",
244 [ { sqlt_datatype => 'integer' } => 5 ]
247 ordered_limit_offset => [
249 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
252 ON owner.id = me.owner
254 GROUP BY (me.id / ?), owner.id
259 [ { sqlt_datatype => 'integer' } => 4 ],
260 [ { sqlt_datatype => 'integer' } => 3 ],
268 limit_offset_prefetch => [
270 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
272 SELECT FIRST ? SKIP ? me.name, me.id
275 LEFT JOIN books books
276 ON books.owner = me.id
279 [ { sqlt_datatype => 'integer' } => 3 ],
280 [ { sqlt_datatype => 'integer' } => 1 ],
285 RowNumberOver => do {
286 my $unordered_sql = "(
287 SELECT me.id, owner__id, owner__name, bar, baz
289 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
291 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
294 ON owner.id = me.owner
296 GROUP BY (me.id / ?), owner.id
300 WHERE rno__row__index >= ? AND rno__row__index <= ?
304 SELECT me.id, owner__id, owner__name, bar, baz
306 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
308 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
309 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
312 ON owner.id = me.owner
314 GROUP BY (me.id / ?), owner.id
318 WHERE rno__row__index >= ? AND rno__row__index <= ?
326 SELECT me.artistid, ROW_NUMBER() OVER( ) AS rno__row__index
332 WHERE rno__row__index >= ? AND rno__row__index <= ?
335 [ { sqlt_datatype => 'integer' } => 1 ],
336 [ { sqlt_datatype => 'integer' } => 5 ],
339 limit => [$unordered_sql,
345 [ { sqlt_datatype => 'integer' } => 1 ],
346 [ { sqlt_datatype => 'integer' } => 4 ],
349 limit_offset => [$unordered_sql,
355 [ { sqlt_datatype => 'integer' } => 4 ],
356 [ { sqlt_datatype => 'integer' } => 7 ],
359 ordered_limit => [$ordered_sql,
366 [ { sqlt_datatype => 'integer' } => 1 ],
367 [ { sqlt_datatype => 'integer' } => 4 ],
370 ordered_limit_offset => [$ordered_sql,
377 [ { sqlt_datatype => 'integer' } => 4 ],
378 [ { sqlt_datatype => 'integer' } => 7 ],
381 limit_offset_prefetch => [
383 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
385 SELECT me.name, me.id
387 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
389 SELECT me.name, me.id FROM owners me
392 WHERE rno__row__index >= ? AND rno__row__index <= ?
394 LEFT JOIN books books
395 ON books.owner = me.id
398 [ { sqlt_datatype => 'integer' } => 2 ],
399 [ { sqlt_datatype => 'integer' } => 4 ],
406 my $limit_sql = sub {
408 SELECT me.id, owner__id, owner__name, bar, baz
410 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
413 ON owner.id = me.owner
415 GROUP BY (me.id / ?), owner.id
434 [ { sqlt_datatype => 'integer' } => 5 ],
437 limit => [ $limit_sql->(),
443 [ { sqlt_datatype => 'integer' } => 4 ],
448 SELECT me.id, owner__id, owner__name, bar, baz
450 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
452 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
455 ON owner.id = me.owner
457 GROUP BY (me.id / ?), owner.id
461 WHERE rownum__index BETWEEN ? AND ?
468 [ { sqlt_datatype => 'integer' } => 4 ],
469 [ { sqlt_datatype => 'integer' } => 7 ],
472 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
479 [ { sqlt_datatype => 'integer' } => 4 ],
482 ordered_limit_offset => [
484 SELECT me.id, owner__id, owner__name, bar, baz
486 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
488 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
491 ON owner.id = me.owner
493 GROUP BY (me.id / ?), owner.id
499 WHERE rownum__index >= ?
507 [ { sqlt_datatype => 'integer' } => 7 ],
508 [ { sqlt_datatype => 'integer' } => 4 ],
511 limit_offset_prefetch => [
513 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
515 SELECT me.name, me.id
517 SELECT me.name, me.id, ROWNUM AS rownum__index
519 SELECT me.name, me.id
522 ) me WHERE rownum__index BETWEEN ? AND ?
524 LEFT JOIN books books
525 ON books.owner = me.id
528 [ { sqlt_datatype => 'integer' } => 2 ],
529 [ { sqlt_datatype => 'integer' } => 4 ],
537 "( SELECT me.artistid FROM artist me FETCH FIRST 5 ROWS ONLY )",
542 SELECT me.id, owner.id, owner.name, ? * ?, ?
545 ON owner.id = me.owner
547 GROUP BY (me.id / ?), owner.id
549 FETCH FIRST 4 ROWS ONLY
560 SELECT me.id, owner__id, owner__name, bar, baz
562 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
565 ON owner.id = me.owner
567 GROUP BY (me.id / ?), owner.id
570 FETCH FIRST 7 ROWS ONLY
573 FETCH FIRST 4 ROWS ONLY
584 SELECT me.id, owner.id, owner.name, ? * ?, ?
587 ON owner.id = me.owner
589 GROUP BY (me.id / ?), owner.id
592 FETCH FIRST 4 ROWS ONLY
602 ordered_limit_offset => [
604 SELECT me.id, owner__id, owner__name, bar, baz
606 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
608 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
611 ON owner.id = me.owner
613 GROUP BY (me.id / ?), owner.id
616 FETCH FIRST 7 ROWS ONLY
618 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
619 FETCH FIRST 4 ROWS ONLY
621 ORDER BY ORDER__BY__001, ORDER__BY__002
629 @{ deep_clone \@order_bind }, # without this is_deeply throws a fit
632 limit_offset_prefetch => [
634 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
636 SELECT me.name, me.id
638 SELECT me.name, me.id
641 FETCH FIRST 4 ROWS ONLY
644 FETCH FIRST 3 ROWS ONLY
646 LEFT JOIN books books
647 ON books.owner = me.id
655 "( SELECT TOP 5 me.artistid FROM artist me )",
660 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
663 ON owner.id = me.owner
665 GROUP BY (me.id / ?), owner.id
677 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
679 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
682 ON owner.id = me.owner
684 GROUP BY (me.id / ?), owner.id
699 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
702 ON owner.id = me.owner
704 GROUP BY (me.id / ?), owner.id
716 ordered_limit_offset => [
718 SELECT me.id, owner__id, owner__name, bar, baz
720 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
722 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
725 ON owner.id = me.owner
727 GROUP BY (me.id / ?), owner.id
731 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
733 ORDER BY ORDER__BY__001, ORDER__BY__002
741 @{ deep_clone \@order_bind }, # without this is_deeply throws a fit
744 limit_offset_prefetch => [
746 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
748 SELECT TOP 3 me.name, me.id
750 SELECT TOP 4 me.name, me.id
756 LEFT JOIN books books
757 ON books.owner = me.id
774 FROM artist rownum__emulation
775 WHERE rownum__emulation.artistid < me.artistid
777 ORDER BY me.artistid ASC
780 [ { sqlt_datatype => 'integer' } => 5 ]
785 SELECT me.id, owner__id, owner__name, bar, baz
787 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
790 ON owner.id = me.owner
792 GROUP BY (me.id / ?), owner.id
797 FROM books rownum__emulation
799 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
802 rownum__emulation.price > me.price
806 rownum__emulation.price IS NOT NULL
811 me.price = rownum__emulation.price
813 ( me.price IS NULL AND rownum__emulation.price IS NULL )
816 rownum__emulation.id < me.id
819 ORDER BY me.price DESC, me.id ASC
826 [ { sqlt_datatype => 'integer' } => 4 ],
829 ordered_limit_offset => [
831 SELECT me.id, owner__id, owner__name, bar, baz
833 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
836 ON owner.id = me.owner
838 GROUP BY (me.id / ?), owner.id
843 FROM books rownum__emulation
845 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
848 rownum__emulation.price > me.price
852 rownum__emulation.price IS NOT NULL
857 me.price = rownum__emulation.price
859 ( me.price IS NULL AND rownum__emulation.price IS NULL )
862 rownum__emulation.id < me.id
865 ORDER BY me.price DESC, me.id ASC
872 [ { sqlt_datatype => 'integer' } => 3 ],
873 [ { sqlt_datatype => 'integer' } => 6 ],
876 limit_offset_prefetch => [
878 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
880 SELECT me.name, me.id
882 SELECT me.name, me.id
888 FROM owners rownum__emulation
890 rownum__emulation.name < me.name
893 me.name = rownum__emulation.name
895 rownum__emulation.id > me.id
899 ORDER BY me.name ASC, me.id DESC
901 LEFT JOIN books books
902 ON books.owner = me.id
903 ORDER BY me.name ASC, me.id DESC
906 [ { sqlt_datatype => 'integer' } => 1 ],
907 [ { sqlt_datatype => 'integer' } => 3 ],
913 for my $limtype (sort keys %$tests) {
915 Test::Builder->new->is_passing or exit;
917 delete $schema->storage->_sql_maker->{_cached_syntax};
918 $schema->storage->_sql_maker->limit_dialect ($limtype);
920 # do the simplest thing possible first
921 if ($tests->{$limtype}{limit_plain}) {
923 $schema->resultset('Artist')->search(
924 [ -and => [ {}, [] ], -or => [ {}, [] ] ],
926 columns => 'artistid',
927 join => [ {}, [ [ {}, {} ] ], {} ],
928 prefetch => [ [ [ {}, [] ], {} ], {}, [ {} ] ],
929 order_by => ( $limtype eq 'GenericSubQ' ? 'artistid' : [] ),
935 @{$tests->{$limtype}{limit_plain}},
936 "$limtype: Plain unordered ungrouped select with limit and no offset",
940 # chained search is necessary to exercise the recursive {where} parser
941 my $rs = $schema->resultset('BooksInLibrary')->search(
942 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
944 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
946 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
947 join => 'owner', # single-rel manual prefetch
949 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
950 group_by => \[ '(me.id / ?), owner.id', 21 ],
951 having => \[ '?', 31 ],
956 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
958 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
960 # only limit, no offset, no order
961 if ($tests->{$limtype}{limit}) {
965 @{$tests->{$limtype}{limit}},
966 "$limtype: Unordered limit with select/group/having",
969 $rs->all if $can_run;
970 } "Grouped limit under $limtype";
973 # limit + offset, no order
974 if ($tests->{$limtype}{limit_offset}) {
977 my $subrs = $rs->search({}, { offset => 3 });
981 @{$tests->{$limtype}{limit_offset}},
982 "$limtype: Unordered limit+offset with select/group/having",
985 $subrs->all if $can_run;
986 } "Grouped limit+offset runs under $limtype";
989 # order + limit, no offset
990 $rs = $rs->search(undef, {
991 order_by => ( $limtype =~ /GenericSubQ/
992 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
993 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
997 if ($tests->{$limtype}{ordered_limit}) {
1002 @{$tests->{$limtype}{ordered_limit}},
1003 "$limtype: Ordered limit with select/group/having",
1006 $rs->all if $can_run;
1007 } "Grouped ordered limit runs under $limtype"
1010 # order + limit + offset
1011 if ($tests->{$limtype}{ordered_limit_offset}) {
1013 my $subrs = $rs->search({}, { offset => 3 });
1017 @{$tests->{$limtype}{ordered_limit_offset}},
1018 "$limtype: Ordered limit+offset with select/group/having",
1021 $subrs->all if $can_run;
1022 } "Grouped ordered limit+offset runs under $limtype";
1025 # complex prefetch on partial-fetch root with limit
1026 my $pref_rs = $schema->resultset('Owners')->search({}, {
1029 columns => 'name', # only the owner name, still prefetch all the books
1030 prefetch => 'books',
1031 ($limtype !~ /GenericSubQ/ ? () : (
1032 # needs a same-table stable order to be happy
1033 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
1040 @{$tests->{$limtype}{limit_offset_prefetch}},
1041 "$limtype: Prefetch with limit+offset",
1042 ) if $tests->{$limtype}{limit_offset_prefetch};
1044 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
1046 } "Complex limited prefetch runs under $limtype";