RE: Bug in GROUP BY/CASE/MAX?
Well, according to my reading/understanding of SQL ANSI'92,
MAX/MIN/SUM/AVG are supposed to eliminate the NULLs from the
set before doing the appropriate calculation...
Bob Diss, [EMAIL PROTECTED]
===================================================================
>Date: Tue, 10 Dec 2002 15:45:03 -0600
>From: gerald_clark <[EMAIL PROTECTED]>
>To: "'Bob Diss'" <[EMAIL PROTECTED]>
>
>What is the MAX of a known and an unknown value?
>It would be unknown or NULL
>
>'Bob Diss' wrote:
>
>>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, [EMAIL PROTECTED]
>>
>>===================================================================
>>
>>
>>
>>>Date: Tue, 10 Dec 02 13:57:00 EST
>>>From: "'Bob Diss'" <[EMAIL PROTECTED]>
>>>To: "'MySQL List'" <[EMAIL PROTECTED]>
>>>
>>>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, [EMAIL PROTECTED]
>>>
>>>---------------------------------------------------------------------
>>>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 <[EMAIL PROTECTED]>
>>>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>>
>>>
>>>
>>
>>---------------------------------------------------------------------
>>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 <[EMAIL PROTECTED]>
>>To unsubscribe, e-mail <mysql-unsubscribe-gerald_clark=suppliersystems.com-
>@lists.mysql.com>
>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
