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.