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

[Jason Kohles]

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?


-- 
Jason Kohles, RHCA RHCDS RHCE
email at jasonkohles.com - http://www.jasonkohles.com/
"A witty saying proves nothing."  -- Voltaire