[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / torture_quoted.t

use strict;
use warnings;

use Test::More;
use Test::Exception;
use Storable 'dclone';
use lib qw(t/lib);
use DBICTest ':DiffSQL';

my $schema = DBICTest->init_schema(
  no_deploy => 1,
  quote_char => '`',
  name_sep => '.',
);

my $bs = $schema->source('BooksInLibrary');
$bs->remove_column('price');
$bs->add_columns(
  'price (#)' => {
    data_type => 'integer',
    is_nullable => 1,
  },
  'short desc' => {
    data_type => 'text',
    is_nullable => 1,
  },
);

my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};

my $where_string = '`me`.`title` = ? AND `source` != ? AND `source` = ?';

my @where_bind = (
  [ {} => 'kama sutra' ],
  [ {} => 'Study' ],
  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
);
my @select_bind = (
  [ { sqlt_datatype => 'numeric' } => 11 ],
  [ {} => 12 ],
  [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
);
my @group_bind = (
  [ {} => 21 ],
);
my @having_bind = (
  [ {} => 31 ],
);
my @order_bind = (
  [ { sqlt_datatype => 'int' } => 1 ],
  [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
  [ {} => 3 ],
);

my $tests = {

  LimitOffset => {
    limit => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        LIMIT ?
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        [ { sqlt_datatype => 'integer' } => 4 ],
      ],
    ],
    limit_offset => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        LIMIT ?
        OFFSET ?
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        [ { sqlt_datatype => 'integer' } => 4 ],
        [ { sqlt_datatype => 'integer' } => 3 ],
      ],
    ],
    ordered_limit => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
        LIMIT ?
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_bind,
        [ { sqlt_datatype => 'integer' } => 4 ],
      ]
    ],
    ordered_limit_offset => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
        LIMIT ?
        OFFSET ?
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_bind,
        [ { sqlt_datatype => 'integer' } => 4 ],
        [ { sqlt_datatype => 'integer' } => 3 ],
      ],
    ],
    limit_offset_prefetch => [
      "(
        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
          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 => {
    ordered_limit_offset => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
        LIMIT ?, ?
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_bind,
        [ { sqlt_datatype => 'integer' } => 3 ],
        [ { sqlt_datatype => 'integer' } => 4 ],
      ],
    ],
    limit_offset_prefetch => [
      "(
        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
          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 => {
    ordered_limit_offset => [
      "(
        SELECT SKIP ? FIRST ? `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
      )",
      [
        [ { sqlt_datatype => 'integer' } => 3 ],
        [ { sqlt_datatype => 'integer' } => 4 ],
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_bind,
      ],
    ],
    limit_offset_prefetch => [
      "(
        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
          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 => {
    ordered_limit_offset => [
      "(
        SELECT FIRST ? SKIP ? `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
      )",
      [
        [ { sqlt_datatype => 'integer' } => 4 ],
        [ { sqlt_datatype => 'integer' } => 3 ],
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_bind,
      ],
    ],
    limit_offset_prefetch => [
      "(
        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
          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 {
    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 (
              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 $where_string
              GROUP BY (`me`.`id` / ?), `owner`.`id`
              HAVING ?
            ) `me`
      ) `me`
      WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
    )";

    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`, `ORDER__BY__003` ) AS `rno__row__index`
            FROM (
              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`, `me`.`short desc` AS `ORDER__BY__003`
                FROM `books` `me`
                JOIN `owners` `owner`
                  ON `owner`.`id` = `me`.`owner`
              WHERE $where_string
              GROUP BY (`me`.`id` / ?), `owner`.`id`
              HAVING ?
            ) `me`
      ) `me`
      WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
    )";

    {
      limit => [$unordered_sql,
        [
          @select_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          [ { sqlt_datatype => 'integer' } => 1 ],
          [ { sqlt_datatype => 'integer' } => 4 ],
        ],
      ],
      limit_offset => [$unordered_sql,
        [
          @select_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          [ { sqlt_datatype => 'integer' } => 4 ],
          [ { sqlt_datatype => 'integer' } => 7 ],
        ],
      ],
      ordered_limit => [$ordered_sql,
        [
          @select_bind,
          @order_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          [ { sqlt_datatype => 'integer' } => 1 ],
          [ { sqlt_datatype => 'integer' } => 4 ],
        ],
      ],
      ordered_limit_offset => [$ordered_sql,
        [
          @select_bind,
          @order_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          [ { sqlt_datatype => 'integer' } => 4 ],
          [ { sqlt_datatype => 'integer' } => 7 ],
        ],
      ],
      limit_offset_prefetch => [
        "(
          SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
            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 ],
        ]
      ],
    };
  },

  RowNum => do {
    my $limit_sql = sub {
      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 $where_string
            GROUP BY (`me`.`id` / ?), `owner`.`id`
            HAVING ?
            %s
          ) `me`
        WHERE ROWNUM <= ?
      )", $_[0] || '';
    };

    {
      limit => [ $limit_sql->(),
        [
          @select_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          [ { sqlt_datatype => 'integer' } => 4 ],
        ],
      ],
      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 (
                  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 $where_string
                  GROUP BY (`me`.`id` / ?), `owner`.`id`
                  HAVING ?
                ) `me`
            ) `me`
          WHERE `rownum__index` BETWEEN ? AND ?
        )",
        [
          @select_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          [ { sqlt_datatype => 'integer' } => 4 ],
          [ { sqlt_datatype => 'integer' } => 7 ],
        ],
      ],
      ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?, `me`.`short desc`'),
        [
          @select_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          @order_bind,
          [ { sqlt_datatype => 'integer' } => 4 ],
        ],
      ],
      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 (
                  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 $where_string
                  GROUP BY (`me`.`id` / ?), `owner`.`id`
                  HAVING ?
                  ORDER BY ? / ?, ?, `me`.`short desc`
                ) `me`
              WHERE ROWNUM <= ?
            ) `me`
          WHERE `rownum__index` >= ?
        )",
        [
          @select_bind,
          @where_bind,
          @group_bind,
          @having_bind,
          @order_bind,
          [ { sqlt_datatype => 'integer' } => 7 ],
          [ { sqlt_datatype => 'integer' } => 4 ],
        ],
      ],
      limit_offset_prefetch => [
        "(
          SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
            FROM (
              SELECT `me`.`name`, `me`.`id`
                FROM (
                  SELECT `me`.`name`, `me`.`id`, ROWNUM AS `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 ],
        ]
      ],
    };
  },

  FetchFirst => {
    limit => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        FETCH FIRST 4 ROWS ONLY
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_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 $where_string
            GROUP BY (`me`.`id` / ?), `owner`.`id`
            HAVING ?
            ORDER BY `me`.`id`
            FETCH FIRST 7 ROWS ONLY
          ) `me`
        ORDER BY `me`.`id` DESC
        FETCH FIRST 4 ROWS ONLY
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
      ],
    ],
    ordered_limit => [
      "(
        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
        FETCH FIRST 4 ROWS ONLY
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_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`, `ORDER__BY__003`
              FROM (
                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`, `me`.`short desc` AS `ORDER__BY__003`
                  FROM `books` `me`
                  JOIN `owners` `owner`
                    ON `owner`.`id` = `me`.`owner`
                WHERE $where_string
                GROUP BY (`me`.`id` / ?), `owner`.`id`
                HAVING ?
                ORDER BY ? / ?, ?, `me`.`short desc`
                FETCH FIRST 7 ROWS ONLY
              ) `me`
            ORDER BY `ORDER__BY__001` DESC, `ORDER__BY__002` DESC, `ORDER__BY__003` DESC
            FETCH FIRST 4 ROWS ONLY
          ) `me`
        ORDER BY `ORDER__BY__001`, `ORDER__BY__002`, `ORDER__BY__003`
      )",
      [
        @select_bind,
        @order_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @{ dclone \@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 (#)`, `books`.`short desc`
          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 => {
    limit => [
      "(
        SELECT TOP 4 `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_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 $where_string
            GROUP BY (`me`.`id` / ?), `owner`.`id`
            HAVING ?
            ORDER BY `me`.`id`
          ) `me`
        ORDER BY `me`.`id` DESC
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
      ],
    ],
    ordered_limit => [
      "(
        SELECT TOP 4 `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
          FROM `books` `me`
          JOIN `owners` `owner`
            ON `owner`.`id` = `me`.`owner`
        WHERE $where_string
        GROUP BY (`me`.`id` / ?), `owner`.`id`
        HAVING ?
        ORDER BY ? / ?, ?, `me`.`short desc`
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @order_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`, `ORDER__BY__003`
              FROM (
                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`, `me`.`short desc` AS `ORDER__BY__003`
                  FROM `books` `me`
                  JOIN `owners` `owner`
                    ON `owner`.`id` = `me`.`owner`
                WHERE $where_string
                GROUP BY (`me`.`id` / ?), `owner`.`id`
                HAVING ?
                ORDER BY ? / ?, ?, `me`.`short desc`
              ) `me`
            ORDER BY `ORDER__BY__001` DESC, `ORDER__BY__002` DESC, `ORDER__BY__003` DESC
          ) `me`
        ORDER BY `ORDER__BY__001`, `ORDER__BY__002`, `ORDER__BY__003`
      )",
      [
        @select_bind,
        @order_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        @{ dclone \@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 (#)`, `books`.`short desc`
          FROM (
            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`
          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 $where_string
            GROUP BY (`me`.`id` / ?), `owner`.`id`
            HAVING ?
          ) `me`
        WHERE (
          SELECT COUNT( * )
            FROM `books` `rownum__emulation`
          WHERE
            ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NOT NULL )
              OR
            (
              `rownum__emulation`.`price (#)` > `me`.`price (#)`
                AND
              `me`.`price (#)` IS NOT NULL
                AND
              `rownum__emulation`.`price (#)` IS NOT NULL
            )
              OR
            (
              (
                `me`.`price (#)` = `rownum__emulation`.`price (#)`
                 OR
                ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NULL )
              )
                AND
              `rownum__emulation`.`id` < `me`.`id`
            )
          ) < ?
        ORDER BY `me`.`price (#)` DESC, `me`.`id` ASC
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        [ { sqlt_datatype => 'integer' } => 4 ],
      ],
    ],
    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 $where_string
            GROUP BY (`me`.`id` / ?), `owner`.`id`
            HAVING ?
          ) `me`
        WHERE (
          SELECT COUNT( * )
            FROM `books` `rownum__emulation`
          WHERE
            ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NOT NULL )
              OR
            (
              `rownum__emulation`.`price (#)` > `me`.`price (#)`
                AND
              `me`.`price (#)` IS NOT NULL
                AND
              `rownum__emulation`.`price (#)` IS NOT NULL
            )
              OR
            (
              (
                `me`.`price (#)` = `rownum__emulation`.`price (#)`
                 OR
                ( `me`.`price (#)` IS NULL AND `rownum__emulation`.`price (#)` IS NULL )
              )
                AND
              `rownum__emulation`.`id` < `me`.`id`
            )
          ) BETWEEN ? AND ?
        ORDER BY `me`.`price (#)` DESC, `me`.`id` ASC
      )",
      [
        @select_bind,
        @where_bind,
        @group_bind,
        @having_bind,
        [ { sqlt_datatype => 'integer' } => 3 ],
        [ { sqlt_datatype => 'integer' } => 6 ],
      ],
    ],
    limit_offset_prefetch => [
      "(
        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price (#)`, `books`.`short desc`
          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`.`name` < `me`.`name`
                    OR
                  (
                    `me`.`name` = `rownum__emulation`.`name`
                      AND
                    `rownum__emulation`.`id` > `me`.`id`
                  )
                )
              ) BETWEEN ? AND ?
            ORDER BY `me`.`name` ASC, `me`.`id` DESC
          ) `me`
          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 ],
      ],
    ],
  }
};

for my $limtype (sort keys %$tests) {

  Test::Builder->new->is_passing or exit;

  delete $schema->storage->_sql_maker->{_cached_syntax};
  $schema->storage->_sql_maker->limit_dialect ($limtype);

  # not deploying, so can't run
  my $can_run = 0 && ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');

  # chained search is necessary to exercise the recursive {where} parser
  my $rs = $schema->resultset('BooksInLibrary')->search(
    { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
  )->search(
    { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
    {
      columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
      join => 'owner',  # single-rel manual prefetch
      rows => 4,
      '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
      group_by => \[ '(`me`.`id` / ?), `owner`.`id`', 21 ],
      having => \[ '?', 31 ],
    }
  );

  #
  # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
  #

  # only limit, no offset, no order
  if ($tests->{$limtype}{limit}) {
    lives_ok {
      is_same_sql_bind(
        $rs->as_query,
        @{$tests->{$limtype}{limit}},
        "$limtype: Unordered limit with select/group/having",
      );

      $rs->all if $can_run;
    } "Grouped limit under $limtype";
  }

  # limit + offset, no order
  if ($tests->{$limtype}{limit_offset}) {

    lives_ok {
      my $subrs = $rs->search({}, { offset => 3 });

      is_same_sql_bind(
        $subrs->as_query,
        @{$tests->{$limtype}{limit_offset}},
        "$limtype: Unordered limit+offset with select/group/having",
      );

      $subrs->all if $can_run;
    } "Grouped limit+offset runs under $limtype";
  }

  # order + limit, no offset
  $rs = $rs->search(undef, {
    order_by => ( $limtype =~ /GenericSubQ/
      ? [ { -desc => 'price (#)' }, 'me.id', \[ '`owner`.`name` + ?', 'bah' ], \'`me`.`short desc`' ] # needs a same-table stable order to be happy
      : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ], \'`me`.`short desc`' ]
    ),
  });

  if ($tests->{$limtype}{ordered_limit}) {

    lives_ok {
      is_same_sql_bind(
        $rs->as_query,
        @{$tests->{$limtype}{ordered_limit}},
        "$limtype: Ordered limit with select/group/having",
      );

      $rs->all if $can_run;
    } "Grouped ordered limit runs under $limtype"
  }

  # order + limit + offset
  if ($tests->{$limtype}{ordered_limit_offset}) {
    lives_ok {
      my $subrs = $rs->search({}, { offset => 3 });

      is_same_sql_bind(
        $subrs->as_query,
        @{$tests->{$limtype}{ordered_limit_offset}},
        "$limtype: Ordered limit+offset with select/group/having",
      );

      $subrs->all if $can_run;
    } "Grouped ordered limit+offset runs under $limtype";
  }

  # 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/ ? () : (
      # needs a same-table stable order to be happy
      order_by => [ { -asc => 'me.name' }, \ '`me`.`id` DESC' ]
    )),
  });

  lives_ok {
    is_same_sql_bind (
      $pref_rs->as_query,
      @{$tests->{$limtype}{limit_offset_prefetch}},
      "$limtype: Prefetch with limit+offset",
    ) if $tests->{$limtype}{limit_offset_prefetch};

    is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
      if $can_run;
  } "Complex limited prefetch runs under $limtype";
}

done_testing;