egor-ryashin opened a new issue #7468: CASE with multiple types fails: Illegal mixing of types in CASE or COALESCE statement URL: https://github.com/apache/incubator-druid/issues/7468 When I use CASE statement with multiple types I get the exception, ie: ```sql select (case dim1 when dim1 = 'a' then dim1 else 0 end) from foo ``` ### Affected Version 0.13.0-incubating ### Description Try this test in `CalciteQueryTest`: ```java @Test public void testSelectConstantExpressionFromTableCaseDim() throws Exception { String sql = "select (case dim1 when dim1 = 'a' then dim1 else 0 end) from foo"; queryLogHook.clearRecordedQueries(); final List<Object[]> plannerResults = getResults(PLANNER_CONFIG_DEFAULT, QUERY_CONTEXT_DEFAULT, sql, CalciteTests.REGULAR_USER_AUTH_RESULT); for (Object[] plannerResult : plannerResults) { System.out.println(Arrays.asList(plannerResult)); } } ``` Exception: ``` org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 9 to line 1, column 54: Illegal mixing of types in CASE or COALESCE statement at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:190) at org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:124) at org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:105) at org.apache.druid.sql.calcite.CalciteQueryTest.getResults(CalciteQueryTest.java:7825) at org.apache.druid.sql.calcite.CalciteQueryTest.testSelectConstantExpressionFromTableCaseDim(CalciteQueryTest.java:379) 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.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:239) at org.apache.druid.sql.calcite.util.QueryLogHook$1.evaluate(QueryLogHook.java:95) at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48) at org.junit.rules.RunRules.evaluate(RunRules.java:20) 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.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) 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: org.apache.calcite.runtime.CalciteContextException: From line 1, column 9 to line 1, column 54: Illegal mixing of types in CASE or COALESCE statement 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:4759) at org.apache.calcite.sql.SqlCallBinding.newValidationError(SqlCallBinding.java:295) at org.apache.calcite.sql.fun.SqlCaseOperator.inferTypeFromValidator(SqlCaseOperator.java:250) at org.apache.calcite.sql.fun.SqlCaseOperator.inferReturnType(SqlCaseOperator.java:223) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:437) at org.apache.calcite.sql.fun.SqlCaseOperator.deriveType(SqlCaseOperator.java:165) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5483) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5470) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1629) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1614) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1773) at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:456) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4023) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3286) 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:967) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:943) at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:225) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:918) at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:628) at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:188) ... 39 more Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Illegal mixing of types in CASE or COALESCE statement 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) ... 64 more ``` Probably could be a blocker for fixing https://github.com/apache/incubator-druid/issues/6687
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
