9 use DBIC::SqlMakerTest;
11 my $schema = DBICTest->init_schema;
12 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
14 my $where_string = 'me.title = ? AND source != ? AND source = ?';
17 [ {} => 'kama sutra' ],
19 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
22 [ { sqlt_datatype => 'numeric' } => 11 ],
24 [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
33 [ { sqlt_datatype => 'int' } => 1 ],
34 [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
43 SELECT me.id, owner.id, owner.name, ? * ?, ?
46 ON owner.id = me.owner
48 GROUP BY (me.id / ?), owner.id
57 [ { sqlt_datatype => 'integer' } => 4 ],
62 SELECT me.id, owner.id, owner.name, ? * ?, ?
65 ON owner.id = me.owner
67 GROUP BY (me.id / ?), owner.id
77 [ { sqlt_datatype => 'integer' } => 4 ],
78 [ { sqlt_datatype => 'integer' } => 3 ],
83 SELECT me.id, owner.id, owner.name, ? * ?, ?
86 ON owner.id = me.owner
88 GROUP BY (me.id / ?), owner.id
99 [ { sqlt_datatype => 'integer' } => 4 ],
102 ordered_limit_offset => [
104 SELECT me.id, owner.id, owner.name, ? * ?, ?
107 ON owner.id = me.owner
109 GROUP BY (me.id / ?), owner.id
121 [ { sqlt_datatype => 'integer' } => 4 ],
122 [ { sqlt_datatype => 'integer' } => 3 ],
125 limit_offset_prefetch => [
127 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
129 SELECT me.name, me.id
133 LEFT JOIN books books
134 ON books.owner = me.id
137 [ { sqlt_datatype => 'integer' } => 3 ],
138 [ { sqlt_datatype => 'integer' } => 1 ],
144 ordered_limit_offset => [
146 SELECT me.id, owner.id, owner.name, ? * ?, ?
149 ON owner.id = me.owner
151 GROUP BY (me.id / ?), owner.id
162 [ { sqlt_datatype => 'integer' } => 3 ],
163 [ { sqlt_datatype => 'integer' } => 4 ],
166 limit_offset_prefetch => [
168 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
170 SELECT me.name, me.id
174 LEFT JOIN books books
175 ON books.owner = me.id
178 [ { sqlt_datatype => 'integer' } => 1 ],
179 [ { sqlt_datatype => 'integer' } => 3 ],
185 ordered_limit_offset => [
187 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
190 ON owner.id = me.owner
192 GROUP BY (me.id / ?), owner.id
197 [ { sqlt_datatype => 'integer' } => 3 ],
198 [ { sqlt_datatype => 'integer' } => 4 ],
206 limit_offset_prefetch => [
208 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
210 SELECT SKIP ? FIRST ? me.name, me.id
213 LEFT JOIN books books
214 ON books.owner = me.id
217 [ { sqlt_datatype => 'integer' } => 1 ],
218 [ { sqlt_datatype => 'integer' } => 3 ],
224 ordered_limit_offset => [
226 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
229 ON owner.id = me.owner
231 GROUP BY (me.id / ?), owner.id
236 [ { sqlt_datatype => 'integer' } => 4 ],
237 [ { sqlt_datatype => 'integer' } => 3 ],
245 limit_offset_prefetch => [
247 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
249 SELECT FIRST ? SKIP ? me.name, me.id
252 LEFT JOIN books books
253 ON books.owner = me.id
256 [ { sqlt_datatype => 'integer' } => 3 ],
257 [ { sqlt_datatype => 'integer' } => 1 ],
262 RowNumberOver => do {
263 my $unordered_sql = "(
264 SELECT me.id, owner__id, owner__name, bar, baz
266 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
268 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
271 ON owner.id = me.owner
273 GROUP BY (me.id / ?), owner.id
277 WHERE rno__row__index >= ? AND rno__row__index <= ?
281 SELECT me.id, owner__id, owner__name, bar, baz
283 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
285 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
286 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
289 ON owner.id = me.owner
291 GROUP BY (me.id / ?), owner.id
295 WHERE rno__row__index >= ? AND rno__row__index <= ?
299 limit => [$unordered_sql,
305 [ { sqlt_datatype => 'integer' } => 1 ],
306 [ { sqlt_datatype => 'integer' } => 4 ],
309 limit_offset => [$unordered_sql,
315 [ { sqlt_datatype => 'integer' } => 4 ],
316 [ { sqlt_datatype => 'integer' } => 7 ],
319 ordered_limit => [$ordered_sql,
326 [ { sqlt_datatype => 'integer' } => 1 ],
327 [ { sqlt_datatype => 'integer' } => 4 ],
330 ordered_limit_offset => [$ordered_sql,
337 [ { sqlt_datatype => 'integer' } => 4 ],
338 [ { sqlt_datatype => 'integer' } => 7 ],
341 limit_offset_prefetch => [
343 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
345 SELECT me.name, me.id
347 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
349 SELECT me.name, me.id FROM owners me
352 WHERE rno__row__index >= ? AND rno__row__index <= ?
354 LEFT JOIN books books
355 ON books.owner = me.id
358 [ { sqlt_datatype => 'integer' } => 2 ],
359 [ { sqlt_datatype => 'integer' } => 4 ],
366 my $limit_sql = sub {
368 SELECT me.id, owner__id, owner__name, bar, baz
370 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
373 ON owner.id = me.owner
375 GROUP BY (me.id / ?), owner.id
384 limit => [ $limit_sql->(),
390 [ { sqlt_datatype => 'integer' } => 4 ],
395 SELECT me.id, owner__id, owner__name, bar, baz
397 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
399 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
402 ON owner.id = me.owner
404 GROUP BY (me.id / ?), owner.id
408 WHERE rownum__index BETWEEN ? AND ?
415 [ { sqlt_datatype => 'integer' } => 4 ],
416 [ { sqlt_datatype => 'integer' } => 7 ],
419 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
426 [ { sqlt_datatype => 'integer' } => 4 ],
429 ordered_limit_offset => [
431 SELECT me.id, owner__id, owner__name, bar, baz
433 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
435 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
438 ON owner.id = me.owner
440 GROUP BY (me.id / ?), owner.id
446 WHERE rownum__index >= ?
454 [ { sqlt_datatype => 'integer' } => 7 ],
455 [ { sqlt_datatype => 'integer' } => 4 ],
458 limit_offset_prefetch => [
460 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
462 SELECT me.name, me.id
464 SELECT me.name, me.id, ROWNUM AS rownum__index
466 SELECT me.name, me.id
469 ) me WHERE rownum__index BETWEEN ? AND ?
471 LEFT JOIN books books
472 ON books.owner = me.id
475 [ { sqlt_datatype => 'integer' } => 2 ],
476 [ { sqlt_datatype => 'integer' } => 4 ],
485 SELECT me.id, owner.id, owner.name, ? * ?, ?
488 ON owner.id = me.owner
490 GROUP BY (me.id / ?), owner.id
492 FETCH FIRST 4 ROWS ONLY
503 SELECT me.id, owner__id, owner__name, bar, baz
505 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
508 ON owner.id = me.owner
510 GROUP BY (me.id / ?), owner.id
513 FETCH FIRST 7 ROWS ONLY
516 FETCH FIRST 4 ROWS ONLY
527 SELECT me.id, owner.id, owner.name, ? * ?, ?
530 ON owner.id = me.owner
532 GROUP BY (me.id / ?), owner.id
535 FETCH FIRST 4 ROWS ONLY
545 ordered_limit_offset => [
547 SELECT me.id, owner__id, owner__name, bar, baz
549 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
551 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
554 ON owner.id = me.owner
556 GROUP BY (me.id / ?), owner.id
559 FETCH FIRST 7 ROWS ONLY
561 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
562 FETCH FIRST 4 ROWS ONLY
564 ORDER BY ORDER__BY__001, ORDER__BY__002
572 @{ dclone \@order_bind }, # without this is_deeply throws a fit
575 limit_offset_prefetch => [
577 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
579 SELECT me.name, me.id
581 SELECT me.name, me.id
584 FETCH FIRST 4 ROWS ONLY
587 FETCH FIRST 3 ROWS ONLY
589 LEFT JOIN books books
590 ON books.owner = me.id
599 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
602 ON owner.id = me.owner
604 GROUP BY (me.id / ?), owner.id
616 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
618 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
621 ON owner.id = me.owner
623 GROUP BY (me.id / ?), owner.id
638 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
641 ON owner.id = me.owner
643 GROUP BY (me.id / ?), owner.id
655 ordered_limit_offset => [
657 SELECT me.id, owner__id, owner__name, bar, baz
659 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
661 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
664 ON owner.id = me.owner
666 GROUP BY (me.id / ?), owner.id
670 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
672 ORDER BY ORDER__BY__001, ORDER__BY__002
680 @{ dclone \@order_bind }, # without this is_deeply throws a fit
683 limit_offset_prefetch => [
685 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
687 SELECT TOP 3 me.name, me.id
689 SELECT TOP 4 me.name, me.id
695 LEFT JOIN books books
696 ON books.owner = me.id
705 SELECT me.id, owner__id, owner__name, bar, baz
707 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
710 ON owner.id = me.owner
712 GROUP BY (me.id / ?), owner.id
717 FROM books rownum__emulation
719 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
722 rownum__emulation.price > me.price
726 rownum__emulation.price IS NOT NULL
731 me.price = rownum__emulation.price
733 ( me.price IS NULL AND rownum__emulation.price IS NULL )
736 rownum__emulation.id < me.id
739 ORDER BY me.price DESC, me.id ASC
746 [ { sqlt_datatype => 'integer' } => 4 ],
749 ordered_limit_offset => [
751 SELECT me.id, owner__id, owner__name, bar, baz
753 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
756 ON owner.id = me.owner
758 GROUP BY (me.id / ?), owner.id
763 FROM books rownum__emulation
765 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
768 rownum__emulation.price > me.price
772 rownum__emulation.price IS NOT NULL
777 me.price = rownum__emulation.price
779 ( me.price IS NULL AND rownum__emulation.price IS NULL )
782 rownum__emulation.id < me.id
785 ORDER BY me.price DESC, me.id ASC
792 [ { sqlt_datatype => 'integer' } => 3 ],
793 [ { sqlt_datatype => 'integer' } => 6 ],
796 limit_offset_prefetch => [
798 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
800 SELECT me.name, me.id
802 SELECT me.name, me.id
808 FROM owners rownum__emulation
810 rownum__emulation.name < me.name
813 me.name = rownum__emulation.name
815 rownum__emulation.id > me.id
819 ORDER BY me.name ASC, me.id DESC
821 LEFT JOIN books books
822 ON books.owner = me.id
823 ORDER BY me.name ASC, me.id DESC
826 [ { sqlt_datatype => 'integer' } => 1 ],
827 [ { sqlt_datatype => 'integer' } => 3 ],
833 for my $limtype (sort keys %$tests) {
835 Test::Builder->new->is_passing or exit;
837 delete $schema->storage->_sql_maker->{_cached_syntax};
838 $schema->storage->_sql_maker->limit_dialect ($limtype);
840 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
842 # chained search is necessary to exercise the recursive {where} parser
843 my $rs = $schema->resultset('BooksInLibrary')->search(
844 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
846 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
848 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
849 join => 'owner', # single-rel manual prefetch
851 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
852 group_by => \[ '(me.id / ?), owner.id', 21 ],
853 having => \[ '?', 31 ],
858 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
861 # only limit, no offset, no order
862 if ($tests->{$limtype}{limit}) {
866 @{$tests->{$limtype}{limit}},
867 "$limtype: Unordered limit with select/group/having",
870 $rs->all if $can_run;
871 } "Grouped limit under $limtype";
874 # limit + offset, no order
875 if ($tests->{$limtype}{limit_offset}) {
878 my $subrs = $rs->search({}, { offset => 3 });
882 @{$tests->{$limtype}{limit_offset}},
883 "$limtype: Unordered limit+offset with select/group/having",
886 $subrs->all if $can_run;
887 } "Grouped limit+offset runs under $limtype";
890 # order + limit, no offset
891 $rs = $rs->search(undef, {
892 order_by => ( $limtype =~ /GenericSubQ/
893 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
894 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
898 if ($tests->{$limtype}{ordered_limit}) {
903 @{$tests->{$limtype}{ordered_limit}},
904 "$limtype: Ordered limit with select/group/having",
907 $rs->all if $can_run;
908 } "Grouped ordered limit runs under $limtype"
911 # order + limit + offset
912 if ($tests->{$limtype}{ordered_limit_offset}) {
914 my $subrs = $rs->search({}, { offset => 3 });
918 @{$tests->{$limtype}{ordered_limit_offset}},
919 "$limtype: Ordered limit+offset with select/group/having",
922 $subrs->all if $can_run;
923 } "Grouped ordered limit+offset runs under $limtype";
926 # complex prefetch on partial-fetch root with limit
927 my $pref_rs = $schema->resultset('Owners')->search({}, {
930 columns => 'name', # only the owner name, still prefetch all the books
932 ($limtype !~ /GenericSubQ/ ? () : (
933 # needs a same-table stable order to be happy
934 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
941 @{$tests->{$limtype}{limit_offset_prefetch}},
942 "$limtype: Prefetch with limit+offset",
943 ) if $tests->{$limtype}{limit_offset_prefetch};
945 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
947 } "Complex limited prefetch runs under $limtype";