[Dbix-class] Problem with count()

[Matt S Trout]

Lorenzo Marcantonio wrote:
> I'm having a trouble using count()... If I try to do (with DBI_TRACE):
> 
> --------
> my $versione_nota = $dbic->resultset('VersioneNotaLt')->search({
> 	ser_num => $ser_num,
> 	mdsrel_id_req => $mdsrel_id_req})->count();
> 
>      <- prepare_cached('SELECT COUNT( * ) FROM versione_nota_lt me WHERE 
> ( mdsrel_id_req = ?
> AND ser_num = ? )' HASH(0x9ed224c) ...)= DBI::st=HASH(0x9e935fc) at 
> DBI.pm line 839
>      <- execute(1 '2')= 1 at DBI.pm line 743
>      <- fetchrow_array= ( '1' ) [1 items] row1 at Cursor.pm line 86
>      <- FETCH('Active')= 1 at Cursor.pm line 163
>      <- finish= 1 at Cursor.pm line 163
> --------
> 
> 
> it returns 1 even if there are NO RECORDS matching! If I instead do:
> 
> --------
> my @versione_nota_set = $dbic->resultset('VersioneNotaLt')->search({
>          ser_num => $ser_num, mdsrel_id_req => $mdsrel_id_req});
> my $versione_nota = $#versione_nota_set;
> 
>      <- prepare_cached('SELECT me.ser_num, me.mdsrel_id_req FROM 
> versione_nota_lt me WHERE ( mdsrel_id_req = ? AND ser_num = ? )' 
> HASH(0x9eb7ae0) ...)= DBI::st=HASH(0x9e935e8) at DBI.pm line 839
>      <- execute(1 '2')= 1 at DBI.pm line 743
>      <- fetchall_arrayref= [ ARRAY(0x9eb7ae0) ] row1 at Cursor.pm line 119
> --------
> 
> (which is, of course, a bad way since I only need the count!), results 
> are correct...
> 
> The SQL generated is fine in both cases, IMHO. Running the statement as 
> sql gives good results...
> 
> DBIx::Class was as of today CPAN release, database is postgres-8.1.3, 
> I'm running under catalyst...
> 
> VersioneNotaLt works from a view and the schema is trivial:
> 
> --------
> package DLService::Schema::DLService::VersioneNotaLt;
> use strict;
> use warnings;
> 
> use base 'DBIx::Class';
> 
> __PACKAGE__->load_components("PK::Auto", "Core");
> __PACKAGE__->table('versione_nota_lt');
> __PACKAGE__->add_columns(qw{ ser_num mdsrel_id_req });
> 
> 1;
> --------
> 
> Any suggestion? I'm doing something wrong, there is a bug somewhere or I 
> have to live with the workaround?

Your code looks fine to me var that you should be doing ->set_primary_key. 
Given the odd data return is shown by the DBI trace, try running the query via 
a quick raw DBI script.

Also note that 0.07+ respect the env var DBIC_TRACE