MySQL Bugs: #2508: 1048 - column 'name' cannot be null (error with union and left join)

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

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
--------------------------------------------

A patch that fixes a problem has been sent and proposed as a solution.

Patch pushed.

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???