SELECT on myisam and innodb empty tables, with unique key, not null columns, fails with error in MyS

Description:
The following SELECT query should return "empty set" and returns ERROR 1048 ('column xx cannot be
null')

How-To-Repeat:
CREATE TABLE t (
a varchar(10) character set latin1 NOT NULL default ''
) TYPE=MyISAM CHARSET=latin1;
CREATE TABLE u (
b varchar(10) character set latin1 NOT NULL default '',
UNIQUE (b)
) TYPE=InnoDB CHARSET=latin1;
select t.a from t,u where t.a=u.b;
ERROR 1048: Column 'b' cannot be null

Tried with all possible myisam/innodb combinations :
t u Result
--------------------------------
myisam innodb ERROR 1048
innodb myisam Empty set
myisam myisam Empty set
innodb innodb Empty set

With above CREATES but without requiring b to be unique : Empty set.
With above CREATES but without requiring b to be NOT NULL : Empty set.
With above CREATES but with t non empty : Empty set.

Fix:
a patch...

Originator: Guilhem Bichot
Synopsis: SELECT on myisam and innodb empty tables, with unique key, not null columns, fails with
error in MySQL4.1
Release: mysql-4.1.0-alpha (Source distribution) from bitkeeper tree pulled 2002-11-13
Server version 4.1.0-alpha-debug-log
System: Linux localhost.localdomain 2.4.18-6mdk #1 Fri Mar 15 02:59:08 CET 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/i586-mandrake-linux-gnu/2.96/specs
gcc version 2.96 20000731 (Mandrake Linux 8.2 2.96-0.76mdk)
Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro
-DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC
-DSAFE_MUTEX -O1' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual
-Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors
-fno-exceptions -fno-rtti -mcpu=pentiumpro -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG
-DFORCE_INIT_OF_VARS -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX -O1' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 4 22:32 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 1275300 Mar 7 2002 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27274138 Mar 7 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Mar 7 2002 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler
--with-extra-charsets=complex --enable-thread-safe-client --with-debug --enable-local-infile
'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat
-Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -DUNIV_MUST_NOT_INLINE
-DEXTRA_DEBUG -DFORCE_INIT_OF_VARS -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX -O1'
'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat
-Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder
-Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti
-mcpu=pentiumpro -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS -DSAFEMALLOC
-DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX -O1' CXX=gcc



___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com

------------------------------------------------------------ ---------
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-thread13010@lists.mysql.com
To unsubscribe, e-mail