my $schema = DBICTest->init_schema;
my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
+my $where_string = 'me.title = ? AND source != ? AND source = ?';
+
my @where_bind = (
- [ {} => 'Study' ],
[ {} => 'kama sutra' ],
+ [ {} => 'Study' ],
[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
);
my @select_bind = (
LimitOffset => {
limit => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
LIMIT ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
LIMIT ?
OFFSET ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?
- )',
+ )",
[
@select_bind,
@where_bind,
]
],
ordered_limit_offset => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?
OFFSET ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 3 ],
[ { sqlt_datatype => 'integer' } => 1 ],
LimitXY => {
ordered_limit_offset => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?, ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 1 ],
[ { sqlt_datatype => 'integer' } => 3 ],
SkipFirst => {
ordered_limit_offset => [
- '(
+ "(
SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 3 ],
[ { sqlt_datatype => 'integer' } => 4 ],
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT SKIP ? FIRST ? me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 1 ],
[ { sqlt_datatype => 'integer' } => 3 ],
FirstSkip => {
ordered_limit_offset => [
- '(
+ "(
SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 4 ],
[ { sqlt_datatype => 'integer' } => 3 ],
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT FIRST ? SKIP ? me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 3 ],
[ { sqlt_datatype => 'integer' } => 1 ],
},
RowNumberOver => do {
- my $unordered_sql = '(
+ my $unordered_sql = "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
) me
WHERE rno__row__index >= ? AND rno__row__index <= ?
- )';
+ )";
- my $ordered_sql = '(
+ my $ordered_sql = "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
) me
WHERE rno__row__index >= ? AND rno__row__index <= ?
- )';
+ )";
{
limit => [$unordered_sql,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 2 ],
[ { sqlt_datatype => 'integer' } => 4 ],
RowNum => do {
my $limit_sql = sub {
- sprintf '(
+ sprintf "(
SELECT me.id, owner__id, owner__name, bar, baz
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
%s
) me
WHERE ROWNUM <= ?
- )', $_[0] || '';
+ )", $_[0] || '';
};
{
],
],
limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
) me
WHERE rownum__index BETWEEN ? AND ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
WHERE ROWNUM <= ?
) me
WHERE rownum__index >= ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 2 ],
[ { sqlt_datatype => 'integer' } => 4 ],
FetchFirst => {
limit => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
FETCH FIRST 4 ROWS ONLY
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY me.id
) me
ORDER BY me.id DESC
FETCH FIRST 4 ROWS ONLY
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit => [
- '(
+ "(
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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
FETCH FIRST 4 ROWS ONLY
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
FETCH FIRST 4 ROWS ONLY
) me
ORDER BY ORDER__BY__001, ORDER__BY__002
- )',
+ )",
[
@select_bind,
@order_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[],
],
},
Top => {
limit => [
- '(
+ "(
SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset => [
- '(
+ "(
SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
FROM (
SELECT TOP 7 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 = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY me.id
) me
ORDER BY me.id DESC
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit => [
- '(
+ "(
SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
) me
ORDER BY ORDER__BY__001, ORDER__BY__002
- )',
+ )",
[
@select_bind,
@order_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT TOP 3 me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[],
],
},
GenericSubQ => {
ordered_limit => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
)
) < ?
ORDER BY me.price DESC, me.id ASC
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
)
) BETWEEN ? AND ?
ORDER BY me.price DESC, me.id ASC
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
LEFT JOIN books books
ON books.owner = me.id
ORDER BY me.name ASC, me.id DESC
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 1 ],
[ { sqlt_datatype => 'integer' } => 3 ],