force NLS_{LANG,COMP,SORT} for improved predictability

[dbsrgits/DBIx-Class.git] / t / prefetch / o2m_o2m_order_by_with_limit.t

use strict;
use warnings;

use Test::More;

use lib qw(t/lib);
use DBIC::SqlMakerTest;
use DBICTest;

my $schema = DBICTest->init_schema();

my $artist_rs = $schema->resultset('Artist');
my $ar = $artist_rs->current_source_alias;

my $filtered_cd_rs = $artist_rs->search_related('cds_unordered',
  { "$ar.rank" => 13 },
  {
    prefetch => [ 'tracks' ],
    order_by => [ { -asc => "$ar.name" }, "$ar.artistid DESC" ],
    offset   => 3,
    rows     => 3,
  },
);

is_same_sql_bind(
  $filtered_cd_rs->as_query,
  q{(
    SELECT  cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track,
            tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, tracks.small_dt
      FROM artist me
      JOIN (
        SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track
          FROM artist me
          JOIN cd cds_unordered
            ON cds_unordered.artist = me.artistid
        WHERE ( me.rank = ? )
        GROUP BY cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track, me.name, me.artistid
        ORDER BY me.name ASC, me.artistid DESC
        LIMIT 3
        OFFSET 3
      ) cds_unordered
        ON cds_unordered.artist = me.artistid
      LEFT JOIN track tracks
        ON tracks.cd = cds_unordered.cdid
    WHERE ( me.rank = ? )
    ORDER BY me.name ASC, me.artistid DESC, tracks.cd
  )},
  [ [ 'me.rank' => 13 ], [ 'me.rank' => 13 ] ],
  'correct SQL on limited prefetch over search_related ordered by root',
);

# note: we only requested "get all cds of all artists with rank 13 then order
# by the artist name and give me the fourth, fifth and sixth", consequently the
# cds that belong to the same artist are unordered; fortunately we know that
# the first artist have 3 cds and the second and third artist both have only
# one, so the first 3 cds belong to the first artist and the fourth and fifth
# cds belong to the second and third artist, respectively, and there's no sixth
# row
is_deeply (
  [ $filtered_cd_rs->hri_dump ],
  [
    {
      'artist' => '2',
      'cdid' => '4',
      'genreid' => undef,
      'single_track' => undef,
      'title' => 'Generic Manufactured Singles',
      'tracks' => [
        {
          'cd' => '4',
          'last_updated_at' => undef,
          'last_updated_on' => undef,
          'position' => '1',
          'small_dt' => undef,
          'title' => 'Boring Name',
          'trackid' => '10'
        },
        {
          'cd' => '4',
          'last_updated_at' => undef,
          'last_updated_on' => undef,
          'position' => '2',
          'small_dt' => undef,
          'title' => 'Boring Song',
          'trackid' => '11'
        },
        {
          'cd' => '4',
          'last_updated_at' => undef,
          'last_updated_on' => undef,
          'position' => '3',
          'small_dt' => undef,
          'title' => 'No More Ideas',
          'trackid' => '12'
        }
      ],
      'year' => '2001'
    },
    {
      'artist' => '3',
      'cdid' => '5',
      'genreid' => undef,
      'single_track' => undef,
      'title' => 'Come Be Depressed With Us',
      'tracks' => [
        {
          'cd' => '5',
          'last_updated_at' => undef,
          'last_updated_on' => undef,
          'position' => '1',
          'small_dt' => undef,
          'title' => 'Sad',
          'trackid' => '13'
        },
        {
          'cd' => '5',
          'last_updated_at' => undef,
          'last_updated_on' => undef,
          'position' => '3',
          'small_dt' => undef,
          'title' => 'Suicidal',
          'trackid' => '15'
        },
        {
          'cd' => '5',
          'last_updated_at' => undef,
          'last_updated_on' => undef,
          'position' => '2',
          'small_dt' => undef,
          'title' => 'Under The Weather',
          'trackid' => '14'
        }
      ],
      'year' => '1998'
    }
  ],
  'Correctly ordered result',
);

done_testing;