[
https://issues.apache.org/jira/browse/HIVE-13582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15978875#comment-15978875
]
Carter Shanklin edited comment on HIVE-13582 at 4/21/17 3:03 PM:
-----------------------------------------------------------------
Since this came up on HIVE-15229, a few more notes on this item:
The syntax is Predicand Comparison Quantifier TableSubquery
The result of this expression is as if comparison is done between the predicand
and every row in the TableSubquery.
Any comparison operator should be supported (i.e. =, <>, >, <, >=, <=)
Quantifier can be ALL, SOME or ANY. SOME and ANY are aliases.
(R1) If the quantifier is ALL:
If T is empty or if the comparison is true for every row in TableSubquery, the
predicate is true for that predicand. If the comparison is false for at least
one row, the predicate is false.
(R2) If the quantifier is SOME or ANY:
If the comparison is true for at least one row in TableSubquery, the predicate
is true for that predicand. If T is empty or the comparison is False for each
row in TableSubquery, the predicate is false.
(R3) If the comparison is neither true or false, the result is unknown.
For example: consider this table called test:
{code}
c1 | c2
----+----
1 | 1
| 2
3 |
|
{code}
This query returns no rows:
select c1 from test where c1 > all ( select c2 from test );
This query returns one row:
select c1 from test where c1 > all ( select c2 from test where c2 is not null );
The other implication of (R3) is this:
{code}
select c1 from test where c1 > all ( select c2 from test ) is null;
c1
----
3
(3 rows)
{code}
One other note, the SQL standard says that you must compare to a table
subquery. Many databases also allow you to provide a list, (Oracle, Teradata,
Vertica, probably others).
Postgres does not allow this, though Postgres does allow you to put a table
value constructor in a subquery so this works:
Postgres:
select * from e011_01 where c1 = any ( values (1), (2) );
Oracle, Teradata, Vertica, maybe others:
select * from e011_01 where c1 = any ( 1, 2 );
This seems like a reasonable thing to support but isn't standard.
was (Author: cartershanklin):
Since this came up on HIVE-15229, a few more notes on this item:
The syntax is Predicand Comparison Quantifier TableSubquery
The result of this expression is as if comparison is done between the predicand
and every row in the TableSubquery.
Any comparison operator should be supported (i.e. =, <>, >, <, >=, <=)
Quantifier can be ALL, SOME or ANY. SOME and ANY are aliases.
If the quantifier is ALL:
If T is empty or if the comparison is true for every row in TableSubquery, the
predicate is true for that predicand. If the comparison is false for at least
one row, the predicate is false.
If the quantifier is SOME or ANY:
If the comparison is true for at least one row in TableSubquery, the predicate
is true for that predicand. If T is empty or the comparison is False for each
row in TableSubquery, the predicate is false.
If the comparison is neither true or false, the result is unknown.
For example: consider this table called test:
{code}
c1 | c2
----+----
1 | 1
| 2
3 |
|
{code}
This query returns no rows:
select c1 from test where c1 > all ( select c2 from test );
This query returns one row:
select c1 from test where c1 > all ( select c2 from test where c2 is not null );
One other note, the SQL standard says that you must compare to a table
subquery. Many databases also allow you to provide a list, (Oracle, Teradata,
Vertica, probably others).
Postgres does not allow this, though Postgres does allow you to put a table
value constructor in a subquery so this works:
Postgres:
select * from e011_01 where c1 = any ( values (1), (2) );
Oracle, Teradata, Vertica, maybe others:
select * from e011_01 where c1 = any ( 1, 2 );
This seems like a reasonable thing to support but isn't standard.
> E061-07 and E061-12: Quantified Comparison Predicates
> -----------------------------------------------------
>
> Key: HIVE-13582
> URL: https://issues.apache.org/jira/browse/HIVE-13582
> Project: Hive
> Issue Type: Sub-task
> Reporter: Carter Shanklin
>
> This is a part of the SQL:2011 Analytics Complete Umbrella JIRA HIVE-13554.
> Quantified comparison predicates (ANY/SOME/ALL) are mandatory in the SQL
> standard. Hive should support the predicates (E061-07) and you should be able
> to use these with subqueries (E061-12)
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
