List:General Discussion« Previous MessageNext Message »
From:'Bob Diss' Date:December 10 2002 9:41pm
Subject:RE: Bug in GROUP BY/CASE/MAX?
View as plain text  
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?

Bob Diss, rld@stripped

===================================================================

>Date: Tue, 10 Dec 02 13:57:00 EST
>From: "'Bob Diss'" <RLD@stripped>
>To: "'MySQL List'" <mysql@stripped>
>
>Bug in GROUP BY/CASE/MAX?
>
>I'm seeing a strange result when I group rows and using CASE and
>MAX() to select the column I'm interested in (typical pivot-table
>operation).  Here's my sample case:
>
>mysql> -- create table
>mysql> create table t (row int not null,
>                       col int not null,
>                       val varchar(255) not null);
>
>mysql> -- populate with test records
>mysql> insert into t values (1,1,'orange');
>mysql> insert into t values (1,2,'large');
>mysql> insert into t values (2,1,'yellow');
>mysql> insert into t values (2,2,'medium');
>mysql> insert into t values (3,1,'green');
>mysql> insert into t values (3,2,'small');
>
>mysql> -- group by row, extract values where col=1
>mysql> select max(case when col = 1 then val else null end) as color
>       from t group by row;
>
>MySQL shows the result set as:
>
>+--------+
>| color  |
>+--------+
>| orange |
>| NULL   |
>| NULL   |
>+--------+
>3 rows in set (0.00 sec)
>
>I would have expected MySQL to group the records into
>three sets according to row:
>  (1,1,'orange')
>  (1,2,'large')
>
>  (2,1,'yellow')
>  (2,2,'medium')
>
>  (3,1,'green')
>  (3,2,'small')
>
>then, for each set, apply the case expression:
>  (1,1,'orange')  becomes   'orange'
>  (1,2,'large')   becomes   NULL
>
>  (2,1,'yellow')  becomes   'yellow'
>  (2,2,'medium')  becomes   NULL
>
>  (3,1,'green')   becomes   'green'
>  (3,2,'small')   becomes   NULL
>
>then, apply the max() aggregate:
>  max('orange',NULL)   becomes  'orange'
>  max('yellow',NULL)   becomes  'yellow'
>  max('green',NULL)    becomes  'green'
>
>So, I'm expecting to see a result set of three colors. This query
>works as I expect in MS-SQLServer 7.0.  Why doesn't it work the same
>way in MySQL?
>
>I'm running the RPMS from MySQL.com for MySQL-max 3.23.52
>on RedHat 7.3.
>
>tia,
>
>Bob Diss, rld@stripped
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread127138@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-RLD=opn.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
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