[
https://issues.apache.org/jira/browse/HIVE-784?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13815290#comment-13815290
]
Harish Butani commented on HIVE-784:
------------------------------------
[~snarayanan] thanks for reviewing the doc. This is very helpful. Will add your
eg to the Spec.
You are right, the transformation doesn't work in case of nulls for 'Not In'.
Not sure how to solve this easily. Any thoughts?
One thought is to do a Cross product and then a Group By on the Outer Query's
columns, applying a new UDAF 'ALL' that takes a predicate and returns true if
all rows are true on the predicate. This was in the context of supporting
queries of the form ' > ALL' or '< ANY'.
So your e.g. would be transformed to something like this:
{code}
select t1.*, ALL(t1.x <> t2.y)
from t1 cross join t2
group by t1.x
having ALL(t1.x <> t2.y)
{code}
But this transformation can get quite involved if the Outer Query has a Group
By and/or windowing. So wasn't planning to do this in the first pass.
Also a cross join followed by GBy is expensive. It may make sense to support
these use cases with a SubQuery Operator.
> Support uncorrelated subqueries in the WHERE clause
> ---------------------------------------------------
>
> Key: HIVE-784
> URL: https://issues.apache.org/jira/browse/HIVE-784
> Project: Hive
> Issue Type: New Feature
> Components: Query Processor
> Reporter: Ning Zhang
> Assignee: Harish Butani
> Fix For: 0.13.0
>
> Attachments: D13443.1.patch, D13443.2.patch, HIVE-784.1.patch.txt,
> HIVE-784.2.patch, SubQuerySpec.pdf, tpchQueriesUsingSubQueryClauses.sql
>
>
> Hive currently only support views in the FROM-clause, some Facebook use cases
> suggest that Hive should support subqueries such as those connected by
> IN/EXISTS in the WHERE-clause.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
