[Dbix-class] Search with complex joins generating bad SQL

[Byron Young]

> -----Original Message-----
> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Sent: Tuesday, March 30, 2010 3:40 AM
> To: Class user and developer list
> Subject: Re: [Dbix-class] Search with complex joins generating bad SQL
> 
> Byron Young wrote:
> > Hello,
> >
> > I have a search that's generating SQL that causes MySQL to barf.  I'm
> using DBIC v0.08120.  Here is the search call:
> >
> > $schema->resultset("Results")->search({
> >         'hostname_2.name' => {
> >             '=' => 'gen-cs167'
> >         }
> >     }, {
> >         join => [
> >             { 'map_results_tested_products' => {
> >                   'tested_product' => 'hostname'
> >                }
> >             },
> >             { 'map_results_tested_hosts' => {
> >                   'tested_host' => 'hostname' # references to this
> will
> >                                               # need to use
> 'hostname_2'
> >               }
> >             },
> >         ],
> >     }
> > );
> >
> > And here is the generated SQL and exception:
> >
> > DBI Exception: DBD::mysql::st execute failed: Unknown column
> 'tested_product.hostname' in 'on clause' [for Statement "SELECT COUNT(
> * ) FROM (SELECT `me`.`id`, `me`.`result_type`, `me`.`start_time`,
> `me`.`end_time`, `me`.`results_dir`, `me`.`test_case`,
> `me`.`modified_ts`, `me`.`result_category`, `me`.`bug_ids`,
> `me`.`comments`, `me`.`is_debug`, `me`.`regression`, `me`.`release`
> FROM `results` `me` LEFT JOIN `hostnames` `hostname` ON `hostname`.`id`
> = `tested_product`.`hostname` LEFT JOIN `map_results_tested_hosts`
> `map_results_tested_hosts` ON `map_results_tested_hosts`.`result` =
> `me`.`id` LEFT JOIN `tested_hosts` `tested_host` ON `tested_host`.`id`
> = `map_results_tested_hosts`.`tested_host` LEFT JOIN `hostnames`
> `hostname_2` ON `hostname_2`.`id` = `tested_host`.`hostname` WHERE (
> `hostname_2`.`name` = ? ) GROUP BY `me`.`id`, `me`.`result_type`,
> `me`.`start_time`, `me`.`end_time`, `me`.`results_dir`,
> `me`.`test_case`, `me`.`modified_ts`, `me`.`result_category`,
> `me`.`bug_
> ids`, `me`.`comments`, `me`.`is_debug`, `me`.`regression`,
> `me`.`release`) `count_subq`" with ParamValues: 0='gen-cs167']
> >
> 
> This is a blatant bug, 99% caused by my crazy experiments.
> Unfortunately
> I have no time this week to even start figuring out what went wrong.
> Try
> current trunk, if that doesn't work - all I can suggest is downgrade at
> this point. I should be able to look into it Saturday-ish.
> 
> Cheers

Hey Peter,

Have you had a chance to look at this?  I tried to test with trunk to see if it had the issue, but I got lost in dependency hell.  The trunk version wants to upgrade Moose, which will cause us to have to upgrade Catalyst.  We just spent a pretty significant amount of time upgrading to the latest DBIC and testing all of our apps that depend on the various upgraded modules, and we're on a super tight schedule right now, so I didn't want to go down that road again with more upgrades at the moment.  I realize your time is limited too, but any help you can offer would be greatly appreciated.  Is it possible to patch our current version to fix the bug without upgrading?

Thanks,
Byron