Possible SqlConverter issue?

["Tremper, Diego (ESI)" <Di...@adp.com.INVALID>]

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.

Re: Possible SqlConverter issue?

[Julian Hyde <jh...@gmail.com>]

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) <Di...@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.