[jira] [Commented] (CALCITE-5834) Rel to Sql wrong output for correlated subqueries using the same table

["Francesco Gini (Jira)" <ji...@apache.org>]

    [ 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)