[CALCITE-3720] Failed to validate the field in MATCH_RECOGNIZE phase

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.21.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Axis
• Created: 09/Jan/20 10:09
• Updated: 10/Jan/20 02:23

Description

hello, everyone

    the following case is failed when we run it:

@Test public void testMatchRecognize10() {
  final String sql = "select *\n"
      + "  from (select * from emp, unnest(multiset(select*from dept)) where empno > 10) match_recognize\n"
      + "  (\n"
      + "    partition by job, sal\n"
      + "    order by job asc, sal desc, empno\n"
      + "    pattern (strt down+ up+)\n"
      + "    define\n"
      + "      down as down.mgr < PREV(down.mgr),\n"
      + "      up as up.mgr > prev(up.mgr)) as mr";
  sql(sql).ok();
}

the error message is :

Cycle detected during type-checkingCycle detected during type-checkingjava.lang.AssertionError: Cycle detected during type-checking at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:93) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1057) at org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:115) at org.apache.calcite.sql.validate.MatchRecognizeNamespace.getRowType(MatchRecognizeNamespace.java:26) at org.apache.calcite.sql.validate.DelegatingScope.resolveInNamespace(DelegatingScope.java:101) at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:191) at org.apache.calcite.sql.validate.DelegatingScope.resolve(DelegatingScope.java:89)

 

After checking the calcite code, we found that

when calcite resolve the field `empno` in condition `where empno > 10`, calcite will try to find all the definitions in scope chain(current scope and parent scopes). If the `empno` is not find in current scope(Because of the `unnest`), calcite will try to use the parent scope(SelectScope) to resolve this field, but unluckily ParentScope can see the MatchRecognize namespace, and triggle this error(because MatchRecognize is still validating).

Now we use the `where emp.empno > 10` to avoid this error, but i still think it is the bug of calcite.