Gopal V created HIVE-21012: ------------------------------ Summary: CBO: CASE + CONCAT expr is not converted to optimized SQL Key: HIVE-21012 URL: https://issues.apache.org/jira/browse/HIVE-21012 Project: Hive Issue Type: Bug Components: CBO Affects Versions: 4.0.0 Reporter: Gopal V
{code} create temporary table t3 (receipt_required_flag string, inspection_required_flag string); explain extended select CASE WHEN concat(concat(coalesce(t3.receipt_required_flag, 'N'), '~'), coalesce(t3.inspection_required_flag, 'N')) = 'N~N' THEN '2-Way Match' WHEN concat(concat(coalesce(t3.receipt_required_flag, 'N'), '~'), coalesce(t3.inspection_required_flag, 'N')) = 'Y~N' THEN '3-Way Match' WHEN concat(concat(coalesce(t3.receipt_required_flag, 'N'), '~'), coalesce(t3.inspection_required_flag, 'N')) = 'Y~Y' THEN '4-Way Match' END from t3; {code} does not print an optimized SQL because {code} 2018-12-05T22:52:26,193 WARN [HiveServer2-Background-Pool: Thread-2100] parse.CalcitePlanner: Rel2SQL Rewrite threw error java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlSyntax$6: SPECIAL at org.apache.calcite.util.Util.needToImplement(Util.java:927) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlSyntax$6.unparse(SqlSyntax.java:116) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:332) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:333) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:103) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlUtil.unparseBinarySyntax(SqlUtil.java:323) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlSyntax$3.unparse(SqlSyntax.java:65) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:332) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:333) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:103) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.fun.SqlCaseOperator.unparse(SqlCaseOperator.java:306) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:333) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:103) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlAsOperator.unparse(SqlAsOperator.java:76) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:333) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:103) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlNodeList.commaList(SqlNodeList.java:121) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlOperator.unparseListClause(SqlOperator.java:349) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlOperator.unparseListClause(SqlOperator.java:338) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:152) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:240) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:152) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:158) ~[calcite-core-1.16.0.3.1.0.0-SNAPSHOT.jar:1.16.0.3.1.0.0-SNAPSHOT] at org.apache.hadoop.hive.ql.parse.CalcitePlanner.getOptimizedSql(CalcitePlanner.java:1477) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT] {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)