6 local $TODO = 'Temporarily todo-ed for dq2eb';
12 use DBIC::SqlMakerTest;
14 my $schema = DBICTest->init_schema;
15 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
19 [ {} => '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 ],
44 SELECT me.id, owner.id, owner.name, ? * ?, ?
47 ON owner.id = me.owner
48 WHERE source != ? AND me.title = ? AND source = ?
49 GROUP BY (me.id / ?), owner.id
58 [ { sqlt_datatype => 'integer' } => 4 ],
63 SELECT me.id, owner.id, owner.name, ? * ?, ?
66 ON owner.id = me.owner
67 WHERE source != ? AND me.title = ? AND source = ?
68 GROUP BY (me.id / ?), owner.id
78 [ { sqlt_datatype => 'integer' } => 4 ],
79 [ { sqlt_datatype => 'integer' } => 3 ],
84 SELECT me.id, owner.id, owner.name, ? * ?, ?
87 ON owner.id = me.owner
88 WHERE source != ? AND me.title = ? AND source = ?
89 GROUP BY (me.id / ?), owner.id
100 [ { sqlt_datatype => 'integer' } => 4 ],
103 ordered_limit_offset => [
105 SELECT me.id, owner.id, owner.name, ? * ?, ?
108 ON owner.id = me.owner
109 WHERE source != ? AND me.title = ? AND source = ?
110 GROUP BY (me.id / ?), owner.id
122 [ { sqlt_datatype => 'integer' } => 4 ],
123 [ { sqlt_datatype => 'integer' } => 3 ],
126 limit_offset_prefetch => [
128 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
130 SELECT me.name, me.id
134 LEFT JOIN books books
135 ON books.owner = me.id
138 [ { sqlt_datatype => 'integer' } => 3 ],
139 [ { sqlt_datatype => 'integer' } => 1 ],
145 ordered_limit_offset => [
147 SELECT me.id, owner.id, owner.name, ? * ?, ?
150 ON owner.id = me.owner
151 WHERE source != ? AND me.title = ? AND source = ?
152 GROUP BY (me.id / ?), owner.id
163 [ { sqlt_datatype => 'integer' } => 3 ],
164 [ { sqlt_datatype => 'integer' } => 4 ],
167 limit_offset_prefetch => [
169 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
171 SELECT me.name, me.id
175 LEFT JOIN books books
176 ON books.owner = me.id
179 [ { sqlt_datatype => 'integer' } => 1 ],
180 [ { sqlt_datatype => 'integer' } => 3 ],
186 ordered_limit_offset => [
188 SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
191 ON owner.id = me.owner
192 WHERE source != ? AND me.title = ? AND source = ?
193 GROUP BY (me.id / ?), owner.id
198 [ { sqlt_datatype => 'integer' } => 3 ],
199 [ { sqlt_datatype => 'integer' } => 4 ],
207 limit_offset_prefetch => [
209 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
211 SELECT SKIP ? FIRST ? me.name, me.id
214 LEFT JOIN books books
215 ON books.owner = me.id
218 [ { sqlt_datatype => 'integer' } => 1 ],
219 [ { sqlt_datatype => 'integer' } => 3 ],
225 ordered_limit_offset => [
227 SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
230 ON owner.id = me.owner
231 WHERE source != ? AND me.title = ? AND source = ?
232 GROUP BY (me.id / ?), owner.id
237 [ { sqlt_datatype => 'integer' } => 4 ],
238 [ { sqlt_datatype => 'integer' } => 3 ],
246 limit_offset_prefetch => [
248 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
250 SELECT FIRST ? SKIP ? me.name, me.id
253 LEFT JOIN books books
254 ON books.owner = me.id
257 [ { sqlt_datatype => 'integer' } => 3 ],
258 [ { sqlt_datatype => 'integer' } => 1 ],
263 RowNumberOver => do {
264 my $unordered_sql = '(
265 SELECT me.id, owner__id, owner__name, bar, baz
267 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
269 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
272 ON owner.id = me.owner
273 WHERE source != ? AND me.title = ? AND source = ?
274 GROUP BY (me.id / ?), owner.id
278 WHERE rno__row__index >= ? AND rno__row__index <= ?
282 SELECT me.id, owner__id, owner__name, bar, baz
284 SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
286 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
287 ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
290 ON owner.id = me.owner
291 WHERE source != ? AND me.title = ? AND source = ?
292 GROUP BY (me.id / ?), owner.id
296 WHERE rno__row__index >= ? AND rno__row__index <= ?
300 limit => [$unordered_sql,
306 [ { sqlt_datatype => 'integer' } => 1 ],
307 [ { sqlt_datatype => 'integer' } => 4 ],
310 limit_offset => [$unordered_sql,
316 [ { sqlt_datatype => 'integer' } => 4 ],
317 [ { sqlt_datatype => 'integer' } => 7 ],
320 ordered_limit => [$ordered_sql,
327 [ { sqlt_datatype => 'integer' } => 1 ],
328 [ { sqlt_datatype => 'integer' } => 4 ],
331 ordered_limit_offset => [$ordered_sql,
338 [ { sqlt_datatype => 'integer' } => 4 ],
339 [ { sqlt_datatype => 'integer' } => 7 ],
342 limit_offset_prefetch => [
344 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
346 SELECT me.name, me.id
348 SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
350 SELECT me.name, me.id FROM owners me
353 WHERE rno__row__index >= ? AND rno__row__index <= ?
355 LEFT JOIN books books
356 ON books.owner = me.id
359 [ { sqlt_datatype => 'integer' } => 2 ],
360 [ { sqlt_datatype => 'integer' } => 4 ],
367 my $limit_sql = sub {
369 SELECT me.id, owner__id, owner__name, bar, baz
371 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
374 ON owner.id = me.owner
375 WHERE source != ? AND me.title = ? AND source = ?
376 GROUP BY (me.id / ?), owner.id
385 limit => [ $limit_sql->(),
391 [ { sqlt_datatype => 'integer' } => 4 ],
396 SELECT me.id, owner__id, owner__name, bar, baz
398 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
400 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
403 ON owner.id = me.owner
404 WHERE source != ? AND me.title = ? AND source = ?
405 GROUP BY (me.id / ?), owner.id
409 WHERE rownum__index BETWEEN ? AND ?
416 [ { sqlt_datatype => 'integer' } => 4 ],
417 [ { sqlt_datatype => 'integer' } => 7 ],
420 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
427 [ { sqlt_datatype => 'integer' } => 4 ],
430 ordered_limit_offset => [
432 SELECT me.id, owner__id, owner__name, bar, baz
434 SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
436 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
439 ON owner.id = me.owner
440 WHERE source != ? AND me.title = ? AND source = ?
441 GROUP BY (me.id / ?), owner.id
447 WHERE rownum__index >= ?
455 [ { sqlt_datatype => 'integer' } => 7 ],
456 [ { sqlt_datatype => 'integer' } => 4 ],
459 limit_offset_prefetch => [
461 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
463 SELECT me.name, me.id
465 SELECT me.name, me.id, ROWNUM rownum__index
467 SELECT me.name, me.id
470 ) me WHERE rownum__index BETWEEN ? AND ?
472 LEFT JOIN books books
473 ON books.owner = me.id
476 [ { sqlt_datatype => 'integer' } => 2 ],
477 [ { sqlt_datatype => 'integer' } => 4 ],
486 SELECT me.id, owner.id, owner.name, ? * ?, ?
489 ON owner.id = me.owner
490 WHERE source != ? AND me.title = ? AND source = ?
491 GROUP BY (me.id / ?), owner.id
493 FETCH FIRST 4 ROWS ONLY
504 SELECT me.id, owner__id, owner__name, bar, baz
506 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
509 ON owner.id = me.owner
510 WHERE source != ? AND me.title = ? AND source = ?
511 GROUP BY (me.id / ?), owner.id
513 FETCH FIRST 7 ROWS ONLY
515 FETCH FIRST 4 ROWS ONLY
526 SELECT me.id, owner.id, owner.name, ? * ?, ?
529 ON owner.id = me.owner
530 WHERE source != ? AND me.title = ? AND source = ?
531 GROUP BY (me.id / ?), owner.id
534 FETCH FIRST 4 ROWS ONLY
544 ordered_limit_offset => [
546 SELECT me.id, owner__id, owner__name, bar, baz
548 SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
550 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
553 ON owner.id = me.owner
554 WHERE source != ? AND me.title = ? AND source = ?
555 GROUP BY (me.id / ?), owner.id
558 FETCH FIRST 7 ROWS ONLY
560 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
561 FETCH FIRST 4 ROWS ONLY
563 ORDER BY ORDER__BY__001, ORDER__BY__002
571 @{ dclone \@order_bind }, # without this is_deeply throws a fit
574 limit_offset_prefetch => [
576 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
578 SELECT me.name, me.id
580 SELECT me.name, me.id
583 FETCH FIRST 4 ROWS ONLY
586 FETCH FIRST 3 ROWS ONLY
588 LEFT JOIN books books
589 ON books.owner = me.id
598 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
601 ON owner.id = me.owner
602 WHERE source != ? AND me.title = ? AND source = ?
603 GROUP BY (me.id / ?), owner.id
615 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
617 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
620 ON owner.id = me.owner
621 WHERE source != ? AND me.title = ? AND source = ?
622 GROUP BY (me.id / ?), owner.id
637 SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
640 ON owner.id = me.owner
641 WHERE source != ? AND me.title = ? AND source = ?
642 GROUP BY (me.id / ?), owner.id
654 ordered_limit_offset => [
656 SELECT me.id, owner__id, owner__name, bar, baz
658 SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
660 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
663 ON owner.id = me.owner
664 WHERE source != ? AND me.title = ? AND source = ?
665 GROUP BY (me.id / ?), owner.id
669 ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
671 ORDER BY ORDER__BY__001, ORDER__BY__002
679 @{ dclone \@order_bind }, # without this is_deeply throws a fit
682 limit_offset_prefetch => [
684 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
686 SELECT TOP 3 me.name, me.id
688 SELECT TOP 4 me.name, me.id
694 LEFT JOIN books books
695 ON books.owner = me.id
704 SELECT me.id, owner__id, owner__name, bar, baz
706 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
709 ON owner.id = me.owner
710 WHERE source != ? AND me.title = ? AND source = ?
711 GROUP BY (me.id / ?), owner.id
716 FROM books rownum__emulation
718 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
721 rownum__emulation.price > me.price
725 rownum__emulation.price IS NOT NULL
730 me.price = rownum__emulation.price
732 ( me.price IS NULL AND rownum__emulation.price IS NULL )
735 rownum__emulation.id < me.id
738 ORDER BY me.price DESC, me.id ASC
745 [ { sqlt_datatype => 'integer' } => 4 ],
748 ordered_limit_offset => [
750 SELECT me.id, owner__id, owner__name, bar, baz
752 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
755 ON owner.id = me.owner
756 WHERE source != ? AND me.title = ? AND source = ?
757 GROUP BY (me.id / ?), owner.id
762 FROM books rownum__emulation
764 ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
767 rownum__emulation.price > me.price
771 rownum__emulation.price IS NOT NULL
776 me.price = rownum__emulation.price
778 ( me.price IS NULL AND rownum__emulation.price IS NULL )
781 rownum__emulation.id < me.id
784 ORDER BY me.price DESC, me.id ASC
791 [ { sqlt_datatype => 'integer' } => 3 ],
792 [ { sqlt_datatype => 'integer' } => 6 ],
795 limit_offset_prefetch => [
797 SELECT me.name, books.id, books.source, books.owner, books.title, books.price
799 SELECT me.name, me.id
801 SELECT me.name, me.id
807 FROM owners rownum__emulation
809 rownum__emulation.name < me.name
812 me.name = rownum__emulation.name
814 rownum__emulation.id > me.id
818 ORDER BY me.name ASC, me.id DESC
820 LEFT JOIN books books
821 ON books.owner = me.id
822 ORDER BY me.name ASC, me.id DESC
825 [ { sqlt_datatype => 'integer' } => 1 ],
826 [ { sqlt_datatype => 'integer' } => 3 ],
832 for my $limtype (sort keys %$tests) {
834 Test::Builder->new->is_passing or exit;
836 delete $schema->storage->_sql_maker->{_cached_syntax};
837 $schema->storage->_sql_maker->limit_dialect ($limtype);
839 my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
841 # chained search is necessary to exercise the recursive {where} parser
842 my $rs = $schema->resultset('BooksInLibrary')->search(
843 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
845 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
847 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
848 join => 'owner', # single-rel manual prefetch
850 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
851 group_by => \[ '(me.id / ?), owner.id', 21 ],
852 having => \[ '?', 31 ],
857 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
860 # only limit, no offset, no order
861 if ($tests->{$limtype}{limit}) {
865 @{$tests->{$limtype}{limit}},
866 "$limtype: Unordered limit with select/group/having",
869 $rs->all if $can_run;
870 } "Grouped limit under $limtype";
873 # limit + offset, no order
874 if ($tests->{$limtype}{limit_offset}) {
877 my $subrs = $rs->search({}, { offset => 3 });
881 @{$tests->{$limtype}{limit_offset}},
882 "$limtype: Unordered limit+offset with select/group/having",
885 $subrs->all if $can_run;
886 } "Grouped limit+offset runs under $limtype";
889 # order + limit, no offset
890 $rs = $rs->search(undef, {
891 order_by => ( $limtype =~ /GenericSubQ/
892 ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy
893 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
897 if ($tests->{$limtype}{ordered_limit}) {
902 @{$tests->{$limtype}{ordered_limit}},
903 "$limtype: Ordered limit with select/group/having",
906 $rs->all if $can_run;
907 } "Grouped ordered limit runs under $limtype"
910 # order + limit + offset
911 if ($tests->{$limtype}{ordered_limit_offset}) {
913 my $subrs = $rs->search({}, { offset => 3 });
917 @{$tests->{$limtype}{ordered_limit_offset}},
918 "$limtype: Ordered limit+offset with select/group/having",
921 $subrs->all if $can_run;
922 } "Grouped ordered limit+offset runs under $limtype";
925 # complex prefetch on partial-fetch root with limit
926 my $pref_rs = $schema->resultset('Owners')->search({}, {
929 columns => 'name', # only the owner name, still prefetch all the books
931 ($limtype !~ /GenericSubQ/ ? () : (
932 # needs a same-table stable order to be happy
933 order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
940 @{$tests->{$limtype}{limit_offset_prefetch}},
941 "$limtype: Prefetch with limit+offset",
942 ) if $tests->{$limtype}{limit_offset_prefetch};
944 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
946 } "Complex limited prefetch runs under $limtype";