[
https://issues.apache.org/jira/browse/CALCITE-5834?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17741715#comment-17741715
]
Francesco Gini commented on CALCITE-5834:
-----------------------------------------
I have spent some time looking why this is happening. The relation tree looks
correct, the problem seems to be in going from the relation tree to the sql.
First the "main" logical tree is traversed and the alias _sales_fact_1997_ is
assigned to the outer table. Then the subquery condition is also traversed, the
table get given alias _sales_fact_19970_ and the where condition is rewritten
to replace the reference to the correlation variable. However, the name
_sales_fact_1997_ is used which in the subquery context would resolved to the
table in the subquery.
I think one way to solve it could be to always assign a unique alias name to
the outer table when there are correlation variable on the rel node. I am
attaching a patch [^unique-alias-patch.patch] that does that. It does not feel
optimal as it would add an alias in situation where the subquery does use other
tables thus not provoking this issue (see tests). However, this approach limit
the changes that are required.
Let me know if you prefer discuss this with a pr. For now I resisted to open
one as I am not sure the approach I am proposing is the right one anyway. Thanks
> Rel to Sql wrong output for correlated subqueries using the same table
> ----------------------------------------------------------------------
>
> Key: CALCITE-5834
> URL: https://issues.apache.org/jira/browse/CALCITE-5834
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: Francesco Gini
> Priority: Major
> Attachments: test-patch.patch, unique-alias-patch.patch
>
>
> A correlated query with a filter on a table that is also in the outer scope
> is converted into a sql that would produce the wrong results.
> Below is a test for RelToSqlConverterTest that reproduces the problem (also
> attached as patch: test-patch.patch)
> {code:java}
> @Test void testSubQueryInWhereAlias() {
> String query = "select s1.\"customer_id\", s1.\"unit_sales\"\n"
> + "from \"sales_fact_1997\" s1\n"
> + "where s1.\"store_sales\" in\n"
> + "(select max(\"store_sales\") from \"sales_fact_1997\" s2 where
> s2.\"customer_id\" = s1.\"customer_id\"\n"
> + ")";
> final String wrong = "SELECT \"customer_id\", \"unit_sales\"\n"
> + "FROM \"foodmart\".\"sales_fact_1997\"\n"
> + "WHERE \"store_sales\" IN (SELECT MAX(\"store_sales\")\n"
> + "FROM \"foodmart\".\"sales_fact_1997\"\n"
> + "WHERE \"customer_id\" = \"sales_fact_1997\".\"customer_id\")";
> sql(query).ok(wrong);
> }{code}
> The sql produced has a filter
> {code:java}
> WHERE \"customer_id\" = \"sales_fact_1997\".\"customer_id\" {code}
> whose columns both refer to the subquery table, whereas the right hand side
> of the filter should refer to the table in the outer query
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
