[jira] [Created] (KYLIN-5360) Kylin4 " select xx from table1 join table2 on table2.column = anyone constant value" query bug

["Liu Zhao (Jira)" <ji...@apache.org>]

Liu Zhao created KYLIN-5360:
-------------------------------

             Summary: Kylin4 " select xx from table1 join table2 on table2.column = anyone constant value" query bug
                 Key: KYLIN-5360
                 URL: https://issues.apache.org/jira/browse/KYLIN-5360
             Project: Kylin
          Issue Type: Bug
          Components: Query Engine
    Affects Versions: v4.0.2, v4.0.1
            Reporter: Liu Zhao
         Attachments: image-2022-12-12-14-10-37-996.png, image-2022-12-12-14-10-49-173.png

1) b.age = 22 放在 join 的 on 表达式部分，查询抛出异常：
with tmp_t as (
  select
    name,age
  from
    LZ_TEST
)
select
  a.name,
  b.age
from
  LZ_TEST a
  left join tmp_t b on a.name = b.name and b.age = 22

2）仅将 b.age = 22 移到 where 条件部分，查询成功
with tmp_t as (
  select
    name,age
  from
    LZ_TEST
)
select
  a.name,
  b.age
from
  LZ_TEST a
  left join tmp_t b on a.name = b.name
where b.age = 22

查看源码，发现在 calcite 的 org.apache.calcite.plan.RelOptUtil#splitJoinCondition(org.apache.calcite.rel.RelNode, org.apache.calcite.rel.RelNode, org.apache.calcite.rex.RexNode, java.util.List<java.lang.Integer>, java.util.List<java.lang.Integer>, java.util.List<java.lang.Boolean>) 对 equal 的判断有以下部分处理，要求表达式左右child只能是 RexInputRef，如果是常量值（RexLiteral）就会判断错误


{code:java}
      // "=" and "IS NOT DISTINCT FROM" are the same except for how they
      // treat nulls.
      if (kind == SqlKind.EQUALS
          || (filterNulls != null && kind == SqlKind.IS_NOT_DISTINCT_FROM)) {
        final List<RexNode> operands = call.getOperands();
        if ((operands.get(0) instanceof RexInputRef)
            && (operands.get(1) instanceof RexInputRef)) {
          RexInputRef op0 = (RexInputRef) operands.get(0);
          RexInputRef op1 = (RexInputRef) operands.get(1);

          RexInputRef leftField;
          RexInputRef rightField;
          if ((op0.getIndex() < leftFieldCount)
              && (op1.getIndex() >= leftFieldCount)) {
            // Arguments were of form 'op0 = op1'
            leftField = op0;
            rightField = op1;
          } else if (
              (op1.getIndex() < leftFieldCount)
                  && (op0.getIndex() >= leftFieldCount)) {
            // Arguments were of form 'op1 = op0'
            leftField = op1;
            rightField = op0;
          } else {
            nonEquiList.add(condition);
            return;
          }

          leftKeys.add(leftField.getIndex());
          rightKeys.add(rightField.getIndex() - leftFieldCount);
          if (filterNulls != null) {
            filterNulls.add(kind == SqlKind.EQUALS);
          }
          return;
        }
        // Arguments were not field references, one from each side, so
        // we fail. Fall through.
      }
{code}

 !image-2022-12-12-14-10-37-996.png! 
 !image-2022-12-12-14-10-49-173.png! 

修复：
1）方式一：更改calcite splitJoinCondition 逻辑
2）方式二：更改 org.apache.kylin.query.optrule.OLAPJoinRule#convert 逻辑




--
This message was sent by Atlassian Jira
(v8.20.10#820010)