Bug #2508 1048 - column 'name' cannot be null (error with union and left join)
Submitted: 25 Jan 2004 23:12 Modified: 6 Feb 2004 10:50
Reporter: david hodge Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0 and 4.1.1a OS:Windows (windows XP)
Assigned to: MySQL Verification Team CPU Architecture:Any

[25 Jan 2004 23:12] david hodge
Description:
null values in selected columns for left side of join cause error:
"1048 column 'name' cannot be null"

Identical query/table works correctly in 4.0.16 on linux.

This happens with and without indexes.  If all fields are changed to ALLOW NULL, then different error occurs:

"A component named name already exists".

How to repeat:
create table tbltest (
   RID int(11) not null default '0',
   IID int(11) not null default '0',
   SECTION not null varchar(50),
   NAME not null varchar(50),
   PHONE not null varchar(50))
   type=MyISAM;

#----------------------------
# Records for table tbltest
#----------------------------

insert into tbltest ( RID,IID,SECTION,NAME,PHONE) values
(1, 1, 'main', 'a', '111'),
(2, 1, 'main', 'b', '222'),
(3, 1, 'main', 'c', '333'),
(4, 1, 'main', 'd', '444'),
(5, 1, 'main', 'e', '555'),
(6, 2, 'main', 'c', '333'),
(7, 2, 'main', 'd', '454'),
(8, 2, 'main', 'e', '555'),
(9, 2, 'main', 'f', '666'),
(10, 2, 'main', 'g', '777');

#----------------------------
# Sample Query
#----------------------------

select
A.NAME, A.PHONE, B.NAME, B.PHONE
from tbltest A left join tbltest B on A.NAME = B.NAME and B.IID = 2
where A.IID = 1
and (A.PHONE <> B.PHONE or isnull(B.NAME))

union

select
A.NAME, A.PHONE, B.NAME, B.PHONE
from tbltest B left join tbltest A on B.NAME = A.NAME and A.IID = 1
where B.IID = 2
and (A.PHONE <> B.PHONE or isnull(A.NAME))

Suggested fix:
The query should return (and does on 4.0 linux):

A.NAME     A.PHONE     B.NAME      B.PHONE
--------------------------------------------
a          111
--------------------------------------------
b          222
--------------------------------------------
d          444         d           454
--------------------------------------------
                       f           666
--------------------------------------------
                       g           777
--------------------------------------------
[28 Jan 2004 8:13] MySQL Verification Team
A patch that fixes a problem has been sent and proposed as a solution.
[6 Feb 2004 10:50] MySQL Verification Team
Patch pushed.
[2 Nov 2007 18:11] Dariel Barroso Tallart
I am a proble to call a procedure, 
and give me the same mistake.
my procedure is this:

create procedure insert_embarque (in cod int(10), in pai varchar(50), trans int(10), cant int(10), fech date)

begin 

declare c int(10);
declare p varchar(50);
declare t int(10);

set c = (select code from MERCANCIA where code=cod);

set p = (select nombre from PAIS where nombre='pai');

set t = (select code from TRANSPORTE where code=trans);

INSERT INTO EMBARQUE (mercancia, pais, tipo_transporte, fecha, cantidad, precio) values (c, p, t, fech, cant, null);

end;

and my call si this:
call insert_embarque(1, 'pais 1', 2, '2007/10/27', 4);

and this give me this mistake:

#1048 - Column 'pais' cannot be null 

how can I resolve that problem???