You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by John Omernik <jo...@omernik.com> on 2012/04/04 15:40:14 UTC
LIKE Statement
I did a test today:
select * from table where 'hello' like '%el%' limit 10;
I got 10 rows from my table
select * from table where 'HELLO' like '%el%' limit 10;
I got 0 rows from the same table.
Based on this, it would appear the like statement is case sensitive.
Coming from other RDBMs, this would be counter to the common knowledge.
(I've included a piece from the mysql documentation below). While I know
hive ISN'T a RDBMS, it is trying to ease the transition into using bigdata
for people who know SQL. This is a commonly used aspect of RDBMS and for
someone moving to hive, they may write this statement, get no results and
assume there are no results that match. This is not clear in the hive-wiki
documentation, nor is it something that a reasonable user should be
"expected" to know or even test prior to using hive. I think the like
statement should be changed to be case-insensitive to match it's function
in other DBMS Thoughts?
Mysql Documentation Reference
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
The following two statements illustrate that string comparisons are not
case sensitive unless one of the operands is a binary string:
mysql> *SELECT 'abc' LIKE 'ABC';*
-> 1
mysql> *SELECT 'abc' LIKE BINARY 'ABC';*
-> 0
Re: LIKE Statement
Posted by John Omernik <jo...@omernik.com>.
Well the link provided isn't really about what I originally asked about. I
have not come across a SQL implementation (Postgres, MySQL, or MSSQL are
the ones I have experience in) where LIKE was "by default" case sensitive
with wildcards. That being said, I'm not the type to based my assertions
on my own experience.
That being said, I have understood that in other implementations
users/administrators have the option to specify whether LIKE is case
sensitive (I saw that in the posted link) Could we have that? If we can't
agree on what other databases are doing, can we at least agree that some
other databases do it differently by default, and for organizations making
the move from a more traditional system to hive, having the option may help
their transition?
On Mon, May 14, 2012 at 4:36 PM, Keith Wiley <kw...@keithwiley.com> wrote:
> Thanks for the followup.
>
>
> ________________________________________________________________________________
> Keith Wiley kwiley@keithwiley.com keithwiley.com
> music.keithwiley.com
>
> "I used to be with it, but then they changed what it was. Now, what I'm
> with
> isn't it, and what's it seems weird and scary to me."
> -- Abe (Grandpa) Simpson
>
> ________________________________________________________________________________
>
>
Re: LIKE Statement
Posted by Keith Wiley <kw...@keithwiley.com>.
Thanks for the followup.
________________________________________________________________________________
Keith Wiley kwiley@keithwiley.com keithwiley.com music.keithwiley.com
"I used to be with it, but then they changed what it was. Now, what I'm with
isn't it, and what's it seems weird and scary to me."
-- Abe (Grandpa) Simpson
________________________________________________________________________________
Re: LIKE Statement
Posted by Edward Capriolo <ed...@gmail.com>.
By saying "OTHER DATABASES" you are implying that all other databases
agree on implementation.
I could not find the SQL official spec but anecdotally it seems this
is not the case.
http://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive
With hive you have like and rlike to chose from so pick your poison :)
hive> describe function extended like;
OK
like(str, pattern) - Checks if str matches pattern
Example:
> SELECT a.* FROM srcpart a WHERE a.hr like '%2' LIMIT 1;
27 val_27 2008-04-08 12
Time taken: 0.222 seconds
hive> describe function extended rlike;
OK
str rlike regexp - Returns true if str matches regexp and false otherwise
Synonyms: regexp
Example:
> SELECT 'fb' rlike '.*' FROM src LIMIT 1;
true
Time taken: 0.081 seconds
hive>
On 5/14/12, Keith Wiley <kw...@keithwiley.com> wrote:
> On Apr 4, 2012, at 06:40 , John Omernik wrote:
>
>> I think the like statement should be changed to be case-insensitive to
>> match it's function in other DBMS Thoughts?
>
> Out of curiosity, was there any activity on this issue? I see John's
> original post in the archives (~5wks ago) with no followups. I'm generally
> curious about HiveQL's similarities and differences w.r.t. other SQL
> environments, so questions like this are of interest to me as well.
>
> Cheers!
>
> ________________________________________________________________________________
> Keith Wiley kwiley@keithwiley.com keithwiley.com
> music.keithwiley.com
>
> "I do not feel obliged to believe that the same God who has endowed us with
> sense, reason, and intellect has intended us to forgo their use."
> -- Galileo Galilei
> ________________________________________________________________________________
>
>
Re: LIKE Statement
Posted by Keith Wiley <kw...@keithwiley.com>.
On Apr 4, 2012, at 06:40 , John Omernik wrote:
> I think the like statement should be changed to be case-insensitive to match it's function in other DBMS Thoughts?
Out of curiosity, was there any activity on this issue? I see John's original post in the archives (~5wks ago) with no followups. I'm generally curious about HiveQL's similarities and differences w.r.t. other SQL environments, so questions like this are of interest to me as well.
Cheers!
________________________________________________________________________________
Keith Wiley kwiley@keithwiley.com keithwiley.com music.keithwiley.com
"I do not feel obliged to believe that the same God who has endowed us with
sense, reason, and intellect has intended us to forgo their use."
-- Galileo Galilei
________________________________________________________________________________