[
https://issues.apache.org/jira/browse/HIVE-21012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16710802#comment-16710802
]
Ashutosh Chauhan commented on HIVE-21012:
-----------------------------------------
This is because of a bug in Calcite which [~kgyrtkirk] has already fixed. After
upgrading to calcite-1.18 this should go away.
> 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
> Assignee: Jesus Camacho Rodriguez
> Priority: Major
>
> {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'
> 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;
> {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}
> The calcite node is
> {code}
> 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)])
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
