Bug #92855 LEFT JOIN ON multiple conditions is weird

Submitted: 19 Oct 2018 7:13 Modified: 19 Oct 2018 11:37
Reporter: Hongyu Qiao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.7.21 OS:Debian
Assigned to: CPU Architecture:x86
Tags: left join

[19 Oct 2018 7:13] Hongyu Qiao
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.
[19 Oct 2018 11:37] Hongyu Qiao
I made mistakes

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.