[HIVE-21012] CBO: CASE + CONCAT expr is not converted to optimized SQL

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Component/s: CBO
• Assignee: Unassigned
• Reporter: Gopal Vijayaraghavan
• Created: 05/Dec/18 22:56
• Updated: 27/Feb/24 22:23

Description

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'
WHEN concat(concat(coalesce(t3.receipt_required_flag, 'N'), '~'), coalesce(t3.inspection_required_flag, 'N')) = 'Y~N' THEN '3'
WHEN concat(concat(coalesce(t3.receipt_required_flag, 'N'), '~'), coalesce(t3.inspection_required_flag, 'N')) = 'Y~Y' THEN '4'
END from t3;

does not print an optimized SQL because

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]

The calcite node is

HiveProject(_o__c0=[CASE(=(||(||(CASE(IS NOT NULL($0), $0, _UTF-16LE'N'), _UTF-16LE'~'), CASE(IS NOT NULL($1), $1, _UTF-16LE'N')), _UTF-16LE'N~N'), _UTF-16LE'2', =(||(||(CASE(IS NOT NULL($0), $0, _UTF-16LE'N'), _UTF-16LE'~'), CASE(IS NOT NULL($1), $1, _UTF-16LE'N')), _UTF-16LE'Y~N'), _UTF-16LE'3', =(||(||(CASE(IS NOT NULL($0), $0, _UTF-16LE'N'), _UTF-16LE'~'), CASE(IS NOT NULL($1), $1, _UTF-16LE'N')), _UTF-16LE'Y~Y'), _UTF-16LE'4', null)])

Issue Links

• depends upon: HIVE-20822 Improvements to push computation to JDBC from Calcite