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)