On Thu, Jan 21, 2010 at 00:34, Peter Rabbitson <[email protected]> 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 _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
