>Description:
When performing a left join with a table including a non-null
datetime column, saying "where a.non_null_datetime is null" always
returns the empty set. It works if the column is either not non-null
or not datetime. This used to work (on 3.22.32).
>How-To-Repeat:
create table a (
id int primary key
);
create table b (
id int primary key,
dt datetime not null,
f int not null
);
insert into a values (150);
# this works
select a.id from a left join b using (id) where b.f is null;
# this works
select a.id from a left join b using (id) where b.id is null;
# this doesn't work
select a.id from a left join b using (id) where b.dt is null;
>Fix:
Choose a field other than a datetime field.
>Submitter-Id: Nick Barnes <[EMAIL PROTECTED]>
>Originator: Nick Barnes
>Organization:
Ravenbrook Limited
>MySQL support: none
>Synopsis: left join where not_null_datetime is null fails
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.37 (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.19 Distrib 3.23.37, 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.37
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 9 hours 59 sec
Threads: 1 Questions: 470 Slow queries: 0 Opens: 172 Flush tables: 1 Open tables:
13 Queries per second avg: 0.014
>Environment:
System: Linux swan.ravenbrook.com 2.2.14-5.0 #9 Tue Oct 10 19:15:41 BST 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/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
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 Oct 9 2000 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20272704 Feb 29 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 29 2000 /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'
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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
