[
https://issues.apache.org/jira/browse/CALCITE-6157?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17817245#comment-17817245
]
Corvin Kuebler edited comment on CALCITE-6157 at 2/14/24 8:26 AM:
------------------------------------------------------------------
We recently ran into the same issue again (other context but similar query).
Since we are still unsure of the exact conditions of this happening and the
error messages we get are quite missleading I'll go ahead and add an
RTException that atleast hints at the root cause of the isue.
was (Author: JIRAUSER299611):
We recently ran into the same issue again (other context but similar query).
I'll open a PR that throws an exception if the unusual behaviour is detected
> SqlValidatorImpl breaks join condition when inlining table alias
> ----------------------------------------------------------------
>
> Key: CALCITE-6157
> URL: https://issues.apache.org/jira/browse/CALCITE-6157
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Reporter: Ulrich Kramer
> Priority: Major
> Labels: pull-request-available
>
> When switching from 1.35.0 to 1.36.0, {{SqlValidatorImpl}} breaks the join
> condition on the following statement
> {code:SQL}
> WITH `it1` AS (
> SELECT
> `company`,
> `area`,
> `revenue`,
> `regionId`
> FROM
> `12345678-1234-1234-1234-000100000001`.`revenues`
> )
> SELECT
> *
> FROM
> (
> SELECT
> `T1`.`company` AS `company`,
> MAX(`T1`.`revenue`) AS `revenue`
> FROM
> `it1` AS `T1`
> GROUP BY
> `company`
> ) AS `T`
> LEFT JOIN `it1` AS `T2` ON `T`.`company` = `T2`.`company`
> AND `T`.`revenue` = `T2`.`revenue`
> {code}
> It modifies the join condition [at this
> location|https://github.com/apache/calcite/blob/d3ab0bc8e4d4c9ebc0fc4e33ce478d276f5d11e4/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L3603-L3604]
> to
> {code:SQL}
> `T`.`company` = `T1`.`company`
> AND `T`.`revenue` = `T1`.`revenue`
> {code}
> which causes a "{{Table `T1`not found}}" exception.
> I already tried to reproduce the problem by adding the following test to
> {{SqlValidatorTest}}, but everything worked fine
> {code:java}
> @Test void testWith2() {
> sql("with it1 as (select empno, sal, deptno from emp)\n"
> + "select * from ( select t1.empno as empno, max(t1.deptno) as deptno
> from it1 as t1 group by empno) as t\n"
> + "left outer join it1 as t2 on t.empno = t2.empno and t.deptno =
> t2.deptno")
> .ok();
> }
> {code}
> During debugging, I recognized that the {{DelegatingScope}} does something
> different in our case [at this
> location|https://github.com/apache/calcite/blob/d3ab0bc8e4d4c9ebc0fc4e33ce478d276f5d11e4/core/src/main/java/org/apache/calcite/sql/validate/DelegatingScope.java#L333-L337].
> In our case the {{fromNs}} resolved to {{`it1` AS `T1`}}, which is wrong.
> But I was not able to find the root cause yet.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
