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 15:28:37 GMT
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