[jira] [Assigned] (HIVE-6030) Introduce Explain SubQuery Rewrite command

["Harish Butani (JIRA)" <ji...@apache.org>]

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

Harish Butani reassigned HIVE-6030:
-----------------------------------

    Assignee: Harish Butani

> Introduce Explain SubQuery Rewrite command
> ------------------------------------------
>
>                 Key: HIVE-6030
>                 URL: https://issues.apache.org/jira/browse/HIVE-6030
>             Project: Hive
>          Issue Type: Improvement
>            Reporter: Harish Butani
>            Assignee: Harish Butani
>
> There are several transformations happening for SubQuery predicates(HIVE-784). It is hard to tell from the explain plan how the Query is executed. 
> So the goal is to introduce an explain subquery rewrite command that will show the details on the rewrite. Fo e.g.:
> {noformat}
> -- non corr e.g.
> explain subquery rewrite select * from src where src.key in (select key from src s1 where s1.key > '9');
> -- outputs:
> select * from src left semi join (select key from src s1 where s1.key > '9') sq_1  on src.key = sq_1.key where 1 = 1
> -- corr e.g.
> explain subquery rewrite select * from src where src.key in (select key from src s1 where s1.key > '9');
> -- outputs
> select key 
>   from src b left semi join (select min(value), a.key as sq_corr_0 
>      from (select key, value, rank() over(partition by key order by value) as r from src) a 
>      where r <= 2 
>      group by a.key) sq_1  on b.key = sq_1.sq_corr_0 and b.value = sq_1._c0 where 1 = 1 and key < '9'
> {noformat}
> There are multiple rewrite cases:
> - corr vs non-cor
> - when 'not in' operator  is involved
> - a where clause predicate vs. a having clause predicate
> Not sure if it will be possible to output a valid Hive SQL query for all cases; but at least provide the user enough information to assemble a valid Hive query.
> Breaking this down into multiple subtasks.



--
This message was sent by Atlassian JIRA
(v6.1.4#6159)