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

Gopal V (JIRA) Wed, 05 Dec 2018 14:59:10 -0800


     [ 
https://issues.apache.org/jira/browse/HIVE-21012?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]


Gopal V updated HIVE-21012:
---------------------------
    Description: 
{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}

  was:
{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}


> 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
>            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}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

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

Reply via email to