Bug #978 Error 1048 in UNION
Submitted: 1 Aug 2003 8:54 Modified: 23 Aug 2003 12:09
Reporter: Branislav Siarsky Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.0-alpha-standard OS:Linux (Linux 686, Mandrake 9.0)
Assigned to: MySQL Verification Team CPU Architecture:Any

[1 Aug 2003 8:54] Branislav Siarsky
Description:
Null value not allowed in column of a UNION, if in others subqueries
there is a column with not null constrain.

How to repeat:
create table tt_users (
   id int not null auto_increment, primary key (id)
  ,user_name text
);

create table tt_groups (
   id int not null auto_increment, primary key (id)
  ,group_name text
);

create table tt_group_user (
   id int not null auto_increment, primary key (id)
  ,user_id int
  ,index user_idx (user_id)
  ,foreign key (user_id) references users(id)
  ,group_id int
  ,index group_idx (group_id)
  ,foreign key (group_id) references groups(id)
);

insert into tt_users (user_name) values ('Tester');
insert into tt_groups (group_name) values ('Group A');
insert into tt_groups (group_name) values ('Group B');
insert into tt_group_user (user_id, group_id) values (1,1);

select 1 'is_in_group', a.user_name, c.group_name, b.id
from tt_users a, tt_group_user b, tt_groups c
where a.id = b.user_id
and b.group_id = c.id
UNION
select 0 'is_in_group', a.user_name, c.group_name, null
from tt_users a, tt_groups c
where concat(a.id, '-', c.id) not in  #simulate MINUS: not supported by Mysql
(
select concat(a.id, '-', c.id)
from tt_users a, tt_group_user b, tt_groups c
where a.id = b.user_id
and b.group_id = c.id
)

Result: ERROR 1048: Column 'id' cannot be null

and should be:

+-------------+-----------+------------+------+
| is_in_group | user_name | group_name | id   |
+-------------+-----------+------------+------+
|           1 | Tester    | Group A    |   1  |
|           0 | Tester    | Group B    | null |
+-------------+-----------+------------+------+

Suggested fix:
Do not check constrains on unions.
[2 Aug 2003 8:53] MySQL Verification Team
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html