8 use DBICTest ':DiffSQL';
10 my $schema = DBICTest->init_schema(
16 my $bs = $schema->source('BooksInLibrary');
17 $bs->remove_column('price');
20 data_type => 'integer',
25 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
27 my $where_string = '`me`.`title` = ? AND `source` != ? AND `source` = ?';
30 [ {} => 'kama sutra' ],
32 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
35 [ { sqlt_datatype => 'numeric' } => 11 ],
37 [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
46 [ { sqlt_datatype => 'int' } => 1 ],
47 [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
56 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
59 ON `owner`.`id` = `me`.`owner`
61 GROUP BY (`me`.`id` / ?), `owner`.`id`
70 [ { sqlt_datatype => 'integer' } => 4 ],
75 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
78 ON `owner`.`id` = `me`.`owner`
80 GROUP BY (`me`.`id` / ?), `owner`.`id`
90 [ { sqlt_datatype => 'integer' } => 4 ],
91 [ { sqlt_datatype => 'integer' } => 3 ],
96 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
99 ON `owner`.`id` = `me`.`owner`
101 GROUP BY (`me`.`id` / ?), `owner`.`id`
112 [ { sqlt_datatype => 'integer' } => 4 ],
115 ordered_limit_offset => [
117 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
119 JOIN `owners` `owner`
120 ON `owner`.`id` = `me`.`owner`
122 GROUP BY (`me`.`id` / ?), `owner`.`id`
134 [ { sqlt_datatype => 'integer' } => 4 ],
135 [ { sqlt_datatype => 'integer' } => 3 ],
138 limit_offset_prefetch => [
140 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
142 SELECT `me`.`name`, `me`.`id`
146 LEFT JOIN `books` `books`
147 ON `books`.`owner` = `me`.`id`
150 [ { sqlt_datatype => 'integer' } => 3 ],
151 [ { sqlt_datatype => 'integer' } => 1 ],
157 ordered_limit_offset => [
159 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
161 JOIN `owners` `owner`
162 ON `owner`.`id` = `me`.`owner`
164 GROUP BY (`me`.`id` / ?), `owner`.`id`
175 [ { sqlt_datatype => 'integer' } => 3 ],
176 [ { sqlt_datatype => 'integer' } => 4 ],
179 limit_offset_prefetch => [
181 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
183 SELECT `me`.`name`, `me`.`id`
187 LEFT JOIN `books` `books`
188 ON `books`.`owner` = `me`.`id`
191 [ { sqlt_datatype => 'integer' } => 1 ],
192 [ { sqlt_datatype => 'integer' } => 3 ],
198 ordered_limit_offset => [
200 SELECT SKIP ? FIRST ? `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
202 JOIN `owners` `owner`
203 ON `owner`.`id` = `me`.`owner`
205 GROUP BY (`me`.`id` / ?), `owner`.`id`
210 [ { sqlt_datatype => 'integer' } => 3 ],
211 [ { sqlt_datatype => 'integer' } => 4 ],
219 limit_offset_prefetch => [
221 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
223 SELECT SKIP ? FIRST ? `me`.`name`, `me`.`id`
226 LEFT JOIN `books` `books`
227 ON `books`.`owner` = `me`.`id`
230 [ { sqlt_datatype => 'integer' } => 1 ],
231 [ { sqlt_datatype => 'integer' } => 3 ],
237 ordered_limit_offset => [
239 SELECT FIRST ? SKIP ? `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
241 JOIN `owners` `owner`
242 ON `owner`.`id` = `me`.`owner`
244 GROUP BY (`me`.`id` / ?), `owner`.`id`
249 [ { sqlt_datatype => 'integer' } => 4 ],
250 [ { sqlt_datatype => 'integer' } => 3 ],
258 limit_offset_prefetch => [
260 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
262 SELECT FIRST ? SKIP ? `me`.`name`, `me`.`id`
265 LEFT JOIN `books` `books`
266 ON `books`.`owner` = `me`.`id`
269 [ { sqlt_datatype => 'integer' } => 3 ],
270 [ { sqlt_datatype => 'integer' } => 1 ],
275 RowNumberOver => do {
276 my $unordered_sql = "(
277 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
279 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROW_NUMBER() OVER() AS `rno__row__index`
281 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
283 JOIN `owners` `owner`
284 ON `owner`.`id` = `me`.`owner`
286 GROUP BY (`me`.`id` / ?), `owner`.`id`
290 WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
294 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
296 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROW_NUMBER() OVER( ORDER BY `ORDER__BY__001`, `ORDER__BY__002` ) AS `rno__row__index`
298 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`,
299 ? / ? AS `ORDER__BY__001`, ? AS `ORDER__BY__002`
301 JOIN `owners` `owner`
302 ON `owner`.`id` = `me`.`owner`
304 GROUP BY (`me`.`id` / ?), `owner`.`id`
308 WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
312 limit => [$unordered_sql,
318 [ { sqlt_datatype => 'integer' } => 1 ],
319 [ { sqlt_datatype => 'integer' } => 4 ],
322 limit_offset => [$unordered_sql,
328 [ { sqlt_datatype => 'integer' } => 4 ],
329 [ { sqlt_datatype => 'integer' } => 7 ],
332 ordered_limit => [$ordered_sql,
339 [ { sqlt_datatype => 'integer' } => 1 ],
340 [ { sqlt_datatype => 'integer' } => 4 ],
343 ordered_limit_offset => [$ordered_sql,
350 [ { sqlt_datatype => 'integer' } => 4 ],
351 [ { sqlt_datatype => 'integer' } => 7 ],
354 limit_offset_prefetch => [
356 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
358 SELECT `me`.`name`, `me`.`id`
360 SELECT `me`.`name`, `me`.`id`, ROW_NUMBER() OVER() AS `rno__row__index`
362 SELECT `me`.`name`, `me`.`id` FROM `owners` `me`
365 WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
367 LEFT JOIN `books` `books`
368 ON `books`.`owner` = `me`.`id`
371 [ { sqlt_datatype => 'integer' } => 2 ],
372 [ { sqlt_datatype => 'integer' } => 4 ],
379 my $limit_sql = sub {
381 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
383 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
385 JOIN `owners` `owner`
386 ON `owner`.`id` = `me`.`owner`
388 GROUP BY (`me`.`id` / ?), `owner`.`id`
397 limit => [ $limit_sql->(),
403 [ { sqlt_datatype => 'integer' } => 4 ],
408 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
410 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROWNUM AS `rownum__index`
412 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
414 JOIN `owners` `owner`
415 ON `owner`.`id` = `me`.`owner`
417 GROUP BY (`me`.`id` / ?), `owner`.`id`
421 WHERE `rownum__index` BETWEEN ? AND ?
428 [ { sqlt_datatype => 'integer' } => 4 ],
429 [ { sqlt_datatype => 'integer' } => 7 ],
432 ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
439 [ { sqlt_datatype => 'integer' } => 4 ],
442 ordered_limit_offset => [
444 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
446 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROWNUM AS `rownum__index`
448 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
450 JOIN `owners` `owner`
451 ON `owner`.`id` = `me`.`owner`
453 GROUP BY (`me`.`id` / ?), `owner`.`id`
459 WHERE `rownum__index` >= ?
467 [ { sqlt_datatype => 'integer' } => 7 ],
468 [ { sqlt_datatype => 'integer' } => 4 ],
471 limit_offset_prefetch => [
473 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
475 SELECT `me`.`name`, `me`.`id`
477 SELECT `me`.`name`, `me`.`id`, ROWNUM AS `rownum__index`
479 SELECT `me`.`name`, `me`.`id`
482 ) `me` WHERE `rownum__index` BETWEEN ? AND ?
484 LEFT JOIN `books` `books`
485 ON `books`.`owner` = `me`.`id`
488 [ { sqlt_datatype => 'integer' } => 2 ],
489 [ { sqlt_datatype => 'integer' } => 4 ],
498 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
500 JOIN `owners` `owner`
501 ON `owner`.`id` = `me`.`owner`
503 GROUP BY (`me`.`id` / ?), `owner`.`id`
505 FETCH FIRST 4 ROWS ONLY
516 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
518 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
520 JOIN `owners` `owner`
521 ON `owner`.`id` = `me`.`owner`
523 GROUP BY (`me`.`id` / ?), `owner`.`id`
526 FETCH FIRST 7 ROWS ONLY
528 ORDER BY `me`.`id` DESC
529 FETCH FIRST 4 ROWS ONLY
540 SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
542 JOIN `owners` `owner`
543 ON `owner`.`id` = `me`.`owner`
545 GROUP BY (`me`.`id` / ?), `owner`.`id`
548 FETCH FIRST 4 ROWS ONLY
558 ordered_limit_offset => [
560 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
562 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, `ORDER__BY__001`, `ORDER__BY__002`
564 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`
566 JOIN `owners` `owner`
567 ON `owner`.`id` = `me`.`owner`
569 GROUP BY (`me`.`id` / ?), `owner`.`id`
572 FETCH FIRST 7 ROWS ONLY
574 ORDER BY `ORDER__BY__001` DESC, `ORDER__BY__002` DESC
575 FETCH FIRST 4 ROWS ONLY
577 ORDER BY `ORDER__BY__001`, `ORDER__BY__002`
585 @{ dclone \@order_bind }, # without this is_deeply throws a fit
588 limit_offset_prefetch => [
590 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
592 SELECT `me`.`name`, `me`.`id`
594 SELECT `me`.`name`, `me`.`id`
597 FETCH FIRST 4 ROWS ONLY
599 ORDER BY `me`.`id` DESC
600 FETCH FIRST 3 ROWS ONLY
602 LEFT JOIN `books` `books`
603 ON `books`.`owner` = `me`.`id`
612 SELECT TOP 4 `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
614 JOIN `owners` `owner`
615 ON `owner`.`id` = `me`.`owner`
617 GROUP BY (`me`.`id` / ?), `owner`.`id`
629 SELECT TOP 4 `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
631 SELECT TOP 7 `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
633 JOIN `owners` `owner`
634 ON `owner`.`id` = `me`.`owner`
636 GROUP BY (`me`.`id` / ?), `owner`.`id`
640 ORDER BY `me`.`id` DESC
651 SELECT TOP 4 `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
653 JOIN `owners` `owner`
654 ON `owner`.`id` = `me`.`owner`
656 GROUP BY (`me`.`id` / ?), `owner`.`id`
668 ordered_limit_offset => [
670 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
672 SELECT TOP 4 `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, `ORDER__BY__001`, `ORDER__BY__002`
674 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`
676 JOIN `owners` `owner`
677 ON `owner`.`id` = `me`.`owner`
679 GROUP BY (`me`.`id` / ?), `owner`.`id`
683 ORDER BY `ORDER__BY__001` DESC, `ORDER__BY__002` DESC
685 ORDER BY `ORDER__BY__001`, `ORDER__BY__002`
693 @{ dclone \@order_bind }, # without this is_deeply throws a fit
696 limit_offset_prefetch => [
698 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
700 SELECT TOP 3 `me`.`name`, `me`.`id`
702 SELECT TOP 4 `me`.`name`, `me`.`id`
706 ORDER BY `me`.`id` DESC
708 LEFT JOIN `books` `books`
709 ON `books`.`owner` = `me`.`id`
718 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
720 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`, `me`.`price (#)`
722 JOIN `owners` `owner`
723 ON `owner`.`id` = `me`.`owner`
725 GROUP BY (`me`.`id` / ?), `owner`.`id`
730 FROM `books` `rownum__emulation`
732 ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NOT NULL )
735 `rownum__emulation`.`price (#)` > `me`.`price (#)`
737 `me`.`price (#)` IS NOT NULL
739 `rownum__emulation`.`price (#)` IS NOT NULL
744 `me`.`price (#)` = `rownum__emulation`.`price (#)`
746 ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NULL )
749 `rownum__emulation`.`id` < `me`.`id`
752 ORDER BY `me`.`price (#)` DESC, `me`.`id` ASC
759 [ { sqlt_datatype => 'integer' } => 4 ],
762 ordered_limit_offset => [
764 SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
766 SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`, `me`.`price (#)`
768 JOIN `owners` `owner`
769 ON `owner`.`id` = `me`.`owner`
771 GROUP BY (`me`.`id` / ?), `owner`.`id`
776 FROM `books` `rownum__emulation`
778 ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NOT NULL )
781 `rownum__emulation`.`price (#)` > `me`.`price (#)`
783 `me`.`price (#)` IS NOT NULL
785 `rownum__emulation`.`price (#)` IS NOT NULL
790 `me`.`price (#)` = `rownum__emulation`.`price (#)`
792 ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NULL )
795 `rownum__emulation`.`id` < `me`.`id`
798 ORDER BY `me`.`price (#)` DESC, `me`.`id` ASC
805 [ { sqlt_datatype => 'integer' } => 3 ],
806 [ { sqlt_datatype => 'integer' } => 6 ],
809 limit_offset_prefetch => [
811 SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`
813 SELECT `me`.`name`, `me`.`id`
815 SELECT `me`.`name`, `me`.`id`
821 FROM `owners` `rownum__emulation`
823 `rownum__emulation`.`name` < `me`.`name`
826 `me`.`name` = `rownum__emulation`.`name`
828 `rownum__emulation`.`id` > `me`.`id`
832 ORDER BY `me`.`name` ASC, `me`.`id` DESC
834 LEFT JOIN `books` `books`
835 ON `books`.`owner` = `me`.`id`
836 ORDER BY `me`.`name` ASC, `me`.`id` DESC
839 [ { sqlt_datatype => 'integer' } => 1 ],
840 [ { sqlt_datatype => 'integer' } => 3 ],
846 for my $limtype (sort keys %$tests) {
848 Test::Builder->new->is_passing or exit;
850 delete $schema->storage->_sql_maker->{_cached_syntax};
851 $schema->storage->_sql_maker->limit_dialect ($limtype);
853 # not deploying, so can't run
854 my $can_run = 0 && ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
856 # chained search is necessary to exercise the recursive {where} parser
857 my $rs = $schema->resultset('BooksInLibrary')->search(
858 { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
860 { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
862 columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
863 join => 'owner', # single-rel manual prefetch
865 '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
866 group_by => \[ '(`me`.`id` / ?), `owner`.`id`', 21 ],
867 having => \[ '?', 31 ],
872 # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
875 # only limit, no offset, no order
876 if ($tests->{$limtype}{limit}) {
880 @{$tests->{$limtype}{limit}},
881 "$limtype: Unordered limit with select/group/having",
884 $rs->all if $can_run;
885 } "Grouped limit under $limtype";
888 # limit + offset, no order
889 if ($tests->{$limtype}{limit_offset}) {
892 my $subrs = $rs->search({}, { offset => 3 });
896 @{$tests->{$limtype}{limit_offset}},
897 "$limtype: Unordered limit+offset with select/group/having",
900 $subrs->all if $can_run;
901 } "Grouped limit+offset runs under $limtype";
904 # order + limit, no offset
905 $rs = $rs->search(undef, {
906 order_by => ( $limtype =~ /GenericSubQ/
907 ? [ { -desc => 'price (#)' }, 'me.id', \[ '`owner`.`name` + ?', 'bah' ] ] # needs a same-table stable order to be happy
908 : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
912 if ($tests->{$limtype}{ordered_limit}) {
917 @{$tests->{$limtype}{ordered_limit}},
918 "$limtype: Ordered limit with select/group/having",
921 $rs->all if $can_run;
922 } "Grouped ordered limit runs under $limtype"
925 # order + limit + offset
926 if ($tests->{$limtype}{ordered_limit_offset}) {
928 my $subrs = $rs->search({}, { offset => 3 });
932 @{$tests->{$limtype}{ordered_limit_offset}},
933 "$limtype: Ordered limit+offset with select/group/having",
936 $subrs->all if $can_run;
937 } "Grouped ordered limit+offset runs under $limtype";
940 # complex prefetch on partial-fetch root with limit
941 my $pref_rs = $schema->resultset('Owners')->search({}, {
944 columns => 'name', # only the owner name, still prefetch all the books
946 ($limtype !~ /GenericSubQ/ ? () : (
947 # needs a same-table stable order to be happy
948 order_by => [ { -asc => 'me.name' }, \ '`me`.`id` DESC' ]
955 @{$tests->{$limtype}{limit_offset_prefetch}},
956 "$limtype: Prefetch with limit+offset",
957 ) if $tests->{$limtype}{limit_offset_prefetch};
959 is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
961 } "Complex limited prefetch runs under $limtype";