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

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     [EMAIL PROTECTED]     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
________________________________________________________________________________

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 <[EMAIL PROTECTED]> 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     [EMAIL PROTECTED]     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
> ________________________________________________________________________________
>
>

Thanks for the followup.

________________________________________________________________________________
Keith Wiley     [EMAIL PROTECTED]     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
________________________________________________________________________________

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 <[EMAIL PROTECTED]> wrote:

> Thanks for the followup.
>
>
> ________________________________________________________________________________
> Keith Wiley     [EMAIL PROTECTED]     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
>
> ________________________________________________________________________________
>
>