[Dbix-class] Problems with belongs_to/might_have on columns with null values.

Zbigniew Lukasiak zzbbyy at gmail.com
Fri Sep 19 09:54:56 BST 2008


On Fri, Sep 19, 2008 at 2:41 AM, Jason Kohles <email at jasonkohles.com> wrote:
> If you are one of the people who is getting tired of me popping up on IRC
> every couple of weeks to try once again to track down (or at least
> positively identify) this problem, you are in luck!  I've finally isolated
> exactly what the problem I'm running into is.
>
> The problem is with defining a belongs_to or might_have relationship where
> the column can contain a null.  In my case, the example I've been using from
> my application is a timezone_id column that refers to a table of timezones,
> which can be null because I'm getting data from multiple sources and at the
> time that the record is created, I may not have any idea what time zone the
> object is in.
>
> package MyApp::DB::Object;
> use parent 'DBIx::Class';
> __PACKAGE__->table( 'objects' );
> __PACKAGE__->add_columns(
>    id => { data_type => 'serial' },
>    name => { data_type => 'varchar', size => 64 },
>    timezone_id => { data_type => 'integer' },
> );
> __PACKAGE__->set_primary_key( 'id' );
> __PACKAGE__->belongs_to( 'timezone', 'MyApp::DB::Timezone', 'timezone_id' );
>
> package MyApp::DB::Timezone;
> use parent 'DBIx::Class';
> use overload '""' => 'name', fallback => 1;
> __PACKAGE__->table( 'timezones' );
> __PACKAGE__->add_columns(
>    id => { data_type => 'serial' },
>    name => { data_type => 'varchar', size => 64 },
> );
> __PACKAGE__->has_many( 'objects', 'MyApp::DB::Object', 'timezone_id' );
>
>
>
> Now, given a script like this:
>
> #!/usr/local/bin/perl -w
> use strict;
> use MyApp::DB;
>
> my $schema = MyApp::DB->connect;
> my $object = $schema->resultset( 'Object' )->create( { name => 'no
> timezone!' } );
> print $object->name,"\n";
> print $object->timezone_id,"\n";
> print $object->timezone,"\n";
>
>
> What would you expect to happen?  Something like this?
>
> no timezone!
> Use of uninitialized value in concatenation (.) or string at ...
> Use of uninitialized value in concatenation (.) or string at ...
>
> Yeah, me too.  What actually happens is quite a bit different though...
>
> no timezone!
> Use of uninitialized value in concatenation (.) or string at ...
> Africa/Abidjan
>
> I've tracked the problem down to a this commit...
>
> ------------------------------------------------------------------------
> r3804 | captainL | 2007-10-04 16:54:08 -0400 (Thu, 04 Oct 2007) | 1 line
>
> fixed search_related from object with unset FK behaviour
> ------------------------------------------------------------------------
>
> Which contains (excluding tests) only a one-line change to resolve_condition
> in DBIx::Class::ResultSource, which is the source of my problems:
>
> --- lib/DBIx/Class/ResultSource.pm      (revision 3803)
> +++ lib/DBIx/Class/ResultSource.pm      (revision 3804)
> @@ -790,7 +790,7 @@
>         $self->throw_exception("Invalid rel cond val ${v}");
>       if (ref $for) { # Object
>         #warn "$self $k $for $v";
> -        $ret{$k} = $for->get_column($v);
> +        $ret{$k} = $for->get_column($v) if $for->has_column_loaded($v);
>         #warn %ret;
>       } elsif (!defined $for) { # undef, i.e. "no object"
>         $ret{$k} = undef;
>
> However, what happens in my case is that when related_resultset calls
> resolve_condition, the null column gets skipped, so instead of the related
> query being built as 'SELECT me.id,me.name FROM timezones me WHERE id IS
> NULL' I end up with just 'SELECT me.id,me.name FROM timezones', and in this
> particular case, the first result returned (out of a couple thousand) is for
> 'Africa/Abidjan'.
>
>
> The commit that made this change included 4 new tests, which are:
>
> my $undef_artist_cd = $schema->resultset("CD")->new_result({ 'title' =>
> 'badgers', 'year' => 2007 });
> is($undef_artist_cd->has_column_loaded('artist'), '', 'FK not loaded');
> is($undef_artist_cd->search_related('artist')->count, 3, 'open search on
> undef FK');
>
> my $def_artist_cd = $schema->resultset("CD")->new_result({ 'title' =>
> 'badgers', 'year' => 2007, artist => undef });
> is($def_artist_cd->has_column_loaded('artist'), 1, 'FK loaded');
> is($def_artist_cd->search_related('artist')->count, 0, 'closed search on
> null FK');
>
> Reverting the code change only causes the 'open search on undef FK' test to
> fail, but I can't quite seem to wrap my head around the fact that this
> behavior seems to be the *intended* result of this patch, is there a use
> case that I'm not seeing where having a relationship return the entire
> contents of the related table is desirable?
>
>

Hi,

Did you check the latest svn version?  This looks related to the bug
that I reported many times here - and then described in
http://perlalchemy.blogspot.com/2008/06/dbixclass-gotchas.html (Don't
call $row->some_relation when the $row has not loaded the columns
required to resolve some_relation).  One of recent revisions fixed
that one (but still left one test todo in t/66relationship.t) and the
test run currently is:

my $undef_artist_cd = $schema->resultset("CD")->new_result({ 'title'
=> 'badgers', 'year' => 2007 });
is($undef_artist_cd->has_column_loaded('artist'), '', 'FK not loaded');
is($undef_artist_cd->search_related('artist')->count, 0, '0=1 search
when FK does not exist and object not yet in db');


-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/



More information about the DBIx-Class mailing list