[
https://issues.apache.org/jira/browse/CALCITE-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16962676#comment-16962676
]
Danny Chen commented on CALCITE-3456:
-------------------------------------
Well, i dug a little and find that the BalckBoard would reuse the SubQuery
within the same scope when registering them:
{code:java}
void registerSubQuery(SqlNode node, RelOptUtil.Logic logic) {
for (SubQuery subQuery : subQueryList) {
if (node.equalsDeep(subQuery.node, Litmus.IGNORE)) {
return;
}
}
subQueryList.add(new SubQuery(node, logic));
}
{code}
For this case, there are 2 IN sub-query, one is within the filter and one is in
the project list with the agg call, the
second one was reused by the first but the referencing fields offset/index is
different.
I have no idea how to fix this, it seems that we should identifier the SubQuery
not only by the subquery SqlNode digest, we should also identifier that they
belong to the same relational node.
> AssertionError throws when aggregation same digest in subquery in different
> scope
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-3456
> URL: https://issues.apache.org/jira/browse/CALCITE-3456
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.21.0
> Reporter: Danny Chen
> Assignee: Danny Chen
> Priority: Major
> Fix For: 1.22.0
>
>
> Check this sql in SqlToRelConverterTest:
> {code:java}
> @Test public void testAggregateWithCaseWhen() {
> final String sql = "select\n"
> + " CASE WHEN job IN ('810000', '820000') THEN job\n"
> + " ELSE 'error'\n"
> + " END AS id,\n"
> + " count(empno)\n"
> + "FROM emp\n"
> + "where job <> '' or job in ('810000', '820000')\n"
> + "GROUP by deptno, job";
> sql(sql).ok();
> }
> {code}
> I tested PostgreSQL 9.6 and MySQL 5.6, this is a valid SQL and they both
> outputs the right result.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
