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<[email protected]>:
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);
}
