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