[jira] [Commented] (CALCITE-5622) Cast is lost from plan to postgres statement

["Benchao Li (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5622?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17706812#comment-17706812 ] 

Benchao Li commented on CALCITE-5622:
-------------------------------------

You mean {{DATE '2023-03-27'}} should be {{CAST('2023-03-27' AS DATE)}}?

> Cast is lost from plan to postgres statement
> --------------------------------------------
>
>                 Key: CALCITE-5622
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5622
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>    Affects Versions: 1.34.0
>            Reporter: Corvin Kuebler
>            Priority: Major
>
> Hey,
> I found an issue with translation from plan to postgres sql statement.
> Adding the following Test to `JdbcAdapterTests.java` exposes the bug:
> {code:java}
> @Test void testDateCastPlan() {
> final String sql = "SELECT CAST(T1.\"brand_name\" AS DATE)\n"
> + "FROM \"foodmart\".\"product\" AS T1\n"
> + "WHERE CAST(T1.\"brand_name\" AS DATE) = CAST('2023-03-27' AS DATE)";
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query(sql)
> .explainContains("PLAN=JdbcToEnumerableConverter\n" +
> " JdbcProject(EXPR$0=[CAST($2):DATE])\n" +
> " JdbcFilter(condition=[=(CAST($2):DATE, 2023-03-27)])\n" +
> " JdbcTableScan(table=[[foodmart, product]])\n\n")
> .runs()
> .enable(CalciteAssert.DB == DatabaseInstance.POSTGRESQL)
> .planHasSql("SELECT CAST(\"brand_name\" AS DATE)\n" +
> "FROM \"foodmart\".\"product\"\n" +
> "CAST(\"brand_name\" AS DATE)= DATE '2023-03-27'");
> }{code}
>  
> The plan contains the cast of the column to DATE type. The resulting SQL Statement however does not contain the cast. 
> The test fails in run, as the column I'm trying to cast does not contain date values but the sql that is being sent to postgres is already wrong.
>  
> I'd be happy if you could take a look at it.
>  
> Best regards,
> Corvin
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)