[Dbix-class] Strange result when joining with the same alias twice

[Ivan Fomichev]

Hello, all,

I've got a strange result when I wrote a script, that joins a single
table twice under the same alias. Though I've managed to avoid this
problem, having assigned another alias for one of relationships,
however I think this is a bug and needs to be fixed. There is an
sample code:

8<--------------------------ifomichev_test.sql-----------------------------
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `account_id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(255) NOT NULL,
  `person_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`account_id`),
  KEY `person_id` (`person_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `accounts` VALUES (1,'barney',3),(2,'fred',1);

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `group_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `account_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`group_id`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `groups` VALUES (1,'foo',1),(2,'bar',2),(3,'baz',NULL);

DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
  `person_id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(255) NOT NULL,
  `group_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`person_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `people` VALUES (1,'Fred',1),(2,'Wilma',1),(3,'Barney',2);
8<-------------------------------Schema.pm---------------------------------
package Schema;
use base qw/DBIx::Class::Schema/;

__PACKAGE__->load_classes();

1;
8<---------------------------Schema/Account.pm-----------------------------
package Schema::Account;
use base qw/DBIx::Class/;

__PACKAGE__->load_components( qw/ Core PK::Auto / );
__PACKAGE__->table( 'accounts' );
__PACKAGE__->add_columns( qw/ account_id username person_id / );
__PACKAGE__->set_primary_key( qw/ account_id / );

__PACKAGE__->belongs_to( person => 'Schema::Person', 'person_id' );
__PACKAGE__->has_many( administered_groups => 'Schema::Group', 'account_id' );

1;
8<----------------------------Schema/Group.pm------------------------------
package Schema::Group;

use base qw/DBIx::Class/;

__PACKAGE__->load_components( qw/ Core PK::Auto / );
__PACKAGE__->table( 'groups' );
__PACKAGE__->add_columns( qw/ group_id name account_id / );
__PACKAGE__->set_primary_key( qw/ group_id / );

__PACKAGE__->mk_group_accessors( column => 'member_count' );

__PACKAGE__->belongs_to( account => 'Schema::Account', 'account_id' );
__PACKAGE__->has_many( person => 'Schema::Person', 'group_id' );
#__PACKAGE__->has_many( members => 'Schema::Person', 'group_id' );

1;
8<---------------------------Schema/Person.pm------------------------------
package Schema::Person;

use base qw/DBIx::Class/;

__PACKAGE__->load_components( qw/ Core PK::Auto / );
__PACKAGE__->table( 'people' );
__PACKAGE__->add_columns( qw/ person_id first_name group_id / );
__PACKAGE__->set_primary_key( qw/ person_id / );

__PACKAGE__->belongs_to( 'group', 'Schema::Group', 'group_id' );

1;
8<-----------------------------test_dbic.pl--------------------------------
#!/usr/bin/perl
use strict;
use warnings;

use Schema;

my $schema = Schema->connect( 'DBI:mysql:ifomichev_test', 'root' );
$schema->storage()->debug( 1 );

my $rs = $schema->resultset('Group')->search(
    undef,
    {
        '+select' => [ { COUNT => 'person.person_id' } ],
#        '+select' => [ { COUNT => 'members.person_id' } ],
        '+as'     => [ qw/ member_count / ],
        join  => [ 'person' ],
#        join  => [ 'members' ],
        group_by  => [ qw/ me.group_id / ],
    }
);

$rs = $rs->search(
    undef,
    {
        prefetch  => { account => 'person' },
    },
);

foreach ( $rs->all() ) {
     print "name:    " . $_->name() . "\n";
     print "owner:   " . $_->account()->person()->first_name() . "\n";
     print "members: " . $_->member_count() . "\n";
     print "\n";
}
8<----------------------------------END------------------------------------

Result:

8<---------------------------------BEGIN-----------------------------------
SELECT me.group_id, me.name, me.account_id, COUNT( person.person_id ),
account.account_id, account.username, account.person_id,
person.person_id, person.first_name, person.group_id FROM groups me
LEFT JOIN people person ON ( person.group_id = me.group_id )  JOIN
accounts account ON ( account.account_id = me.account_id )  JOIN
people person_2 ON ( person_2.person_id = account.person_id ) GROUP BY
me.group_id:
name:    foo
owner:   Fred
members: 2

name:    bar
owner:   Barney
members: 1
8<----------------------------------END------------------------------------

We see, that $_->account()->person()->first_name() is taken from the
wrong relationship (person.firstname instead of person_2.firstname).
If we change alias for relationship "Schema::Group->has_many" from
'person' to e. g. 'members' (see commented lines), the code works all
right:

8<---------------------------------BEGIN-----------------------------------
SELECT me.group_id, me.name, me.account_id, COUNT( members.person_id
), account.account_id, account.username, account.person_id,
person.person_id, person.first_name, person.group_id FROM groups me
LEFT JOIN people members ON ( members.group_id = me.group_id )  JOIN
accounts account ON ( account.account_id = me.account_id )  JOIN
people person ON ( person.person_id = account.person_id ) GROUP BY
me.group_id:
name:    foo
owner:   Barney
members: 2

name:    bar
owner:   Fred
members: 1
8<----------------------------------END------------------------------------

Regards,
Ivan