[CALCITE-5622] Cast is lost from plan to postgres statement

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.34.0
• Component/s: jdbc-adapter
• Assignee: Unassigned
• Reporter: Corvin Kuebler
• Created: 30/Mar/23 09:20
• Updated: 17/Apr/24 14:17
• Resolved: 17/Apr/24 14:17

Description

Hey,

I found an issue with translation from plan to postgres sql statement.

Adding the following Test to `JdbcAdapterTests.java` exposes the bug:

@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'");
}

 
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