hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gopal V (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-21012) CBO: CASE + CONCAT expr is not converted to optimized SQL
Date Wed, 05 Dec 2018 23:01:00 GMT

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

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}

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


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

Mime
View raw message