多租户 right join bug

不好意思, 没注意看到租户表的配置 :joy: 
但是上面那个还是有问题, 如下是代码
![测试代码](https://images.gitee.com/uploads/images/2021/1112/095903_bff82cd8_611273.png "屏幕截图.png")
原始sql
```sql
SELECT * FROM entity e right join entity1 e1 on e1.id = e.id
```
多租户处理之后的sql
```sql
SELECT * FROM entity e RIGHT JOIN entity1 e1 ON e1.id = e.id AND e1.tenant_id = 1 WHERE e.tenant_id = 1 AND e1.tenant_id = 1
```
如果left join的时候, 如果有entity表不满足on条件会出现,会出现有entity1表字段,但是entity表字段为空的情况
在where中加入`e.tenant_id=1`会导致这些数据被排除,所以在where中不能含有`e.tenant_id=1`这个条件
例如entity表数据如下
| id | tenant_id |
|----|-----------|
| 1  | 1         |
| 2  | 1         |
entity1表数据如下
| id | tenant_id |
|----|-----------|
| 1  | 1         |
| 2  | 1         |
| 3  | 1         |
多租户处理之后的sql
```sql
SELECT * FROM entity e RIGHT JOIN entity1 e1 ON e1.id = e.id AND e1.tenant_id = 1 WHERE e.tenant_id = 1 AND e1.tenant_id = 1;
```
查询结果![输入图片说明](https://images.gitee.com/uploads/images/2021/1112/103113_137077d0_611273.png "屏幕截图.png")
将过滤放到表中的sql
```sql
SELECT * FROM (select * from entity where tenant_id=1) e RIGHT JOIN (select * from entity1 where tenant_id=1) e1 ON e1.id = e.id;
```
的查询结果![输入图片说明](https://images.gitee.com/uploads/images/2021/1112/103229_912e63d0_611273.png "屏幕截图.png")
最后是期望sql应该是将entity表的筛选条件放到on中, entity1表的筛选条件放到where中
如下
```sql
SELECT * FROM entity e RIGHT JOIN entity1 e1 ON e1.id = e.id AND e.tenant_id = 1 WHERE e1.tenant_id = 1;
```
查询结果![输入图片说明](https://images.gitee.com/uploads/images/2021/1112/103428_01b31183_611273.png "屏幕截图.png")

测试案例,需要在postgresql中测试, mysql不支持full outer join

```sql
CREATE TABLE People
(
    ID        bigint NOT NULL,
    TENANT_ID int4         DEFAULT NULL,
    NAME      varchar(255) DEFAULT NULL,
    PRIMARY KEY (ID)
);
CREATE TABLE Cars
(
    ID        bigint NOT NULL,
    TENANT_ID int4         DEFAULT NULL,
    NAME      varchar(255) DEFAULT NULL,
    PRICE     int          DEFAULT NULL,
    people_id bigint       DEFAULT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (people_id) REFERENCES People (ID)
);

insert into people (id, tenant_id, name)
values (1, 1, 'Audi');
insert into people (id, tenant_id, name)
values (2, 1, 'Mercedes');
insert into people (id, tenant_id, name)
values (3, 2, 'Skoda');

INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (1, 1, 'Audi', 52642, 1);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (2, 2, 'Mercedes', 57127, 1);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (3, 1, 'Skoda', 9000, 1);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (4, 1, 'Volvo', 29000, null);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (5, 2, 'Bentley', 350000, 1);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (6, 2, 'Citroen', 21000, 1);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (7, 1, 'Hummer', 41400, 1);
INSERT INTO Cars (ID, TENANT_ID, NAME, PRICE, people_id)
VALUES (8, 1, 'Volkswagen', 21600, 1);
-- 正确但是效率可能不太好的sql
select t1.*, t2.*
from (select * from cars where TENANT_ID = 1) t1
         full outer join (select * from people where People.TENANT_ID = 1) t2 on (t1.people_id = t2.ID);
-- 原始sql
select c.*, p.*
from cars c
         full outer join people p on c.people_id = p.id;
-- tenant插件处理的sql
SELECT c.*, p.*
FROM cars c
         FULL OUTER JOIN people p ON c.people_id = p.id
WHERE c.tenant_id = 1;

```
感觉full outer join需要写成子查询的方式

GVP baomidou/mybatis-plus

内容风险标识

评论 (28)

GVPbaomidou/mybatis-plus

内容风险标识