[jira] [Commented] (CALCITE-2121) CAST timestamp with time zone to SQL VARCHAR
[ https://issues.apache.org/jira/browse/CALCITE-2121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16313920#comment-16313920 ] Julian Hyde commented on CALCITE-2121: -- Also, CALCITE-2107. > CAST timestamp with time zone to SQL VARCHAR > > > Key: CALCITE-2121 > URL: https://issues.apache.org/jira/browse/CALCITE-2121 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: slim bouguerra >Assignee: Julian Hyde > > Running the following test at org.apache.calcite.test.DruidAdapterIT > {code} > @Test > public void testCastTimestampWithTimeZone() { > final String sql = "SELECT CAST(\"timestamp\" AS VARCHAR) FROM > \"foodmart\""; > sql(sql, FOODMART).runs(); > } > {code} > leads to > {code} > 2018-01-05 09:56:57,358 [main] ERROR - > org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot > convert value of type TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > 2018-01-05 09:56:57,360 [main] ERROR - > org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to > line 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > java.lang.RuntimeException: exception while executing [SELECT > CAST("timestamp" AS VARCHAR) FROM "foodmart"] > at > org.apache.calcite.test.CalciteAssert$AssertQuery.runs(CalciteAssert.java:1411) > at > org.apache.calcite.test.DruidAdapterIT.testCastTimestampWithTimeZone(DruidAdapterIT.java:3503) > 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.RuntimeException: With materializationsEnabled=false, > limit=0 > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:600) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.runs(CalciteAssert.java:1407) > ... 23 more > Caused by: java.sql.SQLException: Error while executing SQL "SELECT > CAST("timestamp" AS VARCHAR) FROM "foodmart"": From line 1, column 8 to line > 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > 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.AvaticaStatement.executeInternal(AvaticaStatement.java:156) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218) > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:568) > ... 24 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 8 to line 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at >
[jira] [Commented] (CALCITE-2121) CAST timestamp with time zone to SQL VARCHAR
[ https://issues.apache.org/jira/browse/CALCITE-2121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16313917#comment-16313917 ] Julian Hyde commented on CALCITE-2121: -- Yes, it's valid. Another way to look at the problem is that we added a new data type in CALCITE-1947, there is a lot of work to make sure that a type works in all circumstances, and we've not yet finished that work. Some areas to look at: * Consider adding a column to ReflectiveSchema.everyTypes, and search for tests that use it; * Read through SqlOperatorBaseTest for tests that test every type (e.g. cast types to and from string); * See SqlValidatorTest.testCastFails; * Search for other tests that use TIMESTAMP and seem to be operating on all types; * We don't have an equivalent function to CURRENT_TIMESTAMP that produces a TIMESTAMP WITH LOCAL TIME ZONE; * Also check CALCITE-1784 and mark it fixed if it's fixed. > CAST timestamp with time zone to SQL VARCHAR > > > Key: CALCITE-2121 > URL: https://issues.apache.org/jira/browse/CALCITE-2121 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: slim bouguerra >Assignee: Julian Hyde > > Running the following test at org.apache.calcite.test.DruidAdapterIT > {code} > @Test > public void testCastTimestampWithTimeZone() { > final String sql = "SELECT CAST(\"timestamp\" AS VARCHAR) FROM > \"foodmart\""; > sql(sql, FOODMART).runs(); > } > {code} > leads to > {code} > 2018-01-05 09:56:57,358 [main] ERROR - > org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot > convert value of type TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > 2018-01-05 09:56:57,360 [main] ERROR - > org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to > line 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > java.lang.RuntimeException: exception while executing [SELECT > CAST("timestamp" AS VARCHAR) FROM "foodmart"] > at > org.apache.calcite.test.CalciteAssert$AssertQuery.runs(CalciteAssert.java:1411) > at > org.apache.calcite.test.DruidAdapterIT.testCastTimestampWithTimeZone(DruidAdapterIT.java:3503) > 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.RuntimeException: With materializationsEnabled=false, > limit=0 > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:600) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.runs(CalciteAssert.java:1407) > ... 23 more > Caused by: java.sql.SQLException: Error while executing SQL "SELECT > CAST("timestamp" AS VARCHAR) FROM "foodmart"": From line 1, column 8 to line > 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at >
[jira] [Commented] (CALCITE-2121) CAST timestamp with time zone to SQL VARCHAR
[ https://issues.apache.org/jira/browse/CALCITE-2121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16313579#comment-16313579 ] slim bouguerra commented on CALCITE-2121: - FYI [~jcamachorodriguez] this is a Timezone related issue. I think is a valid cast since {code} "SELECT CAST(CAST(\"timestamp\" AS TIMESTAMP) AS VARCHAR) FROM \"foodmart\""{code} works fine. > CAST timestamp with time zone to SQL VARCHAR > > > Key: CALCITE-2121 > URL: https://issues.apache.org/jira/browse/CALCITE-2121 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: slim bouguerra >Assignee: Julian Hyde > > Running the following test at org.apache.calcite.test.DruidAdapterIT > {code} > @Test > public void testCastTimestampWithTimeZone() { > final String sql = "SELECT CAST(\"timestamp\" AS VARCHAR) FROM > \"foodmart\""; > sql(sql, FOODMART).runs(); > } > {code} > leads to > {code} > 2018-01-05 09:56:57,358 [main] ERROR - > org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot > convert value of type TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > 2018-01-05 09:56:57,360 [main] ERROR - > org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to > line 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > java.lang.RuntimeException: exception while executing [SELECT > CAST("timestamp" AS VARCHAR) FROM "foodmart"] > at > org.apache.calcite.test.CalciteAssert$AssertQuery.runs(CalciteAssert.java:1411) > at > org.apache.calcite.test.DruidAdapterIT.testCastTimestampWithTimeZone(DruidAdapterIT.java:3503) > 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.RuntimeException: With materializationsEnabled=false, > limit=0 > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:600) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.runs(CalciteAssert.java:1407) > ... 23 more > Caused by: java.sql.SQLException: Error while executing SQL "SELECT > CAST("timestamp" AS VARCHAR) FROM "foodmart"": From line 1, column 8 to line > 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > 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.AvaticaStatement.executeInternal(AvaticaStatement.java:156) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218) > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:568) > ... 24 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 8 to line 1, column 35: Cast function cannot convert value of type > TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) to type VARCHAR > at