Bug #13127 | LEFT JOIN against a VIEW returns NULL instead of correct value | ||
---|---|---|---|
Submitted: | 13 Sep 2005 3:21 | Modified: | 23 Sep 2005 17:16 |
Reporter: | Timothy Smith | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Views | Severity: | S2 (Serious) |
Version: | 5.0.13 current bk | OS: | Any (any) |
Assigned to: | Timour Katchaounov | CPU Architecture: | Any |
[13 Sep 2005 3:21]
Timothy Smith
[15 Sep 2005 1:29]
Timothy Smith
Timour, Here's another test case. It *may* be a different bug, I'm not sure. Or it may be a result of this bug. Please let me know if I should open a new bug report for it. When I run the test case against a 5.0-bk from yesterday, I get: $ mysql test < bug3.sql ERROR 1048 (23000) at line 47: Column 'id1' cannot be null Note that if the index on t5.id1 is removed, then this bug doesn't show up. 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) ;
[15 Sep 2005 13:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/29903
[20 Sep 2005 11:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/30097
[20 Sep 2005 12:26]
Timour Katchaounov
Fixed in 5.0.14.
[23 Sep 2005 17:16]
Paul DuBois
Noted in 5.0.14 changelog.