[Dbix-class] Force LEFT JOIN bug?

[Matt S Trout]

On Mon, Aug 27, 2007 at 01:18:19PM +0400, Ivan Fomichev wrote:
> 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?

Looks like a bug to me.

Can you reproduce it against the DBIC test classes? A patch against t/*.t
would make this a lot easier to fix.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/                    http://www.shadowcat.co.uk/