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

[Peter Rabbitson]

Byron Young wrote:
>> -----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?
> 

Looking now, I already see what the problem is, solution on the way...