Bug #10 SELECT on myisam and innodb empty tables, with unique key, not null columns, fa
Submitted: 2 Dec 2002 20:15 Modified: 19 Apr 2003 5:37
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Any (all)
Assigned to: Sinisa Milivojevic CPU Architecture:Any

[2 Dec 2002 20:15] Sinisa Milivojevic
Description:

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

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.

How to repeat:

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
[19 Apr 2003 5:37] Sinisa Milivojevic
Fixed by some interim push.