[ https://issues.apache.org/jira/browse/CALCITE-6346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Corvin Kuebler updated CALCITE-6346: ------------------------------------ Description: Hey, today we encouraged some issues with the jdbc adapter behaviour. We have a statement like this: SELECT * {code:java} SELECT CASE WHEN CAST(? AS VARCHAR) = CAST(? AS VARCHAR) THEN "NAME" END FROM "AUTHORS" The parameters are ["some", 1]. {code} The statement that is pushed via JDBC Adapter looks like this: {code:java} SELECT * SELECT CASE WHEN ? = ? THEN "NAME" END FROM "AUTHORS" {code} The cast in the resulting statement is lost and therefore we get: ERROR: operator does not exist: character varying = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts. As this example is not minimal I was able to reproduce it with the following test in JdbcTests: {code:java} @Test void testFilterPush() { CalciteAssert.that() .with(CalciteAssert.Config.FOODMART_CLONE) .query("SELECT * FROM \"foodmart\".\"sales_fact_1997\"" + " WHERE cast(? as varchar) = cast(? as varchar)") .planHasSql("SELECT *\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE cast(? as varchar) = cast(? as varchar)"); } {code} The test shows the exact behaviour as it fails with the following: Caused by: java.sql.SQLSyntaxErrorException: data type cast needed for parameter or null literal in statement [SELECT * FROM "foodmart"."sales_fact_1997" WHERE ? = ? As can be seen the cast is also lost here. To me it seems like an obvious bug, but maybe I am missing something here. I hope some1 can help :) BR, Corvin was: Hey, today we encouraged some issues with the jdbc adapter behaviour. We have a statement like this: SELECT * SELECT CASE WHEN CAST(? AS VARCHAR) = CAST(? AS VARCHAR) THEN "NAME" END FROM "AUTHORS" The parameters are ["some", 1]. The statement that is pushed via JDBC Adapter looks like this: SELECT * SELECT CASE WHEN ? = ? THEN "NAME" END FROM "AUTHORS" The cast in the resulting statement is lost and therefore we get: ERROR: operator does not exist: character varying = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts. As this example is not minimal I was able to reproduce it with the following test in JdbcTests: @Test void testFilterPush() { CalciteAssert.that() .with(CalciteAssert.Config.FOODMART_CLONE) .query("SELECT * FROM \"foodmart\".\"sales_fact_1997\"" + " WHERE cast(? as varchar) = cast(? as varchar)") .planHasSql("SELECT *\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE cast(? as varchar) = cast(? as varchar)"); } The test shows the exact behaviour as it fails with the following: Caused by: java.sql.SQLSyntaxErrorException: data type cast needed for parameter or null literal in statement [SELECT * FROM "foodmart"."sales_fact_1997" WHERE ? = ? As can be seen the cast is also lost here. To me it seems like an obvious bug, but maybe I am missing something here. I hope some1 can help :) BR, Corvin > JdbcAdapter: Cast for dynamic filter arguments is lost > ------------------------------------------------------ > > Key: CALCITE-6346 > URL: https://issues.apache.org/jira/browse/CALCITE-6346 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter > Affects Versions: 1.36.0 > Reporter: Corvin Kuebler > Priority: Major > > Hey, > today we encouraged some issues with the jdbc adapter behaviour. > We have a statement like this: > SELECT * > {code:java} > SELECT CASE WHEN CAST(? AS VARCHAR) = CAST(? AS VARCHAR) THEN "NAME" END > FROM "AUTHORS" > The parameters are ["some", 1]. > {code} > The statement that is pushed via JDBC Adapter looks like this: > {code:java} > SELECT * > SELECT CASE WHEN ? = ? THEN "NAME" END > FROM "AUTHORS" > {code} > The cast in the resulting statement is lost and therefore we get: > ERROR: operator does not exist: character varying = integer Hint: No operator > matches the given name and argument types. You might need to add explicit > type casts. > As this example is not minimal I was able to reproduce it with the following > test in JdbcTests: > {code:java} > @Test void testFilterPush() { > CalciteAssert.that() > .with(CalciteAssert.Config.FOODMART_CLONE) > .query("SELECT * FROM \"foodmart\".\"sales_fact_1997\"" + > " WHERE cast(? as varchar) = cast(? as varchar)") > .planHasSql("SELECT *\n" + > "FROM \"foodmart\".\"sales_fact_1997\"\n" + > "WHERE cast(? as varchar) = cast(? as varchar)"); > } > {code} > The test shows the exact behaviour as it fails with the following: > Caused by: java.sql.SQLSyntaxErrorException: data type cast needed for > parameter or null literal in statement [SELECT * > FROM "foodmart"."sales_fact_1997" > WHERE ? = ? > As can be seen the cast is also lost here. > To me it seems like an obvious bug, but maybe I am missing something here. > I hope some1 can help :) > BR, > Corvin -- This message was sent by Atlassian Jira (v8.20.10#820010)