>Description:
Left outer join on null datetime column produces incorrect result.
The 2nd select below produces no rows, which is incorrect and inconsistent
with the fourth select which produces 1 row.
I get the same results with server version 3.23.41 - binary version from
mysql.com
>How-To-Repeat:
create table foo (a int);
insert into foo values(1);
insert into foo values(2);
create table bar1 (a int, b datetime not null);
insert into bar1 values(1, '25-12-2001');
create table bar2 (a int, b datetime);
insert into bar2 values(1, '25-12-2001');
create table bar3 (a int, b char(20) not null);
insert into bar3 values(1, 'Wensleydale');
select * from foo left outer join bar1 on foo.a=bar1.a;
select * from foo left outer join bar1 on foo.a=bar1.a where b is null;
select * from foo left outer join bar2 on foo.a=bar2.a where b is null;
select * from foo left outer join bar3 on foo.a=bar3.a where b is null;
drop table foo;
drop table bar1;
drop table bar2;
drop table bar3;
>Fix:
Convert the datetime column to nullable? Not really a fix since it breaks the
schema.
>Submitter-Id: <submitter ID>
>Originator: Jon Barker
>Organization:
Ingenta PLC
>MySQL support: none
>Synopsis: LEFT OUTER JOIN where not null datetime column is null fails.
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.39 (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.39, 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.39
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 37 days 20 hours 51 min 15 sec
Threads: 1 Questions: 1855361 Slow queries: 4 Opens: 279 Flush tables: 3 Open
tables: 8 Queries per second avg: 0.567
>Environment:
System: Linux ninetyseven 2.2.19-7.0.1 #2 SMP Fri Sep 28 18:00:32 GMT 2001 i686 unknown
Architecture: i686
Also on
Linux Mandrake release 8.0 (Traktopel) for i586
Kernel 2.4.3-20mdk on an i686 (AMD 1 CPU)
Some paths: /usr/local/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-85)
Compilation info: CC='egcs' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='egcs' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Nov 28 13:00 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 5724759 Oct 3 21:05 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27260368 Oct 3 21:03 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Oct 3 21:03 /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 --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/info --includedir=/usr/include --mandir=/usr/man
'--with-comment=Official MySQL RPM'
---------------------------------------------------------------------
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
