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
511 FETCH FIRST 7 ROWS ONLY
514 FETCH FIRST 4 ROWS ONLY
525 SELECT me.id, owner.id, owner.name, ? * ?, ?
528 ON owner.id = me.owner
529 WHERE source != ? AND me.title = ? AND source = ?
530 GROUP BY (me.id / ?), owner.id
533 FETCH FIRST 4 ROWS ONLY
543 ordered_limit_offset => [
545 SELECT me.id, owner__id, owner__name, bar, baz
547 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
549 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
552 ON owner.id = me.owner
553 WHERE source != ? AND me.title = ? AND source = ?
554 GROUP BY (me.id / ?), owner.id
557 FETCH FIRST 7 ROWS ONLY
559 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
560 FETCH FIRST 4 ROWS ONLY
562 ORDER BY ORDER__BY__001, ORDER__BY__002
570 @{ dclone \@order_bind }, # without this is_deeply throws a fit
573 limit_offset_prefetch => [
575 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
577 SELECT me.name, me.id
579 SELECT me.name, me.id
582 FETCH FIRST 4 ROWS ONLY
585 FETCH FIRST 3 ROWS ONLY
587 LEFT JOIN books books
588 ON books.owner = me.id
597 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
600 ON owner.id = me.owner
601 WHERE source != ? AND me.title = ? AND source = ?
602 GROUP BY (me.id / ?), owner.id
614 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
616 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
619 ON owner.id = me.owner
620 WHERE source != ? AND me.title = ? AND source = ?
621 GROUP BY (me.id / ?), owner.id
636 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
639 ON owner.id = me.owner
640 WHERE source != ? AND me.title = ? AND source = ?
641 GROUP BY (me.id / ?), owner.id
653 ordered_limit_offset => [
655 SELECT me.id, owner__id, owner__name, bar, baz
657 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
659 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
662 ON owner.id = me.owner
663 WHERE source != ? AND me.title = ? AND source = ?
664 GROUP BY (me.id / ?), owner.id
668 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
670 ORDER BY ORDER__BY__001, ORDER__BY__002
678 @{ dclone \@order_bind }, # without this is_deeply throws a fit
681 limit_offset_prefetch => [
683 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
685 SELECT TOP 3 me.name, me.id
687 SELECT TOP 4 me.name, me.id
693 LEFT JOIN books books
694 ON books.owner = me.id
703 SELECT me.id, owner__id, owner__name, bar, baz
705 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
708 ON owner.id = me.owner
709 WHERE source != ? AND me.title = ? AND source = ?
710 GROUP BY (me.id / ?), owner.id
715 FROM books rownum__emulation
717 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
720 rownum__emulation.price > me.price
724 rownum__emulation.price IS NOT NULL
729 me.price = rownum__emulation.price
731 ( me.price IS NULL AND rownum__emulation.price IS NULL )
734 rownum__emulation.id < me.id
737 ORDER BY me.price DESC, me.id ASC
744 [ { sqlt_datatype => 'integer' } => 4 ],
747 ordered_limit_offset => [
749 SELECT me.id, owner__id, owner__name, bar, baz
751 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
754 ON owner.id = me.owner
755 WHERE source != ? AND me.title = ? AND source = ?
756 GROUP BY (me.id / ?), owner.id
761 FROM books rownum__emulation
763 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
766 rownum__emulation.price > me.price
770 rownum__emulation.price IS NOT NULL
775 me.price = rownum__emulation.price
777 ( me.price IS NULL AND rownum__emulation.price IS NULL )
780 rownum__emulation.id < me.id
783 ORDER BY me.price DESC, me.id ASC
790 [ { sqlt_datatype => 'integer' } => 3 ],
791 [ { sqlt_datatype => 'integer' } => 6 ],
794 limit_offset_prefetch => [
796 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
798 SELECT me.name, me.id
800 SELECT me.name, me.id
806 FROM owners rownum__emulation
808 rownum__emulation.name < me.name
811 me.name = rownum__emulation.name
813 rownum__emulation.id > me.id
817 ORDER BY me.name ASC, me.id DESC
819 LEFT JOIN books books
820 ON books.owner = me.id
821 ORDER BY me.name ASC, me.id DESC
824 [ { sqlt_datatype => 'integer' } => 1 ],
825 [ { sqlt_datatype => 'integer' } => 3 ],
831 for my $limtype (sort keys %$tests) {
833 Test::Builder->new->is_passing or exit;
835 delete $schema->storage->_sql_maker->{_cached_syntax};
836 $schema->storage->_sql_maker->limit_dialect ($limtype);
838 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
840 # chained search is necessary to exercise the recursive {where} parser
841 my $rs = $schema->resultset('BooksInLibrary')->search(
842 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
844 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
846 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
847 join => 'owner', # single-rel manual prefetch
849 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
850 group_by => \[ '(me.id / ?), owner.id', 21 ],
851 having => \[ '?', 31 ],
856 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
859 # only limit, no offset, no order
860 if ($tests->{$limtype}{limit}) {
864 @{$tests->{$limtype}{limit}},
865 "$limtype: Unordered limit with select/group/having",
868 $rs->all if $can_run;
869 } "Grouped limit under $limtype";
872 # limit + offset, no order
873 if ($tests->{$limtype}{limit_offset}) {
876 my $subrs = $rs->search({}, { offset => 3 });
880 @{$tests->{$limtype}{limit_offset}},
881 "$limtype: Unordered limit+offset with select/group/having",
884 $subrs->all if $can_run;
885 } "Grouped limit+offset runs under $limtype";
888 # order + limit, no offset
889 $rs = $rs->search(undef, {
890 order_by => ( $limtype =~ /GenericSubQ/
891 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
892 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
896 if ($tests->{$limtype}{ordered_limit}) {
901 @{$tests->{$limtype}{ordered_limit}},
902 "$limtype: Ordered limit with select/group/having",
905 $rs->all if $can_run;
906 } "Grouped ordered limit runs under $limtype"
909 # order + limit + offset
910 if ($tests->{$limtype}{ordered_limit_offset}) {
912 my $subrs = $rs->search({}, { offset => 3 });
916 @{$tests->{$limtype}{ordered_limit_offset}},
917 "$limtype: Ordered limit+offset with select/group/having",
920 $subrs->all if $can_run;
921 } "Grouped ordered limit+offset runs under $limtype";
924 # complex prefetch on partial-fetch root with limit
925 my $pref_rs = $schema->resultset('Owners')->search({}, {
928 columns => 'name', # only the owner name, still prefetch all the books
930 ($limtype !~ /GenericSubQ/ ? () : (
931 # needs a same-table stable order to be happy
932 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
939 @{$tests->{$limtype}{limit_offset_prefetch}},
940 "$limtype: Prefetch with limit+offset",
941 ) if $tests->{$limtype}{limit_offset_prefetch};
943 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
945 } "Complex limited prefetch runs under $limtype";