| Bug #13452 | UNION + VIEW == ERROR 1048 (23000), Column 'id1' cannot be null | ||
|---|---|---|---|
| Submitted: | 24 Sep 2005 0:30 | Modified: | 29 Sep 2005 21:57 |
| Reporter: | Timothy Smith | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | Any (all) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[29 Sep 2005 21:53]
Sergey Petrunya
The root of the problem is not with UNION or with VIEWS. The problem is caused by wrong behavior with nested outer joins: mysql> select t1.id1 from t1 inner join (t3 left join v on t3.id3 = v.id3); +-----+ | id1 | +-----+ |NULL | +-----+ 1 row in set (0.17 sec) -- v2 is a view that is the same as v1 except that ALGORITHM=TEMPTABLE is used: mysql> select t1.id1 from t1 inner join (t3 left join v2 on t3.id3 = v2.id3); +-----+ | id1 | +-----+ | 1 | | 2 | +-----+ 2 rows in set (0.05 sec) -- Substituting view v1 with its definition: mysql> select t1.id1 from t1 inner join (t3 left join (t4 join t2 on t4.id2 = t2.id2) on t3.id3 = t4.id3); +-----+ | id1 | +-----+ |NULL | +-----+ 1 row in set (1.27 sec) -- We get wrong result here.
Description: I tested this on FreeBSD & Linux, 5.0.13-rc, recent 5.0 bk code, and other earlier 5.0 versions. When I run it, the SELECT gives this error: ERROR 1048 (23000): Column 'id1' cannot be null How to repeat: drop table if exists t1; create table t1 ( id1 int(11) not null ) engine=innodb default charset=utf8; insert into t1 values (1),(2); drop table if exists t2; create table t2 ( id2 int(11) not null ) engine=innodb default charset=utf8; insert into t2 values (1),(2),(3),(4); drop table if exists t3; create table t3 ( id3 char(16) not null default '' ) engine=innodb default charset=utf8; insert into t3 values ('100'); drop table if exists `t4`; create table `t4` ( `id2` int(11) not null default '0', `id3` char(16) default null ) engine=innodb default charset=utf8; drop table if exists t5; create table t5 ( id1 int(11) not null, -- NOTE: If this key is removed, then the problem goes away key (id1) ) engine=innodb default charset=utf8; insert into t5 values (1),(2),(1); drop table if exists v; drop view if exists v; create view v as select t4.id3 from t4 join t2 on t4.id2 = t2.id2; select t1.id1 from v inner join (t1 inner join t5 on t1.id1 = t5.id1) union all select t1.id1 from (t1 inner join t5 on t1.id1 = t5.id1) inner join (t3 left join v on t3.id3 = v.id3) ;