[jira] [Commented] (CALCITE-3100) cast(? as DATE) won't work with PreparedStatement

["Danny Chan (JIRA)" <ji...@apache.org>]

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

Danny Chan commented on CALCITE-3100:
-------------------------------------

I have tried all other data types, it seems that all of the arguments would be force casted to Integer first(except for the Integer type), it's not as expected, and i'm figuring where the logic is...:(

> cast(? as DATE) won't work with PreparedStatement
> -------------------------------------------------
>
>                 Key: CALCITE-3100
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3100
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Li Xian
>            Assignee: Danny Chan
>            Priority: Minor
>
> When I do something like
>  
> {code:java}
> Connection connection =
>         DriverManager.getConnection("jdbc:calcite:", info);
> PreparedStatement ps = connection.prepareStatement("select * from EMPS where JOINEDAT=cast(? as DATE)");
> ps.setString(1, "1996-08-03");
> ps.execute();{code}
>  
> It will produce error like below
>  
> {code:java}
> java.sql.SQLException: exception while executing query: java.lang.String cannot be cast to java.lang.Integer
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
>  at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>  at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:577)
>  at org.apache.calcite.avatica.AvaticaPreparedStatement.execute(AvaticaPreparedStatement.java:239)
>  at org.apache.calcite.test.CsvTest.testDate(CsvTest.java:185)
>  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>  at java.lang.reflect.Method.invoke(Method.java:498)
>  at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
>  at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
>  at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
>  at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
>  at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
>  at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
>  at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
>  at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
>  at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
>  at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
>  at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
>  at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
>  at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
>  at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
>  at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
>  at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
>  at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
>  at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
>  Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer
>  at Baz$1$1.moveNext(Unknown Source)
>  at org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.<init>(Linq4j.java:675)
>  at org.apache.calcite.linq4j.Linq4j.enumeratorIterator(Linq4j.java:95)
>  at org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>  at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:90)
>  at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184)
>  at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
>  at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
>  at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:573)
>  ... 24 more
> {code}
>  
> It looks like that the 'cast' clause doesn't make effect here with the '?' placeholder parameter.
> I am able to reproduce this error by adding the below testcase to org.apache.calcite.test.CsvTest and run it
> {code:java}
> @Test
> public void testDate() throws Exception {
>   Properties info = new Properties();
>   info.put("model", jsonPath("model"));
>   Connection connection =
>           DriverManager.getConnection("jdbc:calcite:", info);
>   PreparedStatement ps = connection.prepareStatement("select * from EMPS where JOINEDAT=cast(? as DATE)");
>   ps.setString(1, "1996-08-03");
>   ps.execute();
> }
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)