[Dbix-class] Problem selecting distinct years from date column

[Peter Rabbitson]

matthew couchman (JIC) wrote:
> Hi,
> 
>  
> 
> I have a table with a date column from which I’m trying to select a list
> of distinct years which aren’t zero. I use this block:
> 
>  
> 
> my $years_rs = $schema->resultset('Reading')->search(
> 
> {
> 
>                     -and => [
> 
>                                 date => { '!=' => 0 },
> 
>                                 date => { '!=' => undef }
> 
>                     ]
> 
>                 },
> 
>                 {
> 
>                     select => [ { YEAR => 'date' }],
> 
>                     as => [ 'year' ],
> 
>                     distinct => 1
> 
>                 }
> 
>  );
> 
> my @years = $years_rs->get_column('year')->all;
> 
>  
> 
> I’ve set DBIX_CLASS_STORAGE_DBI_DEBUG=1 and when I run the above code it
> reports:
> 
>  
> 
> SELECT YEAR( date ) FROM colour_reading me WHERE ( ( date != ? AND date
> IS NOT NULL ) ) GROUP BY YEAR( date ): '0'
> 
>  
> 
> which looks right but when I inspect @years it contains 0, 2008, 2009 so
> the WHERE condition hasn’t worked. If I cut and paste the query to MySQL
> Query Browser, substitute the ‘?’ for the ‘0’ and run on the same server
> then I get the correct result i.e. 2008, 2009.
> 
>  
> 
> I’m puzzled, can anybody help?
> 

No idea - it works here: http://dev.catalyst.perl.org/svnweb/bast/revision?rev=7976

Please try against your mysql server.