[Dbix-class] Force LEFT JOIN bug?

[Ivan Fomichev]

Hello,

I found an oddity in forcing LEFT JOIN. Here's an example:

8<-----------------------------pet_shop.sql--------------------------------
DROP TABLE IF EXISTS `animals`;
CREATE TABLE `animals` (
  `animal_id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned default NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`animal_id`)
);

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  `state_id` int(10) unsigned NOT NULL,
  `first_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`customer_id`)
);

DROP TABLE IF EXISTS `states`;
CREATE TABLE `states` (
  `state_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`state_id`)
);
8<-------------------------------Schema.pm---------------------------------
package Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_classes();
1;
8<---------------------------Schema/Animal.pm------------------------------
package Schema::Animal;
use base qw/ DBIx::Class /;
__PACKAGE__->load_components( qw/ Core / );
__PACKAGE__->table( 'animals' );
__PACKAGE__->add_columns( qw/ animal_id customer_id name / );
__PACKAGE__->set_primary_key( 'animal_id' );
__PACKAGE__->belongs_to( customer => 'Schema::Customer',
'customer_id', { join_type => 'left' } );
1;
8<--------------------------Schema/Customer.pm-----------------------------
package Schema::Customer;
use base qw/ DBIx::Class /;
__PACKAGE__->load_components( qw/ Core / );
__PACKAGE__->table( 'customers' );
__PACKAGE__->add_columns( qw/ customer_id state_id first_name / );
__PACKAGE__->set_primary_key( 'customer_id' );
__PACKAGE__->belongs_to( state => 'Schema::State', 'state_id', {
join_type => 'inner' } );
1;
8<----------------------------Schema/State.pm------------------------------
package Schema::State;
use base qw/ DBIx::Class /;
__PACKAGE__->load_components( qw/ Core / );
__PACKAGE__->table( 'states' );
__PACKAGE__->load_components( qw/ Core / );
__PACKAGE__->add_columns( qw/ state_id name / );
__PACKAGE__->set_primary_key( 'state_id' );
1;
8<-----------------------------test_dbic3.pl-------------------------------
#!/usr/bin/perl
use Schema;
my $schema = Schema->connect( 'dbi:mysql:pet_shop', 'root', '' );
$schema->storage->debug(1);
my $rs = $schema->resultset('Animal')->search(
        undef,
        { prefetch => { customer => {} } },
    )->search(
        undef,
        { prefetch => { customer => 'state' } },
    );
$rs->first();
8<----------------------------CURRENT RESULT-------------------------------
SELECT me.animal_id, me.customer_id, me.name, customer.customer_id,
customer.state_id, customer.first_name, state.state_id, state.name
FROM animals me LEFT JOIN customers customer ON ( customer.customer_id
= me.customer_id ) INNER JOIN states state ON ( state.state_id =
customer.state_id )
8<----------------------------EXPECTED RESULT------------------------------
SELECT me.animal_id, me.customer_id, me.name, customer.customer_id,
customer.state_id, customer.first_name, state.state_id, state.name
FROM animals me LEFT JOIN customers customer ON ( customer.customer_id
= me.customer_id ) LEFT JOIN states state ON ( state.state_id =
customer.state_id )
8<----------------------------------END------------------------------------

It's interesting, that if we change the first prefetch clause to {
prefetch => [ 'customer' ] }, then everything works all right. Is
empty hash notation was not kept in mind when forcing left join was
introduced?

Regards,
Ivan