Bug in GROUP BY/MAX/CASE
am 11.12.2002 16:04:00 von RLD>Description:
Bug with GROUP BY/CASE/MAX in MySQL-Max-3.23.53a
I'm seeing a bug when I group rows using MAX(CASE) to select the
column I'm interested in (typical pivot-table operation). It appears
that the NULL values are not being eliminated from the group, but
instead are passing through and confusing the CASE expression.
>How-To-Repeat:
mysql> -- create test 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;
+--------+
| color |
+--------+
| orange |
| NULL |
| NULL |
+--------+
3 rows in set (0.00 sec)
The same query using the alternate CASE syntax also returns the
wrong rows:
mysql> select max(case col when 1 then val else null end) as color
from t group by row;
+--------+
| 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, eliminate the NULL values and apply the max() aggregate:
max('orange',NULL) becomes 'orange'
max('yellow',NULL) becomes 'yellow'
max('green',NULL) becomes 'green'
Note that the MySQL-extension IF() can be used to simulate the
case expression (thank you Dan Nelson for pointing this out).
When this is used, the query shows the correct result, as in:
mysql> select max(if(col = 1,val,null)) as color from t group by row;
+--------+
| color |
+--------+
| orange |
| yellow |
| green |
+--------+
3 rows in set (0.10 sec)
I don't know if this means CASE is broken, or if something else is
the problem.
>Fix:
unknown
>Submitter-Id:
>Originator: Bob Diss
>Organization: OPN Systems, Inc.
>MySQL support: none
>Synopsis: MAX(CASE) doesn't handle NULL values properly
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.53a (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.53a, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.53a-Max-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 17 hours 14 min 50 sec
Threads: 5 Questions: 420 Slow queries: 0 Opens: 49 Flush tables: 1 Open tables: 40 Queries per second avg: 0.007
>Environment:
System: Linux advdev.opn.com 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 2 15:33 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 2 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/info --includedir=/usr/include --mandir=/usr/share/man '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc
------------------------------------------------------------ ---------
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 bugs-thread13259@lists.mysql.com
To unsubscribe, e-mail