Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Content-Type: multipart/alternative;
 boundary="===============0906254325102108444=="
MIME-Version: 1.0
Subject: Re: Review Request 53845: 'like any' and 'like all' operators in hive
From: Simanchal Das <simanchal.das@outlook.com>
To: Vineet Garg <vgarg@hortonworks.com>, Carl Steinbach <carl@cloudera.com>
Cc: hive <dev@hive.apache.org>, Simanchal Das <simanchal.das@outlook.com>,
 Carl Steinbach <cws@apache.org>
Date: Sat, 22 Apr 2017 11:03:29 -0000
Message-ID: <20170422110329.23980.7969@reviews-vm2.apache.org>
Auto-Submitted: auto-generated
Sender: Simanchal Das <noreply@reviews.apache.org>
References: <20170302152613.39766.49144@reviews-vm2.apache.org>
In-Reply-To: <20170302152613.39766.49144@reviews-vm2.apache.org>
Reply-To: Simanchal Das <simanchal.das@outlook.com>
archived-at: Sat, 22 Apr 2017 11:03:36 -0000

--===============0906254325102108444==
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit


-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------

(Updated April 22, 2017, 11:03 a.m.)


Review request for hive, Carl Steinbach and Vineet Garg.


Repository: hive-git


Description
-------

https://issues.apache.org/jira/browse/HIVE-15229


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.
--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%' ;

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.


Diffs (updated)
-----

  ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 8dc5f2e 
  ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g 0721b92 
  ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g d98a663 
  ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 8598fae 
  ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java 8f8eab0 
  ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION 
  ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION 
  ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION 
  ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION 
  ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION 
  ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION 
  ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION 
  ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION 
  ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION 
  ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION 
  ql/src/test/results/clientpositive/show_functions.q.out ac5ca41 
  ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION 
  ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION 


Diff: https://reviews.apache.org/r/53845/diff/9/

Changes: https://reviews.apache.org/r/53845/diff/8-9/


Testing
-------

Junit test cases and query.q files are attached


Thanks,

Simanchal Das


--===============0906254325102108444==--