You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "xiong duan (Jira)" <ji...@apache.org> on 2025/01/13 12:42:00 UTC
[jira] [Updated] (CALCITE-6785) RelToSqlConverter generate wrong sql when UNNEST has a correlate variable
[ https://issues.apache.org/jira/browse/CALCITE-6785?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
xiong duan updated CALCITE-6785:
--------------------------------
Description:
The unit test case in RelToSqlConverterTest:
{code:java}
@Test void testUnnest() {
final String sql = "select \"department_id\",split(\"department_description\", ','), unnestValues as code_path\n"
+ "from \"foodmart\".\"department\",UNNEST(split(\"department_description\", ',')) as unnestValues";
final String expected = "SELECT \"$cor0\".\"department_id\", SPLIT(\"$cor0\".\"department_description\", ','), \"t10\".\"col_0\" AS \"CODE_PATH\"\n"
+ "FROM (SELECT \"department_id\", \"department_description\", SPLIT(\"department_description\", ',') AS \"$f2\"\n"
+ "FROM \"foodmart\".\"department\") AS \"$cor0\",\n"
+ "LATERAL UNNEST (SELECT \"$cor0\".\"$f2\"\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"col_0\") AS \"t10\"";
sql(sql).withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
}
{code}
{code:java}
UNNEST (SELECT \"$cor0\".\"$f2\" FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"col_0\") AS \"t10\"{code}
is a illegal expression.
was:
The unit test case in RelToSqlConverterTest:
{code:java}
@Test void testUnnest() {
final String sql = "select \"department_id\",split(\"department_description\", ','), unnestValues as code_path\n"
+ "from \"foodmart\".\"department\",UNNEST(split(\"department_description\", ',')) as unnestValues";
final String expected = "SELECT \"$cor0\".\"department_id\", SPLIT(\"$cor0\".\"department_description\", ','), \"t10\".\"col_0\" AS \"CODE_PATH\"\n"
+ "FROM (SELECT \"department_id\", \"department_description\", SPLIT(\"department_description\", ',') AS \"$f2\"\n"
+ "FROM \"foodmart\".\"department\") AS \"$cor0\",\n"
+ "LATERAL UNNEST (SELECT \"$cor0\".\"$f2\"\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"col_0\") AS \"t10\"";
sql(sql).withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
}
{code}
> RelToSqlConverter generate wrong sql when UNNEST has a correlate variable
> -------------------------------------------------------------------------
>
> Key: CALCITE-6785
> URL: https://issues.apache.org/jira/browse/CALCITE-6785
> Project: Calcite
> Issue Type: Bug
> Reporter: xiong duan
> Priority: Major
>
> The unit test case in RelToSqlConverterTest:
> {code:java}
> @Test void testUnnest() {
> final String sql = "select \"department_id\",split(\"department_description\", ','), unnestValues as code_path\n"
> + "from \"foodmart\".\"department\",UNNEST(split(\"department_description\", ',')) as unnestValues";
> final String expected = "SELECT \"$cor0\".\"department_id\", SPLIT(\"$cor0\".\"department_description\", ','), \"t10\".\"col_0\" AS \"CODE_PATH\"\n"
> + "FROM (SELECT \"department_id\", \"department_description\", SPLIT(\"department_description\", ',') AS \"$f2\"\n"
> + "FROM \"foodmart\".\"department\") AS \"$cor0\",\n"
> + "LATERAL UNNEST (SELECT \"$cor0\".\"$f2\"\n"
> + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"col_0\") AS \"t10\"";
> sql(sql).withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
> }
> {code}
> {code:java}
> UNNEST (SELECT \"$cor0\".\"$f2\" FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"col_0\") AS \"t10\"{code}
> is a illegal expression.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)