>Description:
HAVING Max(x) IS NULL always TRUE.
This has changed since 3.23.39, when if the x column had all Nulls,
Max(x) IS NULL was TRUE and FALSE otherwise.
>How-To-Repeat:
CREATE TABLE test1 (
ID int unsigned auto_increment not null primary key,
data varchar(50)
);
INSERT INTO test1 (data) VALUES
('1'), ('2'), ('3'), ('4');
CREATE TABLES test2 (
ID int unsigned auto_increment not null primary key,
parent_id int unsigned not null references test1,
data enum('Y', 'N') null
);
INSERT INTO test2 (parent_id, data) values
(1, 'Y'), (1, 'N'), (1, Null),
(2, 'Y'), (2, Null),
(3, 'N'), (3, Null),
(4, Null);
SELECT t1.id, Max(t2.data)
FROM test1 t1 left join test2 t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING Max(t2.data) IS NULL;
Above query is returning only (4, Null) on 3.23.38-max and 3.23.39
but it returns (1, Y), (2, Y), (3, N), (4, Null) on 3.23.51
>Fix:
Workaround:
HAVING Max(IfNull(t2.data, '')) = ''
>Submitter-Id: <submitter ID>
>Originator: Oleh Khoma
>Organization:
FineStudio
>MySQL support: none
>Synopsis: HAVING Max(x) IS NULL always TRUE
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.51 (Source distribution)
>Environment:
RedHat Linux 7.0
System: Linux linux1303.dn.net 2.2.16-RAID #6 SMP Fri Jul 7 13:29:16 EDT 2000 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.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2
-mcpu=pentiumpro -felide-constructors' LDFLAGS='-static'
LIBC:
lrwxrwxrwx 1 root root 13 Mar 20 17:07 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 5723311 Dec 8 2001 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27314296 Dec 8 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Dec 8 2001 /usr/lib/libc.so
Configure command: ./configure --enable-assembler --with-other-libc=/usr/local/mysql
'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors'
CXX=gcc LDFLAGS=-static
---------------------------------------------------------------------
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
