[ https://issues.apache.org/jira/browse/CALCITE-6346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17840024#comment-17840024 ]
Ulrich Kramer commented on CALCITE-6346: ---------------------------------------- This seems to be related to {{RexSimplify::simplifyCast}} {code:java} if (sameTypeOrNarrowsNullability(e.getType(), operand.getType())) { return operand; } {code} should be {code:java} if (sameTypeOrNarrowsNullability(e.getType(), operand.getType()) && !(operand instanceof RexDynamicParam)) { return operand; } {code} > 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: > {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 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 in the exception message, the cast is also lost in this > scenario > 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)