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

Paul Makepeace paulm at paulm.com
Thu Jan 21 07:08:55 GMT 2010


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?

Nailing this down to a test case is going to be a lot of work, and the
datasets are pretty big so I'd like to get a guide before going that
route...

Cheers,
Paul



More information about the DBIx-Class mailing list