Why can I select something from the left join on a NULL column? (With a contrived example to reproduce it locally, probably a bug!)
VERSION I am using server version: 5.1.36-community-log MySQL Community Server (GPL)
I finally came up with a simple example to reproduce easily!
Setting:
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 do:
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 it wrong:
+------+------+---------+
| id | id | content |
+------+------+---------+
| NULL | 1 | test |
+------+------+---------+
But if we create t2 this way, it doesn't:
create table t2(id integer unsigned);
So it has something to do with the primary key!
NEW FOUND
running this will not result in an error:
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 the direction of the connection!
source to share
I just ran create, insert and select for you in MySQL 5.0.58, 5.0.82 and 5.1.35, and I got the following output, which I think is correct:
+------+------+---------+
| id | id | content |
+------+------+---------+
| NULL | NULL | NULL |
+------+------+---------+
This is 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;
source to share
Very interesting. This actually looks like a bug in MySQL. The result of the query depends on whether there are primary keys or not. They should definitely not influence the outcome. For reference, PostgreSQL will return the correct result with primary keys, so I think this is unlikely to be the expected behavior.
source to share
Invalid installation code
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
Also, be sure to check your tables after each test. You probably have the wrong data.
source to share