You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Hive QA (JIRA)" <ji...@apache.org> on 2017/02/09 16:46:41 UTC
[jira] [Commented] (HIVE-15229) 'like any' and 'like all' operators
in hive
[ https://issues.apache.org/jira/browse/HIVE-15229?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15859773#comment-15859773 ]
Hive QA commented on HIVE-15229:
--------------------------------
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12851824/HIVE-15229.2.patch
{color:red}ERROR:{color} -1 due to build exiting with an error
Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/3465/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/3465/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-3465/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Tests exited with: NonZeroExitCodeException
Command 'bash /data/hiveptest/working/scratch/source-prep.sh' failed with exit status 1 and output '+ date '+%Y-%m-%d %T.%3N'
2017-02-09 16:46:02.411
+ [[ -n /usr/lib/jvm/java-8-openjdk-amd64 ]]
+ export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
+ JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
+ export PATH=/usr/lib/jvm/java-8-openjdk-amd64/bin/:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
+ PATH=/usr/lib/jvm/java-8-openjdk-amd64/bin/:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
+ export 'ANT_OPTS=-Xmx1g -XX:MaxPermSize=256m '
+ ANT_OPTS='-Xmx1g -XX:MaxPermSize=256m '
+ export 'MAVEN_OPTS=-Xmx1g '
+ MAVEN_OPTS='-Xmx1g '
+ cd /data/hiveptest/working/
+ tee /data/hiveptest/logs/PreCommit-HIVE-Build-3465/source-prep.txt
+ [[ false == \t\r\u\e ]]
+ mkdir -p maven ivy
+ [[ git = \s\v\n ]]
+ [[ git = \g\i\t ]]
+ [[ -z master ]]
+ [[ -d apache-github-source-source ]]
+ [[ ! -d apache-github-source-source/.git ]]
+ [[ ! -d apache-github-source-source ]]
+ date '+%Y-%m-%d %T.%3N'
2017-02-09 16:46:02.413
+ cd apache-github-source-source
+ git fetch origin
+ git reset --hard HEAD
HEAD is now at 27613f0 HIVE-15792 : Hive should raise SemanticException when LPAD/RPAD pad character's length is 0 (Nanda Kumar via Thejas Nair)
+ git clean -f -d
Removing ql/src/test/queries/clientpositive/view_cbo.q
Removing ql/src/test/results/clientpositive/view_cbo.q.out
+ git checkout master
Already on 'master'
Your branch is up-to-date with 'origin/master'.
+ git reset --hard origin/master
HEAD is now at 27613f0 HIVE-15792 : Hive should raise SemanticException when LPAD/RPAD pad character's length is 0 (Nanda Kumar via Thejas Nair)
+ git merge --ff-only origin/master
Already up-to-date.
+ date '+%Y-%m-%d %T.%3N'
2017-02-09 16:46:04.011
+ patchCommandPath=/data/hiveptest/working/scratch/smart-apply-patch.sh
+ patchFilePath=/data/hiveptest/working/scratch/build.patch
+ [[ -f /data/hiveptest/working/scratch/build.patch ]]
+ chmod +x /data/hiveptest/working/scratch/smart-apply-patch.sh
+ /data/hiveptest/working/scratch/smart-apply-patch.sh /data/hiveptest/working/scratch/build.patch
error: patch failed: ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g:393
error: ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g: patch does not apply
error: patch failed: ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g:92
error: ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g: patch does not apply
The patch does not appear to apply with p0, p1, or p2
+ exit 1
'
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12851824 - PreCommit-HIVE-Build
> '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
>
>
> 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)