Oleh,
> 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;
As temporary solution try to use next query:
SELECT t1.id, Max(t2.data) as MaxData
FROM test1 t1 left join test2 t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING MaxData IS NULL;
Mikhail.
----- Original Message -----
From: "Oleh Khoma" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 14, 2002 10:09 PM
Subject: HAVING Max(x) IS NULL always TRUE.
> >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
>
---------------------------------------------------------------------
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
