Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 59219
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: aleksey.mashanov [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08124



Subject: DBIx::Class::Row->find( \%query )
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 773
Download (untitled) / with headers
text/plain 773b
Method DBIx::Class::Row->find() dosn't handle undef value in unique keys correctly. I have following lines in Domain result class: __PACKAGE__->set_primary_key('id'); __PACKAGE__->add_unique_constraint('domains_unique_key', ['unique_key']); And DBIx::Class::Row->insert() implicitly calls DBIx::Class::Row->find_or_create() from related class passing it hashref with all columns, like this: Schema->resultset("Domain")->find({ id => 10, unique_key => undef }) As result DBIx::Class::Result->find() construct following query: SELECT "me"."id", "me"."unique_key" FROM "domains" "me" WHERE ( ( "me"."id" = ? OR "me"."unique_key" IS NULL ) ): '10' It's incorrect. Database unique constraint's columns can contain NULL values, and query IS NULL can return more than one row.
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-2369-1279894355-1868.59219-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1005
Download (untitled) / with headers
text/plain 1005b
On Fri Jul 09 08:38:14 2010, amashanov wrote: Show quoted text
> Method DBIx::Class::Row->find() dosn't handle undef value in unique keys > correctly. > > I have following lines in Domain result class: > __PACKAGE__->set_primary_key('id'); > __PACKAGE__->add_unique_constraint('domains_unique_key', ['unique_key']); > > And DBIx::Class::Row->insert() implicitly calls > DBIx::Class::Row->find_or_create() from related class passing it hashref > with all columns, like this: > Schema->resultset("Domain")->find({ id => 10, unique_key => undef }) > > As result DBIx::Class::Result->find() construct following query: > SELECT "me"."id", "me"."unique_key" FROM "domains" "me" WHERE ( ( > "me"."id" = ? OR "me"."unique_key" IS NULL ) ): '10' > > It's incorrect. Database unique constraint's columns can contain NULL > values, and query IS NULL can return more than one row.
I am almost positive we had this fixed a long time ago. What version of DBIC is this? Also please provide the full invocation that causes this SQL?
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-2369-1279894355-1868.59219-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-2369-1279894355-1868.59219-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-2366-1280146171-1439.59219-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 3637
Download (untitled) / with headers
text/plain 3.5k
Птн Июл 23 10:12:35 2010, RIBASUSHI писал: Show quoted text
> I am almost positive we had this fixed a long time ago. What version of > DBIC is this? Also please provide the full invocation that causes this
SQL? At time I posted this bug, I tried last available version of DBIx::Class, but result was the same. Here is some additional tracing information which can be useful (using DBIx::Class version 0.08118 and with carp replaced into Carp::cluck in DBIx::Class::Storage::DBI): $ PERL5LIB=$PERL5LIB:~/debuglib DBIC_TRACE=1 perl -MMCM::Schema -we 'my $d = MCM::Schema->resultset("Domain")->new({ occup_id => 1, user_id => 7768, name => "domain-name.tld", status => 2, status_f => 1, unique_key => undef, cdate => "now", mdate => "now" }); my $s = $d->new_related( "services", { shortname => "http", entity_id => 2195, status => 2, status_f => 1, stype => 2, tarif_id => 1, lbill_id => 106221, cdate => "now", mdate => "now" } ); $d->insert(); $s->insert()' SELECT "me"."id", "me"."fullname", "me"."simplename", "me"."stype", "me"."shortname", "me"."control_module", "me"."callback_module", "me"."solo", "me"."editable", "me"."closed", "me"."dealer", "me"."cp_show", "me"."cp_edit", "me"."cp_add", "me"."backlink_module", "me"."allow_give", "me"."always_calc_balance", "me"."allow_status_change", "me"."cp_status_change", "me"."params_class", "me"."service_class" FROM "service_types" "me" WHERE ( "me"."shortname" = ? ): 'http' INSERT INTO "domains" ( "cdate", "mdate", "name", "occup_id", "status", "status_f", "unique_key", "user_id") VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ): 'now', 'now', 'domain-name.tld', '1', '2', '1', 'NULL', '7768' SELECT "me"."id", "me"."name", "me"."user_id", "me"."status", "me"."cdate", "me"."status_f", "me"."flag", "me"."parent_id", "me"."comment", "me"."deact_by_user", "me"."occup_id", "me"."unique_key", "me"."mdate", "me"."del_reason", "me"."auto_prolongation", "me"."delayed_delete", "me"."rdate", "me"."token_date", "me"."name_ml", "me"."user_comment" FROM "domains" "me" WHERE ( ( "me"."id" = ? OR "me"."unique_key" IS NULL ) ): '798894' Query returned more than one row. SQL that returns multiple rows is DEPRECATED for ->find and ->single at /home/amashanov/debuglib/DBIx/Class/Storage/DBI.pm line 1947 DBIx::Class::Storage::DBI::select_single('DBIx::Class::Storage::DBI::Pg=HASH(0x868ac30)', 'ARRAY(0xa128478)', 'ARRAY(0x9ed4f28)', 'ARRAY(0x9edc9fc)', 'HASH(0xa039424)') called at /home/amashanov/debuglib/DBIx/Class/Storage/DBI.pm line 72 DBIx::Class::Storage::DBI::select_single('DBIx::Class::Storage::DBI::Pg=HASH(0x868ac30)', 'ARRAY(0xa128478)', 'ARRAY(0x9ed4f28)', 'ARRAY(0x9edc9fc)', 'HASH(0xa039424)') called at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSet.pm line 745 DBIx::Class::ResultSet::single('MCM::Schema::ResultSet::Domain=HASH(0xa03994c)') called at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSet.pm line 540 DBIx::Class::ResultSet::find('MCM::Schema::ResultSet::Domain=HASH(0x9ed4fb8)', 'HASH(0xa044ff0)') called at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSet.pm line 2248 DBIx::Class::ResultSet::find_or_create('MCM::Schema::ResultSet::Domain=HASH(0x9ed4fb8)', 'HASH(0xa044ff0)') called at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 304 DBIx::Class::Row::insert('MCM::Schema::Result::Service::http=HASH(0x9ed4e8c)') called at -e line 1 INSERT INTO "services" ( "cdate", "domain", "entity_id", "lbill_id", "mdate", "shortname", "status", "status_f", "stype", "tarif_id") VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ): 'now', '791646', '2195', '106221', 'now', 'http', '2', '1', '2', '1'
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-2366-1280146171-1439.59219-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-2369-1279894355-1868.59219-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-2366-1280146171-1439.59219-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-29756-1281268722-1899.59219-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 3873
Download (untitled) / with headers
text/plain 3.7k
On Mon Jul 26 08:09:31 2010, amashanov wrote: Show quoted text
> Птн Июл 23 10:12:35 2010, RIBASUSHI писал:
> > I am almost positive we had this fixed a long time ago. What version
> of
> > DBIC is this? Also please provide the full invocation that causes
> this > SQL? > > At time I posted this bug, I tried last available version of > DBIx::Class, but result was the same. Here is some additional tracing > information which can be useful (using DBIx::Class version 0.08118 and > with carp replaced into Carp::cluck in DBIx::Class::Storage::DBI): > > $ PERL5LIB=$PERL5LIB:~/debuglib DBIC_TRACE=1 perl -MMCM::Schema -we > 'my > $d = MCM::Schema->resultset("Domain")->new({ occup_id => 1, user_id => > 7768, name => "domain-name.tld", status => 2, status_f => 1, > unique_key > => undef, cdate => "now", mdate => "now" }); my $s = $d->new_related( > "services", { shortname => "http", entity_id => 2195, status => 2, > status_f => 1, stype => 2, tarif_id => 1, lbill_id => 106221, cdate => > "now", mdate => "now" } ); $d->insert(); $s->insert()' > SELECT "me"."id", "me"."fullname", "me"."simplename", "me"."stype", > "me"."shortname", "me"."control_module", "me"."callback_module", > "me"."solo", "me"."editable", "me"."closed", "me"."dealer", > "me"."cp_show", "me"."cp_edit", "me"."cp_add", "me"."backlink_module", > "me"."allow_give", "me"."always_calc_balance", > "me"."allow_status_change", "me"."cp_status_change", > "me"."params_class", "me"."service_class" FROM "service_types" "me" > WHERE ( "me"."shortname" = ? ): 'http' > INSERT INTO "domains" ( "cdate", "mdate", "name", "occup_id", > "status", > "status_f", "unique_key", "user_id") VALUES ( ?, ?, ?, ?, ?, ?, ?, ? > ): > 'now', 'now', 'domain-name.tld', '1', '2', '1', 'NULL', '7768' > SELECT "me"."id", "me"."name", "me"."user_id", "me"."status", > "me"."cdate", "me"."status_f", "me"."flag", "me"."parent_id", > "me"."comment", "me"."deact_by_user", "me"."occup_id", > "me"."unique_key", "me"."mdate", "me"."del_reason", > "me"."auto_prolongation", "me"."delayed_delete", "me"."rdate", > "me"."token_date", "me"."name_ml", "me"."user_comment" FROM "domains" > "me" WHERE ( ( "me"."id" = ? OR "me"."unique_key" IS NULL ) ): > '798894' > Query returned more than one row. SQL that returns multiple rows is > DEPRECATED for ->find and ->single at > /home/amashanov/debuglib/DBIx/Class/Storage/DBI.pm line 1947 > >
DBIx::Class::Storage::DBI::select_single('DBIx::Class::Storage::DBI::Pg=HASH(0x868ac30)', Show quoted text
> 'ARRAY(0xa128478)', 'ARRAY(0x9ed4f28)', 'ARRAY(0x9edc9fc)', > 'HASH(0xa039424)') called at > /home/amashanov/debuglib/DBIx/Class/Storage/DBI.pm line 72 > >
DBIx::Class::Storage::DBI::select_single('DBIx::Class::Storage::DBI::Pg=HASH(0x868ac30)', Show quoted text
> 'ARRAY(0xa128478)', 'ARRAY(0x9ed4f28)', 'ARRAY(0x9edc9fc)', > 'HASH(0xa039424)') called at > /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSet.pm line 745 > >
DBIx::Class::ResultSet::single('MCM::Schema::ResultSet::Domain=HASH(0xa03994c)') Show quoted text
> called at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSet.pm > line 540 > >
DBIx::Class::ResultSet::find('MCM::Schema::ResultSet::Domain=HASH(0x9ed4fb8)', Show quoted text
> 'HASH(0xa044ff0)') called at > /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSet.pm line 2248 > >
DBIx::Class::ResultSet::find_or_create('MCM::Schema::ResultSet::Domain=HASH(0x9ed4fb8)', Show quoted text
> 'HASH(0xa044ff0)') called at > /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 304 > >
DBIx::Class::Row::insert('MCM::Schema::Result::Service::http=HASH(0x9ed4e8c)') Show quoted text
> called at -e line 1 > INSERT INTO "services" ( "cdate", "domain", "entity_id", "lbill_id", > "mdate", "shortname", "status", "status_f", "stype", "tarif_id") > VALUES > ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ): 'now', '791646', '2195', '106221', > 'now', 'http', '2', '1', '2', '1'
Indeed I now see the problem, a fix will be devised in the next couple of weeks.
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-29756-1281268722-1899.59219-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-2369-1279894355-1868.59219-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-2366-1280146171-1439.59219-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-29756-1281268722-1899.59219-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-2361-1287273186-1059.59219-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 408
Download (untitled) / with headers
text/plain 408b


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.