9 use DBIC::SqlMakerTest;
11 my $schema = DBICTest->init_schema;
12 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
16 [ {} => 'kama sutra' ],
17 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
20 [ { sqlt_datatype => 'numeric' } => 11 ],
22 [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
31 [ { sqlt_datatype => 'int' } => 1 ],
32 [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
41 SELECT me.id, owner.id, owner.name, ? * ?, ?
44 ON owner.id = me.owner
45 WHERE source != ? AND me.title = ? AND source = ?
46 GROUP BY (me.id / ?), owner.id
55 [ { sqlt_datatype => 'integer' } => 4 ],
60 SELECT me.id, owner.id, owner.name, ? * ?, ?
63 ON owner.id = me.owner
64 WHERE source != ? AND me.title = ? AND source = ?
65 GROUP BY (me.id / ?), owner.id
75 [ { sqlt_datatype => 'integer' } => 4 ],
76 [ { sqlt_datatype => 'integer' } => 3 ],
81 SELECT me.id, owner.id, owner.name, ? * ?, ?
84 ON owner.id = me.owner
85 WHERE source != ? AND me.title = ? AND source = ?
86 GROUP BY (me.id / ?), owner.id
97 [ { sqlt_datatype => 'integer' } => 4 ],
100 ordered_limit_offset => [
102 SELECT me.id, owner.id, owner.name, ? * ?, ?
105 ON owner.id = me.owner
106 WHERE source != ? AND me.title = ? AND source = ?
107 GROUP BY (me.id / ?), owner.id
119 [ { sqlt_datatype => 'integer' } => 4 ],
120 [ { sqlt_datatype => 'integer' } => 3 ],
123 limit_offset_prefetch => [
125 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
127 SELECT me.name, me.id
131 LEFT JOIN books books
132 ON books.owner = me.id
135 [ { sqlt_datatype => 'integer' } => 3 ],
136 [ { sqlt_datatype => 'integer' } => 1 ],
142 ordered_limit_offset => [
144 SELECT me.id, owner.id, owner.name, ? * ?, ?
147 ON owner.id = me.owner
148 WHERE source != ? AND me.title = ? AND source = ?
149 GROUP BY (me.id / ?), owner.id
160 [ { sqlt_datatype => 'integer' } => 3 ],
161 [ { sqlt_datatype => 'integer' } => 4 ],
164 limit_offset_prefetch => [
166 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
168 SELECT me.name, me.id
172 LEFT JOIN books books
173 ON books.owner = me.id
176 [ { sqlt_datatype => 'integer' } => 1 ],
177 [ { sqlt_datatype => 'integer' } => 3 ],
183 ordered_limit_offset => [
185 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
188 ON owner.id = me.owner
189 WHERE source != ? AND me.title = ? AND source = ?
190 GROUP BY (me.id / ?), owner.id
195 [ { sqlt_datatype => 'integer' } => 3 ],
196 [ { sqlt_datatype => 'integer' } => 4 ],
204 limit_offset_prefetch => [
206 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
208 SELECT SKIP ? FIRST ? me.name, me.id
211 LEFT JOIN books books
212 ON books.owner = me.id
215 [ { sqlt_datatype => 'integer' } => 1 ],
216 [ { sqlt_datatype => 'integer' } => 3 ],
222 ordered_limit_offset => [
224 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
227 ON owner.id = me.owner
228 WHERE source != ? AND me.title = ? AND source = ?
229 GROUP BY (me.id / ?), owner.id
234 [ { sqlt_datatype => 'integer' } => 4 ],
235 [ { sqlt_datatype => 'integer' } => 3 ],
243 limit_offset_prefetch => [
245 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
247 SELECT FIRST ? SKIP ? me.name, me.id
250 LEFT JOIN books books
251 ON books.owner = me.id
254 [ { sqlt_datatype => 'integer' } => 3 ],
255 [ { sqlt_datatype => 'integer' } => 1 ],
260 RowNumberOver => do {
261 my $unordered_sql = '(
262 SELECT me.id, owner__id, owner__name, bar, baz
264 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
266 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
269 ON owner.id = me.owner
270 WHERE source != ? AND me.title = ? AND source = ?
271 GROUP BY (me.id / ?), owner.id
275 WHERE rno__row__index >= ? AND rno__row__index <= ?
279 SELECT me.id, owner__id, owner__name, bar, baz
281 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
283 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
284 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
287 ON owner.id = me.owner
288 WHERE source != ? AND me.title = ? AND source = ?
289 GROUP BY (me.id / ?), owner.id
293 WHERE rno__row__index >= ? AND rno__row__index <= ?
297 limit => [$unordered_sql,
303 [ { sqlt_datatype => 'integer' } => 1 ],
304 [ { sqlt_datatype => 'integer' } => 4 ],
307 limit_offset => [$unordered_sql,
313 [ { sqlt_datatype => 'integer' } => 4 ],
314 [ { sqlt_datatype => 'integer' } => 7 ],
317 ordered_limit => [$ordered_sql,
324 [ { sqlt_datatype => 'integer' } => 1 ],
325 [ { sqlt_datatype => 'integer' } => 4 ],
328 ordered_limit_offset => [$ordered_sql,
335 [ { sqlt_datatype => 'integer' } => 4 ],
336 [ { sqlt_datatype => 'integer' } => 7 ],
339 limit_offset_prefetch => [
341 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
343 SELECT me.name, me.id
345 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
347 SELECT me.name, me.id FROM owners me
350 WHERE rno__row__index >= ? AND rno__row__index <= ?
352 LEFT JOIN books books
353 ON books.owner = me.id
356 [ { sqlt_datatype => 'integer' } => 2 ],
357 [ { sqlt_datatype => 'integer' } => 4 ],
364 my $limit_sql = sub {
366 SELECT me.id, owner__id, owner__name, bar, baz
368 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
371 ON owner.id = me.owner
372 WHERE source != ? AND me.title = ? AND source = ?
373 GROUP BY (me.id / ?), owner.id
382 limit => [ $limit_sql->(),
388 [ { sqlt_datatype => 'integer' } => 4 ],
393 SELECT me.id, owner__id, owner__name, bar, baz
395 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
397 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
400 ON owner.id = me.owner
401 WHERE source != ? AND me.title = ? AND source = ?
402 GROUP BY (me.id / ?), owner.id
406 WHERE rownum__index BETWEEN ? AND ?
413 [ { sqlt_datatype => 'integer' } => 4 ],
414 [ { sqlt_datatype => 'integer' } => 7 ],
417 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
424 [ { sqlt_datatype => 'integer' } => 4 ],
427 ordered_limit_offset => [
429 SELECT me.id, owner__id, owner__name, bar, baz
431 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
433 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
436 ON owner.id = me.owner
437 WHERE source != ? AND me.title = ? AND source = ?
438 GROUP BY (me.id / ?), owner.id
444 WHERE rownum__index >= ?
452 [ { sqlt_datatype => 'integer' } => 7 ],
453 [ { sqlt_datatype => 'integer' } => 4 ],
456 limit_offset_prefetch => [
458 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
460 SELECT me.name, me.id
462 SELECT me.name, me.id, ROWNUM rownum__index
464 SELECT me.name, me.id
467 ) me WHERE rownum__index BETWEEN ? AND ?
469 LEFT JOIN books books
470 ON books.owner = me.id
473 [ { sqlt_datatype => 'integer' } => 2 ],
474 [ { sqlt_datatype => 'integer' } => 4 ],
483 SELECT me.id, owner.id, owner.name, ? * ?, ?
486 ON owner.id = me.owner
487 WHERE source != ? AND me.title = ? AND source = ?
488 GROUP BY (me.id / ?), owner.id
490 FETCH FIRST 4 ROWS ONLY
501 SELECT me.id, owner__id, owner__name, bar, baz
503 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
506 ON owner.id = me.owner
507 WHERE source != ? AND me.title = ? AND source = ?
508 GROUP BY (me.id / ?), owner.id
510 FETCH FIRST 7 ROWS ONLY
512 FETCH FIRST 4 ROWS ONLY
523 SELECT me.id, owner.id, owner.name, ? * ?, ?
526 ON owner.id = me.owner
527 WHERE source != ? AND me.title = ? AND source = ?
528 GROUP BY (me.id / ?), owner.id
531 FETCH FIRST 4 ROWS ONLY
541 ordered_limit_offset => [
543 SELECT me.id, owner__id, owner__name, bar, baz
545 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
547 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
550 ON owner.id = me.owner
551 WHERE source != ? AND me.title = ? AND source = ?
552 GROUP BY (me.id / ?), owner.id
555 FETCH FIRST 7 ROWS ONLY
557 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
558 FETCH FIRST 4 ROWS ONLY
560 ORDER BY ORDER__BY__001, ORDER__BY__002
568 @{ dclone \@order_bind }, # without this is_deeply throws a fit
571 limit_offset_prefetch => [
573 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
575 SELECT me.name, me.id
577 SELECT me.name, me.id
580 FETCH FIRST 4 ROWS ONLY
583 FETCH FIRST 3 ROWS ONLY
585 LEFT JOIN books books
586 ON books.owner = me.id
595 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
598 ON owner.id = me.owner
599 WHERE source != ? AND me.title = ? AND source = ?
600 GROUP BY (me.id / ?), owner.id
612 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
614 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
617 ON owner.id = me.owner
618 WHERE source != ? AND me.title = ? AND source = ?
619 GROUP BY (me.id / ?), owner.id
634 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
637 ON owner.id = me.owner
638 WHERE source != ? AND me.title = ? AND source = ?
639 GROUP BY (me.id / ?), owner.id
651 ordered_limit_offset => [
653 SELECT me.id, owner__id, owner__name, bar, baz
655 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
657 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
660 ON owner.id = me.owner
661 WHERE source != ? AND me.title = ? AND source = ?
662 GROUP BY (me.id / ?), owner.id
666 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
668 ORDER BY ORDER__BY__001, ORDER__BY__002
676 @{ dclone \@order_bind }, # without this is_deeply throws a fit
679 limit_offset_prefetch => [
681 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
683 SELECT TOP 3 me.name, me.id
685 SELECT TOP 4 me.name, me.id
691 LEFT JOIN books books
692 ON books.owner = me.id
701 SELECT me.id, owner__id, owner__name, bar, baz
703 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
706 ON owner.id = me.owner
707 WHERE source != ? AND me.title = ? AND source = ?
708 GROUP BY (me.id / ?), owner.id
713 FROM books rownum__emulation
715 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
718 rownum__emulation.price > me.price
722 rownum__emulation.price IS NOT NULL
727 me.price = rownum__emulation.price
729 ( me.price IS NULL AND rownum__emulation.price IS NULL )
732 rownum__emulation.id < me.id
735 ORDER BY me.price DESC, me.id ASC
742 [ { sqlt_datatype => 'integer' } => 4 ],
745 ordered_limit_offset => [
747 SELECT me.id, owner__id, owner__name, bar, baz
749 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
752 ON owner.id = me.owner
753 WHERE source != ? AND me.title = ? AND source = ?
754 GROUP BY (me.id / ?), owner.id
759 FROM books rownum__emulation
761 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
764 rownum__emulation.price > me.price
768 rownum__emulation.price IS NOT NULL
773 me.price = rownum__emulation.price
775 ( me.price IS NULL AND rownum__emulation.price IS NULL )
778 rownum__emulation.id < me.id
781 ORDER BY me.price DESC, me.id ASC
788 [ { sqlt_datatype => 'integer' } => 3 ],
789 [ { sqlt_datatype => 'integer' } => 6 ],
792 limit_offset_prefetch => [
794 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
796 SELECT me.name, me.id
798 SELECT me.name, me.id
804 FROM owners rownum__emulation
806 rownum__emulation.name < me.name
809 me.name = rownum__emulation.name
811 rownum__emulation.id > me.id
815 ORDER BY me.name ASC, me.id DESC
817 LEFT JOIN books books
818 ON books.owner = me.id
819 ORDER BY me.name ASC, me.id DESC
822 [ { sqlt_datatype => 'integer' } => 1 ],
823 [ { sqlt_datatype => 'integer' } => 3 ],
829 for my $limtype (sort keys %$tests) {
831 Test::Builder->new->is_passing or exit;
833 delete $schema->storage->_sql_maker->{_cached_syntax};
834 $schema->storage->_sql_maker->limit_dialect ($limtype);
836 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
838 # chained search is necessary to exercise the recursive {where} parser
839 my $rs = $schema->resultset('BooksInLibrary')->search(
840 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
842 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
844 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
845 join => 'owner', # single-rel manual prefetch
847 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
848 group_by => \[ '(me.id / ?), owner.id', 21 ],
849 having => \[ '?', 31 ],
854 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
857 # only limit, no offset, no order
858 if ($tests->{$limtype}{limit}) {
862 @{$tests->{$limtype}{limit}},
863 "$limtype: Unordered limit with select/group/having",
866 $rs->all if $can_run;
867 } "Grouped limit under $limtype";
870 # limit + offset, no order
871 if ($tests->{$limtype}{limit_offset}) {
874 my $subrs = $rs->search({}, { offset => 3 });
878 @{$tests->{$limtype}{limit_offset}},
879 "$limtype: Unordered limit+offset with select/group/having",
882 $subrs->all if $can_run;
883 } "Grouped limit+offset runs under $limtype";
886 # order + limit, no offset
887 $rs = $rs->search(undef, {
888 order_by => ( $limtype =~ /GenericSubQ/
889 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
890 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
894 if ($tests->{$limtype}{ordered_limit}) {
899 @{$tests->{$limtype}{ordered_limit}},
900 "$limtype: Ordered limit with select/group/having",
903 $rs->all if $can_run;
904 } "Grouped ordered limit runs under $limtype"
907 # order + limit + offset
908 if ($tests->{$limtype}{ordered_limit_offset}) {
910 my $subrs = $rs->search({}, { offset => 3 });
914 @{$tests->{$limtype}{ordered_limit_offset}},
915 "$limtype: Ordered limit+offset with select/group/having",
918 $subrs->all if $can_run;
919 } "Grouped ordered limit+offset runs under $limtype";
922 # complex prefetch on partial-fetch root with limit
923 my $pref_rs = $schema->resultset('Owners')->search({}, {
926 columns => 'name', # only the owner name, still prefetch all the books
928 ($limtype !~ /GenericSubQ/ ? () : (
929 # needs a same-table stable order to be happy
930 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
937 @{$tests->{$limtype}{limit_offset_prefetch}},
938 "$limtype: Prefetch with limit+offset",
939 ) if $tests->{$limtype}{limit_offset_prefetch};
941 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
943 } "Complex limited prefetch runs under $limtype";