From: Dan Nelson
Date: December 10 2002 9:55pm
Subject: Re: Bug in GROUP BY/CASE/MAX?
List-Archive: http://lists.mysql.com/mysql/127160
Message-Id: <20021210215524.GA43996@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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