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

[Peter Rabbitson]

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