use warnings;
use Test::More;
+use Test::Exception;
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
my $schema = DBICTest->init_schema;
+my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
my $attr = {};
my @where_bind = (
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?
[ { sqlt_datatype => 'integer' } => 3 ],
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT me.name, me.id
+ FROM owners me
+ LIMIT ? OFFSET ?
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 3 ],
+ [ { sqlt_datatype => 'integer' } => 1 ],
+ ]
+ ],
},
LimitXY => {
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?, ?
[ { sqlt_datatype => 'integer' } => 4 ],
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT me.name, me.id
+ FROM owners me
+ LIMIT ?,?
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 1 ],
+ [ { sqlt_datatype => 'integer' } => 3 ],
+ ]
+ ],
},
SkipFirst => {
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
)',
@order_bind,
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT SKIP ? FIRST ? me.name, me.id
+ FROM owners me
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 1 ],
+ [ { sqlt_datatype => 'integer' } => 3 ],
+ ]
+ ],
},
FirstSkip => {
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
)',
@order_bind,
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT FIRST ? SKIP ? me.name, me.id
+ FROM owners me
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 3 ],
+ [ { sqlt_datatype => 'integer' } => 1 ],
+ ]
+ ],
},
RowNumberOver => do {
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
) me
) me
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
) me
) me
[ { sqlt_datatype => 'integer' } => 7 ],
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT me.name, me.id
+ FROM (
+ SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
+ FROM (
+ SELECT me.name, me.id FROM owners me
+ ) me
+ ) me
+ WHERE rno__row__index >= ? AND rno__row__index <= ?
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 2 ],
+ [ { sqlt_datatype => 'integer' } => 4 ],
+ ]
+ ],
};
},
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
%s
) me
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
) me
) me
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
) me
[ { sqlt_datatype => 'integer' } => 4 ],
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT me.name, me.id
+ FROM (
+ SELECT me.name, me.id, ROWNUM rownum__index
+ FROM (
+ SELECT me.name, me.id
+ FROM owners me
+ ) me
+ ) me WHERE rownum__index BETWEEN ? AND ?
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 2 ],
+ [ { sqlt_datatype => 'integer' } => 4 ],
+ ]
+ ],
};
},
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
FETCH FIRST 4 ROWS ONLY
)',
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY me.id
FETCH FIRST 7 ROWS ONLY
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
FETCH FIRST 4 ROWS ONLY
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
FETCH FIRST 7 ROWS ONLY
(map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT me.name, me.id
+ FROM (
+ SELECT me.name, me.id
+ FROM owners me
+ ORDER BY me.id
+ FETCH FIRST 4 ROWS ONLY
+ ) me
+ ORDER BY me.id DESC
+ FETCH FIRST 3 ROWS ONLY
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ )',
+ [],
+ ],
},
Top => {
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
)',
[
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY me.id
) me
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
)',
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
ORDER BY ? / ?, ?
) me
(map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit
],
],
- },
-
- RowCountOrGenericSubQ => {
- limit => [
+ limit_offset_prefetch => [
'(
- SET ROWCOUNT 4
- SELECT me.id, owner.id, owner.name, ? * ?, ?
- FROM books me
- JOIN owners owner
- ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg(me.id / ?)
- HAVING ?
- ORDER BY me.id
- SET ROWCOUNT 0
- )',
- [
- @select_bind,
- @where_bind,
- @group_bind,
- @having_bind,
- ],
- ],
- limit_offset => [
- '(
- SELECT me.id, owner__id, owner__name, bar, baz
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
- SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
- FROM books me
- JOIN owners owner
- ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg( me.id / ? )
- HAVING ?
+ SELECT TOP 3 me.name, me.id
+ FROM (
+ SELECT TOP 4 me.name, me.id
+ FROM owners me
+ ORDER BY me.id
+ ) me
+ ORDER BY me.id DESC
) me
- WHERE (
- SELECT COUNT( * )
- FROM books rownum__emulation
- WHERE rownum__emulation.id < me.id
- ) BETWEEN ? AND ?
- ORDER BY me.id
+ LEFT JOIN books books
+ ON books.owner = me.id
)',
- [
- @select_bind,
- @where_bind,
- @group_bind,
- @having_bind,
- [ { sqlt_datatype => 'integer' } => 3 ],
- [ { sqlt_datatype => 'integer' } => 6 ],
- ],
+ [],
],
},
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg( me.id / ? )
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
) me
WHERE (
JOIN owners owner
ON owner.id = me.owner
WHERE source != ? AND me.title = ? AND source = ?
- GROUP BY avg( me.id / ? )
+ GROUP BY AVG(me.id / ?), MAX(owner.id)
HAVING ?
) me
WHERE (
[ { sqlt_datatype => 'integer' } => 6 ],
],
],
+ limit_offset_prefetch => [
+ '(
+ SELECT me.name, books.id, books.source, books.owner, books.title, books.price
+ FROM (
+ SELECT me.name, me.id
+ FROM (
+ SELECT me.name, me.id FROM owners me
+ ) me
+ WHERE (
+ SELECT COUNT(*)
+ FROM owners rownum__emulation
+ WHERE rownum__emulation.id < me.id
+ ) BETWEEN ? AND ?
+ ORDER BY me.id
+ ) me
+ LEFT JOIN books books
+ ON books.owner = me.id
+ ORDER BY me.id
+ )',
+ [
+ [ { sqlt_datatype => 'integer' } => 1 ],
+ [ { sqlt_datatype => 'integer' } => 3 ],
+ ],
+ ],
}
};
join => 'owner', # single-rel manual prefetch
rows => 4,
'+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
- group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
+ group_by => \[ 'AVG(me.id / ?), MAX(owner.id)', [ $attr => 21 ] ],
having => \[ '?', [ $attr => 31 ] ],
($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
});
@{$tests->{$limtype}{ordered_limit_offset}},
"$limtype: Ordered limit+offset with select/group/having",
) if $tests->{$limtype}{ordered_limit_offset};
+
+ # complex prefetch on partial-fetch root with limit
+ my $pref_rs = $schema->resultset('Owners')->search({}, {
+ rows => 3,
+ offset => 1,
+ columns => 'name', # only the owner name, still prefetch all the books
+ prefetch => 'books',
+ ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy
+ });
+
+ is_same_sql_bind (
+ $pref_rs->as_query,
+ @{$tests->{$limtype}{limit_offset_prefetch}},
+ "$limtype: Prefetch with limit+offset",
+ ) if $tests->{$limtype}{limit_offset_prefetch};
+
+ # we can actually run the query
+ if ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ') {
+ lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limtied prefetch') }
+ "Complex limited prefetch works with supported limit $limtype"
+ }
}
done_testing;