[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16818600#comment-16818600 ] Danny Chan commented on CALCITE-3002: - It's true that now Calcite does not support implicit type coercion for Case when branches, you can do an explicit type cast to make all the branches type comparable. There is also a patch can do this CALCITE-2302. > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Construc
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16818603#comment-16818603 ] pengzhiwei commented on CALCITE-3002: - I think the case you show should be : {code:java} select (case when empno > 1 then 1 else empno end) from emps {code} or {code:java} select (case empno > 1 when true then 1 else empno end) from emps {code} > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newIn
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16823491#comment-16823491 ] Egor Ryashin commented on CALCITE-3002: --- I don't see it's incorrect as per [http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt] and CALCITE documentation !image-2019-04-23-00-29-31-703.png! Probably, at least a ticket about documentation update should be created then. > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.De
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16823738#comment-16823738 ] Julian Hyde commented on CALCITE-3002: -- In sql1992 it says bq. The data type of each WO shall be comparable with the data type of the . Your {{}} has type INTEGER and your {{}} has type BOOLEAN. > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16823907#comment-16823907 ] Egor Ryashin commented on CALCITE-3002: --- That's not the right clause, __ is used by that query and __ has a boolean result correctly. >From the specification: {quote} ::= WHEN THEN ::= WHEN THEN {quote} I use that query in *MySQL* and checked it in *PostgreSQL* too. > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.Delegati
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16823945#comment-16823945 ] Egor Ryashin commented on CALCITE-3002: --- I see according to specification 92 the statement is actually incorrect, I still wonder why it works in SQL DBs. > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.new
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16823960#comment-16823960 ] Hongze Zhang commented on CALCITE-3002: --- I probably missed something, but the case looks to be straightforward. [~egrs] - I think basically the below case expression: {code:sql} case empno when empno > 1 then 1 else empno end {code} is equivalent to: {code:sql} case when empno = (empno > 1) then 1 else empno end {code} In current version of Calcite, the comparison predicate (EQUALS, GREATER THAN, LESS THAN, etc.) are validated based on assumption that the operands are in the same SQL type family[1]. Maybe some other SQL implementations use some looser strategies but Calcite is not able to do that so far (And maybe there are some other JIRAs such as CALCITE-2302 suggesting loose strategies but I am not sure if the related changes could be bring into Calcite soon enough). [1] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFamily.java > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(A
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16824346#comment-16824346 ] Julian Hyde commented on CALCITE-3002: -- [~egrs] Did you really run it on PostgreSQL? If so, which version did you use, and what SQL did you use? Here's what happened for me; you can try it on [rextester|https://rextester.com/l/postgresql_online_compiler]: {code:java} drop table if exists emps; create table emps(empno int); select (case empno when empno > 1 then 1 else empno end) from emps; 42883: operator does not exist: integer = boolean {code} I know why the query succeeds on MySQL: it allows boolean to be treated as integer. But then MySQL sucks. > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 5
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16824397#comment-16824397 ] Egor Ryashin commented on CALCITE-3002: --- [~julianhyde] sorry, I was sure I tried exactly that in PostgreSQL, but I have the error right now in PostgreSQL 11.2: {code:java} select (case empno when empno > 1 then 1 else empno end) fro... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. {code} Thanks for the help > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > Fix For: 1.20.0 > > Attachments: image-2019-04-23-00-29-31-703.png > > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' 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:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at