[GitHub] [calcite] yuqi1129 commented on issue #1299: [CALCITE-3100] cast(? as DATE) won't work with PreparedStatement

[GitBox <gi...@apache.org>]

yuqi1129 commented on issue #1299: [CALCITE-3100] cast(? as DATE) won't work with PreparedStatement
URL: https://github.com/apache/calcite/pull/1299#issuecomment-509053107
 
 
   > hmm.. I think this CALCITE-3100 is really a good catch for the shortage when using `SqlDynamicParam` in `CAST`. The core problem is that `SqlValidator` infers the "type" of `SqlDynamicParam` by the "type" of `CAST`. And during the optimization, such a `CAST` will be regarded as redundant and get simplified/peeled.
   > 
   > But this PR just fixes the casting to type of `DATE/TIME`, what about other common types? e.g. cast(String as Integer) and so on ....
   > 
   > In my experiment, below simple query is failing
   > 
   > ```
   >     PreparedStatement pstmt = connection.prepareStatement("select \"name\", \"deptno\" from \"emps\" WHERE \"empid\"=cast(? as INTEGER)");
   >     pstmt.setString(1, "100");
   >     pstmt.execute();
   > ```
   > 
   > I believe I can construct lots of more failures like above. If we only fixs the casting to type of `DATE/TIME` in `RexToLixTranslator.java`, it feels like a too special case for RexToLixTranslator.
   > 
   > How do you think @danny0405 ?
   
   Your suggestion is impressive and meaningful, other types indeed have the same problem as date, I need to cover all cases when using `SqlDynamicParam ` besides the `date`

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services