left join cause result duplicate

[allenliu88]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

-- test data
drop table test_a;
create table test_a(user_config_id bigint(20) primary key, user_id bigint(20), tenant_id bigint(20), default_company_id bigint(20));
insert into test_a(user_config_id, user_id, tenant_id, default_company_id) values (120003, 1, 0, 0);
insert into test_a(user_config_id, user_id, tenant_id, default_company_id) values (120008, 1, 1, null);

select * from test_a;

drop table test_b;
create table test_b (company_id bigint(20) primary key, company_num varchar(30), tenant_id bigint(20));
insert into test_b(company_id, company_num, tenant_id) values (0, 'HZERO', 0);
select * from test_b;


-- test SQL
select huc.*, hc.company_id from test_a huc left join test_b hc on ((hc.company_id = huc.default_company_id or (hc.company_id is null and huc.default_company_id is null)) and hc.tenant_id = huc.tenant_id) where user_id = 1; -- No.1 result correct count 2

select huc.*, hc.company_id from test_a huc left join test_b hc on (hc.company_id = huc.default_company_id and hc.tenant_id = huc.tenant_id) where user_id = 1; -- No.2 result not correct 3

2. What did you expect to see?
   expect No.2 query result size 2 not 3, because data of table test_a has only 2 row, and left join should not be duplicate.

3. What did you see instead?
   No.2 query result size 3, has duplicate
   

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: None
   Git Commit Hash: 508f5dd
   Git Branch: master
   UTC Build Time: 2018-10-27 09:59:28
   GoVersion: go version go1.11 linux/amd64
   Race Enabled: false
   TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
   Check Table Before Drop: false

[XuHuaiyu]

@AllenLiuGit Thanks for your feedback.
We'll investigate this.

[lastzero]

Great that you fixed this so fast! Still it surprises me that a bug like that was hiding in a stable database engine used by the financial services industry. Did I miss something here?

[shenli]

@lastzero Thanks for your comment!

We consider wrong results bugs to be high priority and action them immediately. In this case, the bug was caused by an incorrect optimization being applied (not all the user scenarios will meet this issue). The patch addresses the optimization so that it is handled correctly.

For the important users and critical scenario, we always work together with our customers to evaluate and test their application on TiDB. For the banks, we even help them to review their SQLs. They all use a more stable (but with less functionality) version (for now, it is 2.0.x). The strict test and cautious upgrading strategy are the two key factors to keep the banks' application safe. For the community users, some of them will use the latest version like 2.1.x or the latest master.

Building a distributed RDMS is a hard work. We benefit from the open source community. They help us to improve at an incredible speed. We encourage the community to try TiDB and report Bug/FeatureRequest through Github issue in a public way. And we also publish most of our development work through Github project/pr/issue. The community has the right to known what's going on in this database.