Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-21012

CBO: CASE + CONCAT expr is not converted to optimized SQL

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.0.0
    • Fix Version/s: None
    • Component/s: CBO
    • Labels:
      None

      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)])
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jcamachorodriguez Jesus Camacho Rodriguez
                Reporter:
                gopalv Gopal V
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: