hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Carter Shanklin (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-13582) E061-07 and E061-12: Quantified Comparison Predicates
Date Fri, 21 Apr 2017 15:00:06 GMT

    [ https://issues.apache.org/jira/browse/HIVE-13582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15978875#comment-15978875
] 

Carter Shanklin commented on HIVE-13582:
----------------------------------------

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)

Mime
View raw message