[Dbix-class] Unexpected behavior with possible NULL foreign key relationship

[mike]

[ sorry for the CC, but my last attempt to post to the list never made it ]

On Sun, Apr 6, 2008 at 2:08 PM, Matt S Trout <dbix-class at trout.me.uk> wrote:
> On Fri, Mar 21, 2008 at 03:14:40PM -0700, Damon Snyder wrote:
>  > Hi Everyone,
>  > I'm new to the list so bear with me if I'm missing something. I have a
>  > foreign key relationship where it's possible that the belongs_to
>  > relationship is NULL. See the classes below. When I create a new
>  > Smsmessage object e.g. like so:
>  >
>  > my $sms2 = $m->resultset('Smsmessages')->create(
>  >       {
>  >               subject => 'Some subject',
>  >               message => 'a message',
>  >               channel_id => $chan2->id,
>  >               #channel_keyword_id => undef,
>  >               inception => scalar localtime(time()),
>  >               expiration => scalar localtime(time() + (60*60*24)),
>  >               sent => 0,
>  >               retries => 0,
>  >               provider_id => $prov->id,
>  >       }
>  >       );
>  >
>  > If I leave out the channel_keyword_id => undef field, I will get a
>  > defined value for $sms2->channel_keyword. If I trace it, the following
>  > select ends up happening:
>  >
>  > SELECT me.id, me.okid, me.keyword, me.channel_id FROM channel_keywords
>  > me:
>  >
>  > Which returns a value that gets plugged the $sms2->channel_keyword
>  > field. Is this the expected behavior?  The default for this field is
>  > defined as undef below. If a value is not specified in the create()
>  > shouldn't it be given the default value (in this case NULL and not a
>  > random record)? I'm not quite sure I understand what is going on, but
>  > it seems like the new value is not getting an equivalent of NULL in
>  > the new object  unless I explicitly say channel_keyword_id => undef. I
>  > understand that it is good practice to set this value to NULL on
>  > creation, but I'm wondering if the behavior I'm seeing is to be
>  > expected.
>
>  That's very odd. Could you try and produce a minimal set of classes that
>  exhibit this or better still make it happen against the DBICTest schema
>  in the DBIx::Class dist?

i already sent a message regarding this back on 4/2.  from the looks of things,
it never made it to the list.  following is the text of the message
with the patch
included with the text/plain section, just in case messages with attachments
are rejected sans notification.

it doesn't matter if you use add_relationship or belongs_to.  it happens in
both scenarios.

-mike

--- snip --- snip --- snip ---

On Wed, Apr 2, 2008 at 3:52 PM, mike <pulsation at gmail.com> wrote:
> attached is a patch which includes a test that shows the broken single
>  accessor in DBIx::Class 0.08008 and 0.08010.  DBIx::Class 0.08009 was
>  not tested, but DBIx::Class 0.08007 works fine.  i'm not sure exactly
>  what's going on here, but this behavior has appeared in DBIx::Class
>  before.  it breaks the holy hell out of all of our applications in a
>  very nasty way.  with sharp, pointy teeth.  look at the bones!
>
>  in short, the aforementioned versions of DBIx::Class cause (at least
>  some) single accessors to return inflated objects for fields that are
>  NULL.
>
>  if we are using accessor => single in some obtuse way, education is
>  welcome; however, it would be in everyone's best interests if this
>  behavior didn't change between releases, so i highly recommend that
>  a similar version to this test be added to the default test suite.
>
>  -mike
>
>  --- output of test using 0.08007 from default library path ---
>
>  $ PERL5LIB=t/lib perl t/broken_single_accessor.t
>  1..2
>  ok 1 - genreid is NULL
>  ok 2 - genre accessor returns undef
>
>  --- output of test using DBIx::Class from svn ---
>
>  $ PERL5LIB=t/lib:lib/ perl t/broken_single_accessor.t
>  1..2
>  ok 1 - genreid is NULL
>  not ok 2 - genre accessor returns undef
>  #   Failed test 'genre accessor returns undef'
>  #   at t/broken_single_accessor.t line 17.
>  # Looks like you failed 1 test of 2.
>

--- snip --- snip --- snip ---

Index: t/broken_single_accessor.t
===================================================================
--- t/broken_single_accessor.t	(revision 0)
+++ t/broken_single_accessor.t	(revision 0)
@@ -0,0 +1,18 @@
+use strict;
+use warnings;
+
+use Test::More;
+use lib qw(t/lib);
+use DBICTest;
+
+plan tests => 2;
+
+my $schema = DBICTest->init_schema();
+
+my $artist = $schema->resultset('Artist')->create({ artistid => 666,
name => 'bad religion' });
+my $genre = $schema->resultset('Genre')->create({ name => 'disco' });
+my $cd = $schema->resultset('CD')->create({ cdid => 187, artist => 1,
title => 'how could hell be any worse?', year => 1982 });
+
+ok(!defined($cd->genreid), 'genreid is NULL');
+ok(!defined($cd->genre), 'genre accessor returns undef');
+
Index: t/lib/sqlite.sql
===================================================================
--- t/lib/sqlite.sql	(revision 4250)
+++ t/lib/sqlite.sql	(working copy)
@@ -90,10 +90,19 @@
   cdid INTEGER PRIMARY KEY NOT NULL,
   artist integer NOT NULL,
   title varchar(100) NOT NULL,
-  year varchar(100) NOT NULL
+  year varchar(100) NOT NULL,
+  genreid integer
 );

 --
+-- Table: genre
+--
+CREATE TABLE genre (
+  genreid INTEGER PRIMARY KEY NOT NULL,
+  name varchar(100) NOT NULL
+);
+
+--
 -- Table: bookmark
 --
 CREATE TABLE bookmark (
Index: t/lib/DBICTest/Schema/CD.pm
===================================================================
--- t/lib/DBICTest/Schema/CD.pm	(revision 4250)
+++ t/lib/DBICTest/Schema/CD.pm	(working copy)
@@ -20,6 +20,9 @@
     data_type => 'varchar',
     size      => 100,
   },
+  'genreid' => {
+    data_type => 'integer'
+  }
 );
 __PACKAGE__->set_primary_key('cdid');
 __PACKAGE__->add_unique_constraint([ qw/artist title/ ]);
@@ -45,4 +48,9 @@
     { order_by => 'producer.name' },
 );

+__PACKAGE__->add_relationship('genre', 'DBICTest::Schema::Genre',
+    { 'foreign.genreid' => 'self.genreid' },
+    { 'accessor' => 'single' }
+);
+
 1;
Index: t/lib/DBICTest/Schema/Genre.pm
===================================================================
--- t/lib/DBICTest/Schema/Genre.pm	(revision 0)
+++ t/lib/DBICTest/Schema/Genre.pm	(revision 0)
@@ -0,0 +1,11 @@
+package DBICTest::Schema::Genre;
+
+use strict;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table('genre');
+__PACKAGE__->add_columns(qw/genreid name/);
+__PACKAGE__->set_primary_key('genreid');
+
+1;
Index: t/lib/DBICTest/Schema.pm
===================================================================
--- t/lib/DBICTest/Schema.pm	(revision 4250)
+++ t/lib/DBICTest/Schema.pm	(working copy)
@@ -11,6 +11,7 @@
   Employee
   CD
   FileColumn
+  Genre
   Link
   Bookmark
   #dummy

--- snip --- snip --- snip ---