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

[Paul Makepeace]

On Thu, Jan 21, 2010 at 00:34, Peter Rabbitson <rabbit+dbic at rabbit.us> wrote:
> 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.

Here is a paste,
http://scsys.co.uk:8001/38408?tx=on&wr=on&submit=Format+it%21

Thanks,
Paul