Description:
LEFT JOIN ON multiple conditions is weird
How to repeat:
# LEFT JOIN ON multiple conditions
## DDL and DATA:
CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL,
`v` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `t2` (
`hh` int(11) DEFAULT NULL,
`x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t1` values (1, 1);
INSERT INTO `t1` values (2, 2);
INSERT INTO `t2` values (1, 3);
## Query SQL
select * from t1 left join t2 on t1.i = t2.hh and t1.i = 2;
I expects result is:
+------+------+------+------+
| i | v | hh | x |
+------+------+------+------+
| 2 | 2 | NULL | NULL |
+------+------+------+------+
But actually is:
+------+------+------+------+
| i | v | hh | x |
+------+------+------+------+
| 1 | 1 | NULL | NULL |
| 2 | 2 | NULL | NULL |
+------+------+------+------+
which behavior so weird, because the condition t1.i = 2 neither is igonred(because that result is different from without t1.i = 2) or exclude t1.i = 1 record.
I tested other DB, and don't find this question.
