MySQL Bugs: #13127: LEFT JOIN against a VIEW returns NULL instead of correct value

Bug #13127	LEFT JOIN against a VIEW returns NULL instead of correct value
Submitted:	13 Sep 2005 5:21	Modified:	23 Sep 2005 19:16
Reporter:	Timothy Smith
Status:	Closed
Category:	Server: Views	Severity:	S2 (Serious)
Version:	5.0.13 current bk	OS:	Any (any)
Assigned to:	Timour Katchaounov	Target Version:

Description:

-- This query returns integers
select a.id from a;

-- This query returns NULL
select a.id from a left join v g using (id);

This bug is NOT present in MySQL 5.0.11 and 5.0.12.  It IS present in BK code as of
today, and from a few days ago.

15:20 ~/m/csc/6425$ mysql test < bug.sql
id
100
101
102
103
104
105
id
100
101
102
103
104
105
version()
5.0.11-beta-debug-log
15:20 ~/m/csc/6425$ mm ~/m/50/m
Setting MySQL environment
mysqld is alive
15:20 ~/m/csc/6425$ mysql test < bug.sql
id
100
101
102
103
104
105
id
NULL
NULL
NULL
NULL
NULL
NULL
version()
5.0.13-beta-debug-log
15:20 ~/m/csc/6425$  

How to repeat:

15:20 ~/m/csc/6425$  cat bug.sql
drop table if exists a;
drop table if exists b;
drop view if exists b;

create table a (
  id char(16) not null default '',
  primary key  (id)
) engine=innodb default charset=utf8;

lock tables a write;
insert into a values
('100'),
('101'),
('102'),
('103'),
('104'),
('105');
unlock tables;

drop table if exists b;
create table b (
  id char(16) default null
) engine=innodb default charset=utf8;

insert into b values (1);

drop table if exists v;
drop view if exists v;
create view v as
select
    b.id
from
    b
    ;

select a.id from a;
select a.id from a left join v g using (id);
select version();

Suggested fix:

n/a

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

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

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

Fixed in 5.0.14.

Noted in 5.0.14 changelog.