List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 10 2002 9:55pm
Subject:Re: Bug in GROUP BY/CASE/MAX?
View as plain text  
In the last episode (Dec 10), 'Bob Diss' said:
> RE: Bug in GROUP BY/CASE/MAX?
> 
> More info -- it seems that if I change the 'NULL' to ''
> (i.e. the empty string) in the CASE statements, I get the
> result I'm looking for.  That is, the query:
> 
> mysql> select max(case when col = 1 then val else '' end) as color
>        from t group by row;
> 
> returns the three rows 'orange', 'yellow', and 'green'.
> 
> Why is that?  Is something broken when MySQL calculates the
> aggregate for a column that contains a NULL value?  Is there a doc
> to explain how an aggregate function should work in the presence
> of NULL?

That looks like a bug to me.  The equivalent syntax does work, though:

select max(if(col=1,val,NULL)) as color from t group by row;

-- 
	Dan Nelson
	dnelson@stripped
Thread
Bug in GROUP BY/CASE/MAX?'Bob Diss'10 Dec
RE: Bug in GROUP BY/CASE/MAX?'Bob Diss'10 Dec
  • Re: Bug in GROUP BY/CASE/MAX?gerald_clark10 Dec
  • Re: Bug in GROUP BY/CASE/MAX?Dan Nelson10 Dec
Re: Bug in GROUP BY/CASE/MAX?'Bob Diss'10 Dec