[
https://issues.apache.org/jira/browse/HIVE-15229?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15968570#comment-15968570
]
Carter Shanklin commented on HIVE-15229:
----------------------------------------
[~simanchal] [~vgarg] [~pxiong]
This appears to be a Teradata extension to the SQL standard (not a bad one I'd
say). My question is whether this will contribute to generic quantified
comparison predicate support, possibly as a followup.
First, on the Teradata specific stuff, I'm referring to Teradata document "SQL
Functions, Operators, Expressions and Predicates", Release 14.10, page 928.
Teradata supports subqueries in addition to pattern expressions. I didn't see
any tests with subqueries so it's not clear to me that subqueries are
supported. In my experience with quantified predicates, they are typically used
with subqueries. That may be different for this TD specific extension but with
standard features it is almost always the case.
Also: With quantified comparison predicates SOME and ANY are aliases. The
Teradata documentation confirms that a user can specify either SOME or ANY.
I expect that Hive will eventually support full quantified comparison
predicates, all the commercial analytical databases already do. For an example:
where x = any ( select y from table where table.field = x );
IOW instead of like I could say =, <>, >, <, >=, <=. Based on the patch it
looks like the any/all is tied specifically to like. Maybe that's the Hive way
I honestly don't know since I hardly ever get below the SQL layer. All I want
to ask is whether this sets us down the right path to supporting these
comparison operators.
> 'like any' and 'like all' operators in hive
> -------------------------------------------
>
> Key: HIVE-15229
> URL: https://issues.apache.org/jira/browse/HIVE-15229
> Project: Hive
> Issue Type: New Feature
> Components: Operators
> Reporter: Simanchal Das
> Assignee: Simanchal Das
> Priority: Minor
> Attachments: HIVE-15229.1.patch, HIVE-15229.2.patch,
> HIVE-15229.3.patch, HIVE-15229.4.patch, HIVE-15229.5.patch
>
>
> In Teradata 'like any' and 'like all' operators are mostly used when we are
> matching a text field with numbers of patterns.
> 'like any' and 'like all' operator are equivalents of multiple like operator
> like example below.
> {noformat}
> --like any
> select col1 from table1 where col2 like any ('%accountant%', '%accounting%',
> '%retail%', '%bank%', '%insurance%');
> --Can be written using multiple like condition
> select col1 from table1 where col2 like '%accountant%' or col2 like
> '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like
> '%insurance%' ;
> --like all
> select col1 from table1 where col2 like all ('%accountant%', '%accounting%',
> '%retail%', '%bank%', '%insurance%');
> --Can be written using multiple like operator
> select col1 from table1 where col2 like '%accountant%' and col2 like
> '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like
> '%insurance%' ;
> {noformat}
> Problem statement:
> Now a days so many data warehouse projects are being migrated from Teradata
> to Hive.
> Always Data engineer and Business analyst are searching for these two
> operator.
> If we introduce these two operator in hive then so many scripts will be
> migrated smoothly instead of converting these operators to multiple like
> operators.
> Result:
> 1. 'LIKE ANY' operator return true if a text(column value) matches to any
> pattern.
> 2. 'LIKE ALL' operator return true if a text(column value) matches to all
> patterns.
> 3. 'LIKE ANY' and 'LIKE ALL' returns NULL not only if the expression on the
> left hand side is NULL, but also if one of the pattern in the list is NULL.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
