calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Elser <josh.el...@gmail.com>
Subject Re: Problem when passing Date parameter in PreparedStatement
Date Wed, 07 Sep 2016 21:13:51 GMT
Just noticed that you have opened up CALCITE-1371 for this issue (tying 
this thread to that issue for the archives).

Josh Elser wrote:
> Hi Yiming,
>
> I am not sure about the error you listed below with your test case.
>
> Might I suggest that you try to write a test in Avatica instead of
> Calcite (since we believe the bug to be in Avatica).
>
> https://github.com/apache/calcite/blob/master/avatica/server/src/test/java/org/apache/calcite/avatica/RemoteDriverTest.java#L1648-L1680
>
>
> You should be able to copy the structure of an existing test case and
> verify that your custom TimeZone/Calendar is not used. The tests use an
> embedded HSQLDB instance which should make it simple to test.
>
> Yiming Liu wrote:
>> Hi Josh,
>>
>> I try to write some test cases, but meet exceptions. The code as
>> following,
>> could you help figure out what's wrong I made?
>>
>> @Test
>> public void testSetDate() throws Exception {
>> CalciteAssert.that()
>> .with(CalciteAssert.Config.JDBC_FOODMART)
>> .doWithConnection(
>> new Function<CalciteConnection, Void>() {
>> public Void apply(CalciteConnection connection) {
>> try {
>> PreparedStatement stmt1 = connection
>> .prepareStatement("select count(*), \"date\" from
>> \"foodmart\".\"currency\"\n"
>> + " where \"date\" = DATE'1998-01-01' group by
>> \"date\"");
>> ResultSet rs1 = stmt1.executeQuery();
>> assertTrue(rs1.next());
>> assertEquals(3, rs1.getInt(1));
>> assertEquals(Date.valueOf("1998-01-01"), rs1.getDate(2));
>>
>> PreparedStatement stmt2 = connection
>> .prepareStatement("select count(*), \"date\" from
>> \"foodmart\".\"currency\"\n"
>> + " where \"date\" = ? group by \"date\"");
>> TimeZone tzUtc = TimeZone.getTimeZone("UTC");
>> Calendar cUtc = Calendar.getInstance(tzUtc);
>> cUtc.set(1998, 0, 1, 0, 0, 0);
>> java.sql.Date passSqlDate = new
>> java.sql.Date(cUtc.getTimeInMillis());
>> stmt2.setDate(1, passSqlDate, cUtc);
>> ResultSet rs2 = stmt2.executeQuery();
>> assertTrue(rs2.next());
>> assertEquals(rs1.getInt(1), rs2.getInt(1));
>> assertEquals(rs1.getDate(2), rs2.getDate(2));
>> return null;
>> } catch (SQLException e) {
>> throw new RuntimeException(e);
>> }
>> }
>> });
>> }
>>
>> But the stmt2 prepare statement has the following exception:
>>
>> java.lang.AssertionError: Internal error: While invoking method
>> 'public org.apache.calcite.rel.rel2sql.SqlImplementor$Result
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
>>
>>
>> at org.apache.calcite.util.Util.newInternal(Util.java:792)
>> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:81)
>>
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:85)
>>
>> at
>> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:299)
>>
>> at
>> org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:91)
>>
>> at
>> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102)
>>
>> at
>> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1198)
>>
>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:299)
>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:193)
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:733)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:597)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:567)
>>
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:215)
>>
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:197)
>>
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:186)
>>
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:87)
>>
>> at
>> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:163)
>>
>> at org.apache.calcite.test.JdbcTest$16.apply(JdbcTest.java:5703)
>> at org.apache.calcite.test.JdbcTest$16.apply(JdbcTest.java:5692)
>> at
>> org.apache.calcite.test.CalciteAssert$AssertThat.doWithConnection(CalciteAssert.java:936)
>>
>> at org.apache.calcite.test.JdbcTest.testSetDate(JdbcTest.java:5689)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>
>> at java.lang.reflect.Method.invoke(Method.java:606)
>> 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:117)
>>
>> at
>> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
>>
>> at
>> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
>>
>> at
>> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
>> Caused by: java.lang.reflect.InvocationTargetException
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>
>> at java.lang.reflect.Method.invoke(Method.java:606)
>> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
>> ... 43 more
>> Caused by: java.lang.AssertionError: Internal error: While invoking
>> method 'public org.apache.calcite.rel.rel2sql.SqlImplementor$Result
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
>>
>> at org.apache.calcite.util.Util.newInternal(Util.java:792)
>> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:81)
>>
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:85)
>>
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:126)
>>
>> ... 48 more
>> Caused by: java.lang.reflect.InvocationTargetException
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>
>> at java.lang.reflect.Method.invoke(Method.java:606)
>> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
>> ... 51 more
>> Caused by: java.lang.AssertionError: Internal error: While invoking
>> method 'public org.apache.calcite.rel.rel2sql.SqlImplementor$Result
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Filter)'
>>
>> at org.apache.calcite.util.Util.newInternal(Util.java:792)
>> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:81)
>>
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:85)
>>
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:145)
>>
>> ... 56 more
>> Caused by: java.lang.reflect.InvocationTargetException
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>
>> at java.lang.reflect.Method.invoke(Method.java:606)
>> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
>> ... 59 more
>> Caused by: java.lang.ClassCastException:
>> org.apache.calcite.rex.RexDynamicParam cannot be cast to
>> org.apache.calcite.rex.RexCall
>> at
>> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:537)
>>
>> at
>> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:606)
>>
>> at
>> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:539)
>>
>> at
>> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:120)
>>
>> ... 64 more
>>
>>
>> 2016-09-07 9:47 GMT+08:00 Josh Elser<josh.elser@gmail.com>:
>>
>>> Hi Yiming,
>>>
>>> Not using the Calendar argument in AvaticaSite#setDate(Date, Calendar)
>>> looks like a bug (have not looked at the implementation, though).
>>> Want to
>>> file a JIRA issue to investigate this further, please?
>>>
>>> If you have the ability to provide a unit test to outline this breakage,
>>> it would be very helpful. A fix with the test would be the best ;)
>>>
>>> - Josh
>>>
>>>
>>> Yiming Liu wrote:
>>>
>>>> Hi Calcite devs,
>>>>
>>>> I have problem when passing date parameter in PreparedStatement.
>>>> Following
>>>> is the sample code:
>>>>
>>>> TimeZone tzUtc = TimeZone.getTimeZone("UTC");
>>>> Calendar cUtc = Calendar.getInstance(tzUtc);
>>>> cUtc.set(2013, 10, 5, 0, 0, 0);
>>>> java.sql.Date passSqlDate = new java.sql.Date(cUtc.getTimeInMillis());
>>>> statement.setDate(1, passSqlDate, cUtc);
>>>>
>>>> I try to pass Date'2013/11/5' to the database, but from the debug info
>>>> I found the
>>>>
>>>> database received '2013/10/4' (GMT -8 Timezone) always. It ignored the
>>>> Calendar I passed in.
>>>>
>>>>
>>>> There are some Calcite code a little strange, I have marked by color:
>>>>
>>>> Why setTimestamp and setTime have effective parameter 'calendar', but
>>>> setDate ignore the
>>>>
>>>> parameter. Is that the root cause?
>>>>
>>>> AvaticaSite.java
>>>>
>>>> Line 189-199
>>>>
>>>> public void setTimestamp(Timestamp x, Calendar calendar) {
>>>> slots[index] = wrap(ColumnMetaData.Rep.JAVA_SQL_TIMESTAMP, x,
>>>> calendar);
>>>> }
>>>>
>>>> public void setTime(Time x, Calendar calendar) {
>>>> slots[index] = wrap(ColumnMetaData.Rep.JAVA_SQL_TIME, x, calendar);
>>>> }
>>>>
>>>> public void setDate(Date x, Calendar cal) {
>>>> slots[index] = wrap(ColumnMetaData.Rep.JAVA_SQL_DATE, x, calendar);
>>>> }
>>>>
>>>>
>>>>
>>>>
>>
>>

Mime
View raw message