Yes, it’s a Calcite issue.
The correct plan would create two Correlate instances, because the correlating
variables are set by different outer-loops.
I think you can defer decorrelation until after SqlToRel. That might work
better. In your test, add ".withDecorrelate(false)”. Then add another test in
RelOptRulesTest with “.withLateDecorrelation(true)”.
Julian
> On Nov 24, 2021, at 2:40 PM, Tremper, Diego (ESI)
> <diego.trem...@adp.com.INVALID> wrote:
>
> Hello,
>
> First, thanks to this community for supporting this amazing project.
> I’m using Calcite to convert sql statements into relational algebra, when
> running my tests with assertions enabled I’m facing the following issue to a
> specific statement:
>
> java.lang.AssertionError: All correlation variables should resolve to the
> same namespace. Prev
> ns=org.apache.calcite.sql.validate.IdentifierNamespace@a50d709, new
> ns=org.apache.calcite.sql.validate.IdentifierNamespace@5ed4bc
>
> I was able to reproduce the same behavior using SqlToRelConverterTest as
> follow:
>
>
> @Test public void testCorrelationIssue() {
> String sql = "select (\n" +
> " select count(1)\n" +
> " from dept dept2\n" +
> " where dept2.deptno = dept1.deptno\n" +
> " and dept2.deptno = emp2.deptno\n" +
> " )\n" +
> " from dept dept1\n" +
> " join emp emp1 ON emp1.deptno = dept1.deptno\n" +
> " join emp emp2 ON emp2.deptno = dept1.deptno";
> sql(sql).trim(true).ok();
> }
>
> Could this behavior be a calcite issue or am I missing something?
>
> NOTE: The SQL itself does not make much sense in this context, but I tried to
> provide the minimal statement to reproduce the same behavior.
>
> Thanks,
> Tremper.
>
>
> This message and any attachments are intended only for the use of the
> addressee and may contain information that is privileged and confidential. If
> the reader of the message is not the intended recipient or an authorized
> representative of the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, notify the sender immediately by return
> email and delete the message and any attachments from your system.
