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