outer join reorder will produce unexpected join sequence.

[AilinKid]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

schema

CREATE TABLE Forp_User (
  id INT PRIMARY KEY,
  userName VARCHAR(255)
);

CREATE TABLE Forp_UserRole (
  FK_UserID INT,
  fk_roleId INT,
  FOREIGN KEY (FK_UserID) REFERENCES Forp_User(id),
  FOREIGN KEY (fk_roleId) REFERENCES forp_role(id)
);

CREATE TABLE forp_role (
  id INT PRIMARY KEY,
  NAME VARCHAR(255),
  CODE VARCHAR(255)
);

data

INSERT INTO `forp_role` VALUES
(1, '小天才俱乐部', 'Administrator role'), 
(2, 'User', 'User role');

INSERT INTO `Forp_User` VALUES
(41398, 'Alice'),
(41399, 'Bob'), 
(41400, 'Carol'),
(41401, 'Dave');

INSERT INTO `Forp_UserRole` VALUES
(198316, 41398, 1),
(198317, 41399, 2),
(198318, 41400, 2),
(198319, 41401, 2);

query

SELECT
	u.id,
	u.userName,
	a.fk_roleId ,
	b.`NAME`
FROM
	Forp_User u
	LEFT JOIN Forp_UserRole a ON a.FK_UserID = u.id
	LEFT JOIN forp_role b ON b.id = a.fk_roleId 
WHERE
	u.state = 1 
	AND   
b.`CODE` = 123456789

2. What did you expect to see? (Required)

empty result

3. What did you see instead (Required)

two rows

4. What is your TiDB version? (Required)

v7.1.3 & even master

[AilinKid]

tidb> select * from (select u.id, u.username, a.fk_roleid from forp_user u LEFT JOIN forp_userrole a ON a.FK_UserID = u.id where u.state=1) x left join forp_role b ON b.id = x.fk_roleId where b.`CODE` = 123456789;
+-------+----------+-----------+------+-------------+-----------+------+-------------+-----------+----------+------------+----------------+----------------+------------------+--------------------+--------+--------------+------+-----------+
| id    | username | fk_roleid | ID   | FK_DOMAINID | FK_USERID | NAME | DESCRIPTION | ROLELEVEL | ISINITED | CREATEDATE | CREATEUSERNAME | LASTMODIFYDATE | LASTMODIFYUSERID | LASTMODIFYUSERNAME | REMARK | FK_SOURCE_ID | CODE | IS_REMIND |
+-------+----------+-----------+------+-------------+-----------+------+-------------+-----------+----------+------------+----------------+----------------+------------------+--------------------+--------+--------------+------+-----------+
| 41398 | Alice    |      NULL | NULL |        NULL |      NULL | NULL | NULL        |      NULL | NULL     | NULL       | NULL           | NULL           |             NULL | NULL               | NULL   |         NULL | NULL | NULL      |
| 41399 | Bob      |      NULL | NULL |        NULL |      NULL | NULL | NULL        |      NULL | NULL     | NULL       | NULL           | NULL           |             NULL | NULL               | NULL   |         NULL | NULL | NULL      |
+-------+----------+-----------+------+-------------+-----------+------+-------------+-----------+----------+------------+----------------+----------------+------------------+--------------------+--------+--------------+------+-----------+
2 rows in set, 2 warnings (0.00 sec)

I change control the left join sequence manually, it should outputs the empty result as well.

[AilinKid]

• two cascaded left join with one has the null reject attributes. So the join will become the following shape before entering logic opt.
• When we conduct the outer join reorder with this case, it will link a and b together, then output the sequence as: <u left join (a inner join b)>, obviously, it will generate more rows at least the same level as u's row count.
• Mysql could generate <(a inner join b) left join u>, that's the correct one. It's not that instinctive. Maybe some hack work inside is done.
• From our knowledge base on the connected graph, (left join is a single directed edge, an inner join is a double directed edge) then we get a pic like below. So we could generate = <(u left join b) inner join a> at most.

[AilinKid]