Closed AilinKid closed 6 days ago
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.
/found customer
implicitly fixed by https://github.com/pingcap/tidb/pull/52941, this is an enhancement refactoring, which will not be cherry-picked to earlier versions.
this one has been implictly fixed by https://github.com/pingcap/tidb/pull/52941, this pull request covers refactoring of the current outer join elimination framework, extracting them out predicate-pushdown as a logic rule, and enhancing more cases of it which is not capable in the old one.
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
schema
data
query
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