Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of john@omernik.com designates
 209.85.220.176 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CAJAzPKRmSBDsGj8BtvmSPzfZXg6-MvKxutzvisM9RMA_Yy1xaA@mail.gmail.com>
References: 
 <CAKOFcwpVxFjz2-kKRfirkxURAoOZ+X7QM2JL+LCmVFycHdxJrg@mail.gmail.com>
 <CAJAzPKRmSBDsGj8BtvmSPzfZXg6-MvKxutzvisM9RMA_Yy1xaA@mail.gmail.com>
From: John Omernik <john@omernik.com>
Date: Sat, 26 Jan 2013 09:27:30 -0600
Message-ID: 
 <CAKOFcwpXc653aHSeaLqpP5wKMmVK+u3cfSNVNXv+5hyth93ERQ@mail.gmail.com>
Subject: Re: Is this a known Bug: Multi Inserts from partitioned source ignore
 Where Clauses
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=f46d043085c8dd495c04d432af47

--f46d043085c8dd495c04d432af47
Content-Type: text/plain; charset=ISO-8859-1

I am not a code expert, this looks very much like the bug I posted, but my
bug is not using INSERT OVERWRITE (just INSERT INTO) and I am not doing any
group by (probably not an issue)

Just to be clear, this is probably the same issue as mine, but if someone
with more knowledge of the underlying structures were to see the OVERWRITE
vs INTO they may see something different.

On Sat, Jan 26, 2013 at 9:20 AM, Philip Tromans
<philip.j.tromans@gmail.com>wrote:

> This is a known (recently fixed) bug:
>
> https://issues.apache.org/jira/browse/HIVE-3699
>
> Phil.
>
>
> On 26 January 2013 15:17, John Omernik <john@omernik.com> wrote:
>
>> I ran into an interesting bug. Basically, if your FROM() source is
>> a partitioned table and you use a where clause that prunes, all of the
>> INSERT HERE SELECT * WHERE x=y ignores each specified where clause.  This
>> does not occur if the source partition is not specified, but if the source
>> as where partition = 'x' then the where on each individual insert is
>> ignored...
>>
>> I've included some files here
>>
>> testdata.tsv - Tab delimited data to prove the issue
>> create_tables.hive - Creates a database and tables as well as loads the
>> data from the TSV
>>
>> Test Cases:
>> I created these test case files in a way that there are three types of
>> insert in each case: 1. Load all data from initial statement, 2. Load
>> partial data (use a limiting clause such as where day >= '2013-01-05', and
>> 3 Load NO data from the initial statement (where 1 = 0)
>>
>> These tests are all run on hive 0.9
>>
>> multi-flat-flat.hive - The source table and the dest tables are not
>> partitioned, the where clauses work as expected:
>>
>> 19 Rows loaded to multi_bug_flat
>> 0 Rows loaded to multi_bug_flat3
>> 15 Rows loaded to multi_bug_flat2
>>
>> multi-part-part.hive - The source table and the dest tables are
>> partitioned. The where clauses are not honored.
>>
>> 9 Rows loaded to multi_bug_part3
>> 9 Rows loaded to multi_bug_part2
>> 9 Rows loaded to multi_bug_part
>>
>> multi-flat-part.hive - The source table is flat, the dest table is
>> partitioned - The where clauses work as expected:
>>
>> 0 Rows loaded to multi_bug_part3
>> 15 Rows loaded to multi_bug_part2
>> 19 Rows loaded to multi_bug_part
>>
>> multi-part-flat.hive - The source table is partitioned, the dest table is
>> flat - The where clauses are not honored:
>>
>> 9 Rows loaded to multi_bug_flat
>> 9 Rows loaded to multi_bug_flat3
>> 9 Rows loaded to multi_bug_flat2
>>
>> multi-part-specified.hive - The source and dest are partitioned, but
>> there is no partition pruning statement in the from ()  this works as
>> expected
>>
>> 0 Rows loaded to multi_bug_part3
>> 15 Rows loaded to multi_bug_part2
>> 19 Rows loaded to multi_bug_part
>>
>>
>> Thoughts?
>>
>
>

--f46d043085c8dd495c04d432af47
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I am not a code expert, this looks very much like the bug I posted, but my =
bug is not using INSERT OVERWRITE (just INSERT INTO) and I am not doing any=
 group by (probably not an issue)<div><br></div><div>Just to be clear, this=
 is probably the same issue as mine, but if someone with more knowledge of =
the underlying structures were to see the OVERWRITE vs INTO they may see so=
mething different.=A0<br>

<br><div class=3D"gmail_quote">On Sat, Jan 26, 2013 at 9:20 AM, Philip Trom=
ans <span dir=3D"ltr">&lt;<a href=3D"mailto:philip.j.tromans@gmail.com" tar=
get=3D"_blank">philip.j.tromans@gmail.com</a>&gt;</span> wrote:<br><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc s=
olid;padding-left:1ex">

<div dir=3D"ltr">This is a known (recently fixed) bug:<div><br></div><div><=
a href=3D"https://issues.apache.org/jira/browse/HIVE-3699" target=3D"_blank=
">https://issues.apache.org/jira/browse/HIVE-3699</a><br></div><div><br></d=
iv>

<div>Phil.</div>
</div><div class=3D"HOEnZb"><div class=3D"h5"><div class=3D"gmail_extra"><b=
r><br><div class=3D"gmail_quote">On 26 January 2013 15:17, John Omernik <sp=
an dir=3D"ltr">&lt;<a href=3D"mailto:john@omernik.com" target=3D"_blank">jo=
hn@omernik.com</a>&gt;</span> wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
I ran into an interesting bug. Basically, if your FROM() source is a=A0part=
itioned=A0table and you use a where clause that prunes, all of the INSERT H=
ERE SELECT * WHERE x=3Dy ignores each specified where clause. =A0This does =
not occur if the source partition is not specified, but if the source as wh=
ere partition =3D &#39;x&#39; then the where on each individual insert is i=
gnored...=A0<div>


<br></div><div>I&#39;ve included some files here</div><div><br></div><div>t=
estdata.tsv - Tab delimited data to prove the issue</div><div>create_tables=
.hive - Creates a database and tables as well as loads the data from the TS=
V</div>


<div><br></div><div>Test Cases:</div><div>I created these test case files i=
n a way that there are three types of insert in each case: 1. Load all data=
 from initial statement, 2. Load partial data (use a limiting clause such a=
s where day &gt;=3D &#39;2013-01-05&#39;, and 3 Load NO data from the initi=
al statement (where 1 =3D 0)</div>


<div><br></div><div>These tests are all run on hive 0.9</div><div><br></div=
><div>multi-flat-flat.hive - The source table and the dest tables are not p=
artitioned, the where clauses work as expected:</div><div><div><br></div>


<div>19 Rows loaded to multi_bug_flat</div><div>0 Rows loaded to multi_bug_=
flat3</div><div>15 Rows loaded to multi_bug_flat2</div></div><div><br></div=
><div>multi-part-part.hive - The source table and the dest tables are parti=
tioned. The where clauses are not honored.=A0</div>


<div><div><br></div><div>9 Rows loaded to multi_bug_part3</div><div>9 Rows =
loaded to multi_bug_part2</div><div>9 Rows loaded to multi_bug_part</div></=
div><div><br></div><div>multi-flat-part.hive - The source table is flat, th=
e dest table is partitioned - The where clauses work as expected:</div>


<div><div><br></div><div>0 Rows loaded to multi_bug_part3</div><div>15 Rows=
 loaded to multi_bug_part2</div><div>19 Rows loaded to multi_bug_part</div>=
</div><div><br></div><div>multi-part-flat.hive - The source table is partit=
ioned, the dest table is flat - The where clauses are not honored:</div>


<div><div><br></div><div>9 Rows loaded to multi_bug_flat</div><div>9 Rows l=
oaded to multi_bug_flat3</div><div>9 Rows loaded to multi_bug_flat2</div></=
div><div><br></div><div>multi-part-specified.hive - The source and dest are=
=A0partitioned, but there is no partition pruning statement in the from () =
=A0this works as expected</div>


<div><div><br></div><div>0 Rows loaded to multi_bug_part3</div><div>15 Rows=
 loaded to multi_bug_part2</div><div>19 Rows loaded to multi_bug_part</div>=
</div><div><br></div><div><br></div><div>Thoughts?</div>
</blockquote></div><br></div>
</div></div></blockquote></div><br></div>

--f46d043085c8dd495c04d432af47--