You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Yiming Liu <li...@gmail.com> on 2016/09/06 16:32:39 UTC
Problem when passing Date parameter in PreparedStatement
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);
}
--
With Warm regards
Yiming Liu (刘一鸣)
Re: Problem when passing Date parameter in PreparedStatement
Posted by Josh Elser <jo...@gmail.com>.
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<jo...@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);
>>>> }
>>>>
>>>>
>>>>
>>>>
>>
>>
Re: Problem when passing Date parameter in PreparedStatement
Posted by Josh Elser <jo...@gmail.com>.
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<jo...@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);
>>> }
>>>
>>>
>>>
>>>
>
>
Re: Problem when passing Date parameter in PreparedStatement
Posted by Yiming Liu <li...@gmail.com>.
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 <jo...@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);
>> }
>>
>>
>>
>>
--
With Warm regards
Yiming Liu (刘一鸣)
Re: Problem when passing Date parameter in PreparedStatement
Posted by Josh Elser <jo...@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);
> }
>
>
>