[Dbix-class] Fwd: MySQL query plan affecting DBIC structure (bug?)

[Peter Rabbitson]

Paul Makepeace wrote:
> We've been debugging an awkward fail where the same query on the same
> DBIC version (8115) on the same mysql server is producing different
> results off two databases with very similar data (I'll expand on
> this).
> 
> The SQL that DBIC produces has mysql returning the same dataset but in
> a different order; verified on the command line. Now, if in our DBIC
> query we add in an order_by => [..., 'me.uid'] (PK) the output from
> mysql is identical, as is DBIC's returned result. I dug in and saw
> that the SQL query plan is quite different which would explain the
> ordering difference. It seems like DBIC is being sensitive to order
> where it shouldn't be.
> 
> Here's the query,
>      $c->model('DBIC_Readonly')->resultset('StoryCat')->search(
>      $search,
>      {
>        prefetch => [
>          'company',
>          'impact',
>          'favourability',
>          'topic',
>          {
>            'story' => [
>              { 'story_publication'  =>
>                [ 'region', 'publication_type' ]
>              },
>              { 'author_story' => 'author' }
>            ]
>          }
>        ],
>        order_by => 'story.date_time ASC',
>      });
> 
> The generated query is inserting its own "order by author_story.story_uid"
> 
> __PACKAGE__->table('author_story');
> __PACKAGE__->add_columns(qw/
> author_uid
> story_uid
> is_primary
> /);
> __PACKAGE__->set_primary_key(qw/author_uid story_uid/);
> __PACKAGE__->resultset_class('IDL::ResultSet::AuthorStory');
> __PACKAGE__->belongs_to(author => 'IDL::Schema::Author', 'author_uid');
> 
> Is DBIC attempting to do with "order by author_story.story_uid" what
> we're hacking with "me.uid"?
> 
> So what if anything can we do to help debug this? Is there an obvious
> mistake here? Do you need to see all the rels?

Not the rels, but the generated SQL itself. Also hilight *in the SQL*
what is bothering you. This is not a bug, but in order to explain to
you what happens and why I need the actual SQL.