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

[Byron Young]

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']


Here are the relevant relationship definitions:

Results
==

__PACKAGE__->has_many(
  "map_results_tested_hosts",
  "NBT::Schema::Icebox::MapResultsTestedHosts",
  { "foreign.result" => "self.id" },
);


__PACKAGE__->has_many(
  "map_results_tested_products",
  "NBT::Schema::Icebox::MapResultsTestedProducts",
  { "foreign.result" => "self.id" },
);



MapResultsTestedHosts
==
__PACKAGE__->belongs_to(
  "tested_host",
  "NBT::Schema::Icebox::TestedHosts",
  { id => "tested_host" },
  {},
);


TestedHosts
==
__PACKAGE__->belongs_to(
  "hostname",
  "NBT::Schema::Icebox::Hostnames",
  { id => "hostname" },
  {},
);


MapResultsTestedProducts
==
__PACKAGE__->belongs_to(
  "tested_product",
  "NBT::Schema::Icebox::TestedProducts",
  { id => "tested_product" },
  {},
);


TestedProducts
==
__PACKAGE__->belongs_to(
  "hostname",
  "NBT::Schema::Icebox::Hostnames",
  { id => "hostname" },
  {},
);


Any idea why this is happening?  I'm happy to provide more information if needed.

Thanks!

Byron