Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: unknown (nike.apache.org: error in processing during lookup of
 Guy.Doulberg@conduit.com)
From: Guy Doulberg <Guy.Doulberg@conduit.com>
To: Edward Capriolo <edlinuxguru@gmail.com>, "user@hive.apache.org"
	<user@hive.apache.org>
Date: Thu, 17 Mar 2011 07:21:03 -0700
Subject: RE: We had this wierd behvior
Thread-Topic: We had this wierd behvior
Thread-Index: AcvkrKne+F50Ico/ScyUzbyM49XAywAAdkMw
Message-ID: 
 <6AB151AD074C18409E0CA3CD8D4312302BAC1B0942@EXVMBX017-12.exch017.msoutlookonline.net>
References: 
 <6AB151AD074C18409E0CA3CD8D4312302BAC1B0274@EXVMBX017-12.exch017.msoutlookonline.net>
	<AANLkTinAwGacUx+yGb2tmUT2QfaFmQcC_wvWQFgj_0bM@mail.gmail.com>
	<6AB151AD074C18409E0CA3CD8D4312302BAC1B0862@EXVMBX017-12.exch017.msoutlookonline.net>
 <AANLkTinc9pxKFDtRzvEEkOrvnyFYMUzzyzyce65nPzs7@mail.gmail.com>
In-Reply-To: <AANLkTinc9pxKFDtRzvEEkOrvnyFYMUzzyzyce65nPzs7@mail.gmail.com>
Accept-Language: he-IL, en-US
Content-Language: en-US
acceptlanguage: he-IL, en-US
Content-Type: text/plain; charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Okay thanks

-----Original Message-----
From: Edward Capriolo [mailto:edlinuxguru@gmail.com]=20
Sent: Thursday, March 17, 2011 4:08 PM
To: user@hive.apache.org
Cc: Guy Doulberg
Subject: Re: We had this wierd behvior

2011/3/17 Guy Doulberg <Guy.Doulberg@conduit.com>:
> Strings
>
> I actually simplified the scenario so I could the question,
> Our partitions are actually string of dates with hour
>
> So the query was actually
> Partition >=3D '20110301_20' and Partition <=3D '2011030223'
>
> Still using a single quote wouldn't be advised?
>
>
> Thanks,
>
> -----Original Message-----
> From: Edward Capriolo [mailto:edlinuxguru@gmail.com]
> Sent: Thursday, March 17, 2011 2:59 PM
> To: user@hive.apache.org
> Subject: Re: We had this wierd behvior
>
> On Thursday, March 17, 2011, Guy Doulberg <Guy.Doulberg@conduit.com> wrot=
e:
>> Hey guys,=A0I have a hive partitioned table.=A0First I ran a query that =
look like this:Select count(*) From tableWhere field like '%bla%' and (part=
ition>'10' and partition < '20')=A0For this query I gotSome records let's s=
ay 640=A0When I ran this query Select count(*) From tableWhere field like '=
%bla%' and (partition>'5' and partition < '20')=A0I got none=85..=A0Assume =
that I have partitions 5-20.=A0What am I missing here?=A0Thanks gut
>
> Is your table partitioned by int or string?
>
> '5' is not less then '20' when you are treating them as strings. Don't
> put single quotes around numbers
>

If you put single quotes around numbers it is converted to a string literal=
.

 '5' !=3D 5

How did you define your partitions?

CREATE TABLE ... PARTITIONED BY ( int date)
OR
CREATE TABLE ... PARTITIONED BY ( string date)

If your did the former the sorting / comparison rules are numerical.
If you did the latter the sorting / comparison rules are lexicographical.

The best way to explain this is just to follow one rule.
Do not put single quotes around numbers and this will work as expected.