Why can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)

Question

VERSION I'm using Server version: 5.1.36-community-log MySQL Community Server (GPL)

I've finally contrived a easy example to reproduce it easily!

setup:

create table t1(id integer unsigned,link integer unsigned);
create table t2(id integer unsigned auto_increment,primary key(id));
create table t3(id integer unsigned,content varchar(30));
insert into t1 value(1,null);
insert into t2 value(1);
insert into t3 value(1,'test');

then run:

select t2.*,t3.* 
from t1
left join t2 on t1.link=t2.id
left join t3 on t3.id=t2.id
where t1.id=1;

will get this wrongly:

+------+------+---------+
| id   | id   | content |
+------+------+---------+
| NULL |    1 | test    |
+------+------+---------+

But if we create t2 this way,it won't happen:

create table t2(id integer unsigned);

So,it has something to do with primary key!

NEW FOUND

run this will not trigger the bug:

select t2.*,t3.*
from t1
left join t2 on t1.link=t2.id
left join t3 on t2.id=t3.id
where t1.id=1;

So it also has something to do with join direction!

Answer 1

Is it that one of the 'fid' rows in 'fuinfo' is set to NULL? MySQL could be joining that up with a NULL value in the left table.

Answer 2

Very interesting. This indeed does look like a bug in MySQL. The result of the query depends on whether there are primary keys or not. They should definitely not affect the result. For the reference, PostgreSQL will return the correct result with primary keys, so I think it's unlikely that it would be an expected behavior.

Answer 3

Your setup code is wrong

create table t1(id integer unsigned,link integer unsigned);  
create table t2(id integer unsigned auto_increment,primary key(id));  
create table t2(id integer unsigned,content varchar(30));

             ^^  This is wrong. Should be t3

Plus be sure you drop your tables after each test. You probably have some wrong data in them.

Answer 4

I just ran you create, insert and select on MySQL 5.0.58, 5.0.82 & 5.1.35 and I received the following result, which I think is correct:

+------+------+---------+
| id   | id   | content |
+------+------+---------+
| NULL | NULL | NULL    |
+------+------+---------+

Here is exactly what I used:

CREATE TABLE `t1` (
  `id` int(10) unsigned default NULL,
  `link` int(10) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (1, NULL); 

CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

INSERT INTO `t2` VALUES (1);

CREATE TABLE `t3` (
  `id` int(10) unsigned default NULL,
  `content` varchar(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t3` VALUES (1, 'test');

SELECT t2.id, t3.id, t3.content
FROM t1
LEFT JOIN t2 ON t1.link = t2.id
LEFT JOIN t3 ON t2.id = t3.id
WHERE t1.id = 1;