[
https://issues.apache.org/jira/browse/CALCITE-5834?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Francesco Gini updated CALCITE-5834:
------------------------------------
Attachment: unique-alias-patch.patch
> 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)
