[
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 '<INTEGER> = <BOOLEAN>'.
> ----------------------------------------------------------------------------------------------------------------
>
> 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
> '<INTEGER> = <BOOLEAN>'. Supported form(s): '<COMPARABLE_TYPE> =
> <COMPARABLE_TYPE>' 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 '<INTEGER> = <BOOLEAN>'.
> Supported form(s): '<COMPARABLE_TYPE> = <COMPARABLE_TYPE>' 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
> '<INTEGER> = <BOOLEAN>'. Supported form(s): '<COMPARABLE_TYPE> =
> <COMPARABLE_TYPE>' 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.newInstance(Constructor.java:423) at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:783) at
> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:768) at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4776)
> at
> org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:280)
> at
> org.apache.calcite.sql.type.ComparableOperandTypeChecker.checkOperandTypes(ComparableOperandTypeChecker.java:66)
> at
> org.apache.calcite.sql.SqlOperator.checkOperandTypes(SqlOperator.java:659) at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) at
> org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:509) at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:144)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5501)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5488)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1635)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1620)
> at
> org.apache.calcite.sql.fun.SqlCaseOperator.checkOperandTypes(SqlCaseOperator.java:180)
> at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432)
> at
> org.apache.calcite.sql.fun.SqlCaseOperator.deriveType(SqlCaseOperator.java:165)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5501)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5488)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1635)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1620)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1779)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:456)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4040)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3303)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:973)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:949)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216) at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:924)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:628)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:552)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:264) at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230) at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:772)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:636)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:606)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:230)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> ... 32 more Caused by:
> org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '=' to
> arguments of type '<INTEGER> = <BOOLEAN>'. Supported form(s):
> '<COMPARABLE_TYPE> = <COMPARABLE_TYPE>' 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.newInstance(Constructor.java:423) at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 75
> more
> {code}
> Can be reproduced by running a unit test in
> _org/apache/calcite/test/CsvTest.java_ like:
> {code:java}
> @Test public void testCase() throws SQLException {
> sql("smart", "select (case empno when empno > 1 then 1 else empno end) from
> emps").ok();
> }{code}
> The same bug is reproduced in *Druid*.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
