NLJ 条件下压计划执行结果错误

问题现象

当 OUTER JOIN、SEMI JOIN、ANTI JOIN 走了 NLJ 计划，并且连接条件存在驱动表的过滤条件时，通过 explain 计划可以看到 nl_params 出现在 NLJ 的 startup filter 里面，导致查询执行结果不对，大部分场景是执行结果为空。

以一个例子解释什么样的计划可以判断为该问题。

SELECT 1 FROM (t1 join (select DISTINCT stockholder_id  from t3) n on 1=1) left join
t5 on t5.perparam_id=6 and t5.operator_no=1001
where t1.operator_no = 1001  and t1.stockholder_id = n.stockholder_id
and (find_in_set(t1.stockholder_id,t5.param_value)=0 or ifnull((select null),'')='');
===========================================================
|ID|OPERATOR                          |NAME|EST. ROWS|COST|
-----------------------------------------------------------
|0 |SUBPLAN FILTER                    |    |2        |172 |
|1 | NESTED-LOOP JOIN                 |    |2        |172 |
|2 |  NESTED-LOOP OUTER JOIN CARTESIAN|    |2        |105 |
...
|6 |  TABLE SCAN                      |n   |1        |46  |
...
===========================================================

Outputs & filters: 
-------------------------------------
  0 - output([1]), filter(nil), startup_filter([1], [? OR ?]), 
      exec_params_(nil), onetime_exprs_([ifnull(cast(subquery(1), VARCHAR(0)), '') = '']), init_plan_idxs_(nil)
  1 - output([1]), filter(nil), 
      conds(nil), nl_params_([t1.stockholder_id], [find_in_set(t1.stockholder_id, t5.param_value) = 0])
  2 - output([t1.stockholder_id], [t5.param_value]), filter(nil), 
      conds(nil), nl_params_(nil)
 ...
  6 - output([1]), filter(nil), 
      access([n.stockholder_id])
...

需要关注两种表达式：

• 0 号算子的 startup_filter([1], [? OR ?])。

• 1 号算子的 nl_params_([t1.stockholder_id],[find_in_set(t1.stockholder_id, t5.param_value) = 0])

根据原始 SQL，可知 ? OR ? 其实是 (find_in_set(t1.stockholder_id,t5.param_value)=0 or ifnull((select null),'')='')，1 号算子把 find_in_set(t1.stockholder_id,t5.param_value)=0 替换成了 ？。对于 [? OR ?] 条件，原本应该在 6 号算子上面，但是优化器为了可以提前结束计划，把 [? OR ?] 上拉到 0 号算子上面了。这里存在一个问题，不应该上拉过 1 号算子，因为这个表达式的执行依赖 1 号算子的执行。

整体上，满足以下条件会有一定的风险：

1. 执行计划中同时存在外连接和内连接。

2. 连接次序是先做外连接，之后再做内连接，且外连接的结果是内连接的左支。

3. 内连接采用的算法是 NEST LOOP JOIN。

4. 内连接的NEST LOOP JOIN上层存在 startup filter。

总结而言，需要是三表连接的查询，一次是内连接，一次是外连接，WHERE 中有一个谓词同时引用了外连接的左表和右表，且这个外连接没有被优化器改写为内连接。这种场景有可能会踩以上 BUG。

关键诊断信息

触发条件

当 OUTER JOIN、SEMI JOIN、ANTI JOIN 走了 NLJ 计划，并且连接条件存在驱动表的过滤条件时，通过 explain 计划可以看到 nl_params 出现在 NLJ 的 startup filter 里面。

问题原因

原因在于优化尝试优化常量过滤条件时，错误的把 NLJ 的连接条件提前执行了，导致 NLJ 的执行结果不对。

问题风险及影响

触发该问题之后，查询结果不对。

影响租户

影响 OceanBase 数据库中的 MySQL 租户，对于 SYS 租户和 Oracle 租户无影响。

影响版本

OceanBase 数据库 V3.1.2 GA（oceanbase-3.1.2-20210618150922）及之后版本、V3.2.3 GA（oceanbase-3.2.3.0-20220418212020）及之后版本、V3.2.4 GA（oceanbase-3.2.4.0-100000072022102819）及之后版本。

解决方法及规避方式

解决方法

• 升级到问题已修复版本。目前已修复的版本包括 OceanBase 数据库 V3.2.3 BP8（oceanbase-3.2.3.3-108000062023041511）、V3.2.4 BP2（oceanbase-3.2.4.2-102000042023022717）。

• 触发该问题之后，可以通过使用 hint 走 hash join 绕过。

  示例如下。

  SELECT /*+no_use_nl(n)*/ 
  1 FROM (t1 join (select DISTINCT stockholder_id  from t3) n on 1=1) left join
  t5 on t5.perparam_id=6 and t5.operator_no=1001
  where t1.operator_no = 1001  and t1.stockholder_id = n.stockholder_id
  and (find_in_set(t1.stockholder_id,t5.param_value)=0 or ifnull((select null),'')='');
  

  如果该连接没有等值连接条件，指定右表走主表扫描，避免生成条件下压的 NLJ 计划。

  例如之前的示例。

  SELECT 1 FROM (t1 join (select /*+full(t3)*/ DISTINCT stockholder_id  from t3) n on 1=1) left join
  t5 on t5.perparam_id=6 and t5.operator_no=1001
  where t1.operator_no = 1001  and t1.stockholder_id = n.stockholder_id
  and (find_in_set(t1.stockholder_id,t5.param_value)=0 or ifnull((select null),'')='');
  

规避方式

查询中避免出现以下几种情况：

• OUTER JOIN 的条件避免出现驱动表的过滤条件，例如：T1 LEFT JOIN T2 ON T1.C1 = 1 AND xxxx。

• 子查询里面避免出现父查询的过滤条件，例如：SELECT * FROM T1 WHERE EXISTS(SELECT 1 FROM T2 WHERE T1.C1 = 1 AND xxx)。