Francesco Gini created CALCITE-5834:
---------------------------------------
Summary: 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
Attachments: test-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)
