mysql分页优化时，left join移除方式的问题

[q876625596]

当前使用版本(必填,否则不予处理)

3.5.5

该问题是如何引起的？(确定最新版也有问题再提!!!)

mysql分页优化 join时出现的问题。

重现步骤(如果有就写完整)

原sql为：

SELECT
	ps.*,
	pcp.company_name,
	pl.id,
	pl.parking_lot_name 
FROM
	parking_space AS ps
	
	INNER JOIN parking_lot_space_scope AS plss_condition ON plss_condition.parking_space_id = ps.id 
	AND plss_condition.parking_lot_id = ? 
	
	LEFT JOIN parking_company AS pcp ON pcp.id = ps.parking_company_id 
	
	LEFT JOIN parking_lot_space_scope AS plss ON plss.parking_space_id = ps.id 
	
	LEFT JOIN parking_lot AS pl ON pl.id = plss.parking_lot_id 
WHERE
	ps.parking_company_id = ? 
ORDER BY
	ps.parking_space_number;

分页优化后的sql为：

SELECT
	count(*) 
FROM
	parking_space AS ps
	
	INNER JOIN parking_lot_space_scope AS plss_condition ON plss_condition.parking_space_id = ps.id 
	AND plss_condition.parking_lot_id = ? 
	
	LEFT JOIN parking_company AS pcp ON pcp.id = ps.parking_company_id 
	
	LEFT JOIN parking_lot_space_scope AS plss ON plss.parking_space_id = ps.id 
	
	LEFT JOIN parking_lot AS pl ON pl.id = plss.parking_lot_id 
	
WHERE
	ps.parking_company_id = ?

报错信息

在上述分页后的sql中，还是即使left join 没有出现在where条件中，但是在分页时依旧被保留了，我看你代码中是这样写的：

            if (optimizeJoin && page.optimizeJoinOfCountSql()) {
                List<Join> joins = plainSelect.getJoins();
                if (CollectionUtils.isNotEmpty(joins)) {
                    boolean canRemoveJoin = true;
                    String whereS = Optional.ofNullable(plainSelect.getWhere()).map(Expression::toString).orElse(StringPool.EMPTY);
                    // 不区分大小写
                    whereS = whereS.toLowerCase();
                    for (Join join : joins) {
                        if (!join.isLeft()) {
                            canRemoveJoin = false;
                            break;
                        }
                        FromItem rightItem = join.getRightItem();
                        String str = "";
                        if (rightItem instanceof Table) {
                            Table table = (Table) rightItem;
                            str = Optional.ofNullable(table.getAlias()).map(Alias::getName).orElse(table.getName()) + StringPool.DOT;
                        } else if (rightItem instanceof SubSelect) {
                            SubSelect subSelect = (SubSelect) rightItem;
                            /* 如果 left join 是子查询，并且子查询里包含 ?(代表有入参) 或者 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
                            if (subSelect.toString().contains(StringPool.QUESTION_MARK)) {
                                canRemoveJoin = false;
                                break;
                            }
                            str = subSelect.getAlias().getName() + StringPool.DOT;
                        }
                        // 不区分大小写
                        str = str.toLowerCase();

                        if (whereS.contains(str)) {
                            /* 如果 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
                            canRemoveJoin = false;
                            break;
                        }

                        for (Expression expression : join.getOnExpressions()) {
                            if (expression.toString().contains(StringPool.QUESTION_MARK)) {
                                /* 如果 join 里包含 ?(代表有入参) 就不移除 join */
                                canRemoveJoin = false;
                                break;
                            }
                        }
                    }

                    if (canRemoveJoin) {
                        plainSelect.setJoins(null);
                    }
                }
            }

你这段代码是将全部join遍历，如果所有join都是left join，并且都不存在于where条件中时，就将所有的join都移除掉，但是按我的理解，应该是在遍历join的时候，只要有一个不存在于where条件中的left join，就应该移除掉才对，请问作者这样做的目的是什么呢？