Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Date: Mon, 3 Jul 2017 07:42:00 +0000 (UTC)
From: =?utf-8?Q?Fr=C3=A9d=C3=A9ric_ESCANDELL_=28JIRA=29?= <jira@apache.org>
To: dev@hive.apache.org
Message-ID: <JIRA.13084162.1499067704000.157381.1499067720166@Atlassian.JIRA>
In-Reply-To: <JIRA.13084162.1499067704000@Atlassian.JIRA>
References: <JIRA.13084162.1499067704000@Atlassian.JIRA> <JIRA.13084162.1499067704761@jira-lw-us.apache.org>
Subject: [jira] [Created] (HIVE-17013) Delete request with a subquery based
 on select over a view
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
archived-at: Mon, 03 Jul 2017 07:42:12 -0000

Fr=C3=A9d=C3=A9ric ESCANDELL created HIVE-17013:
-----------------------------------------

             Summary: Delete request with a subquery based on select over a=
 view
                 Key: HIVE-17013
                 URL: https://issues.apache.org/jira/browse/HIVE-17013
             Project: Hive
          Issue Type: Bug
            Reporter: Fr=C3=A9d=C3=A9ric ESCANDELL
            Priority: Blocker


Hi,=20

I based my DDL on this exemple https://fr.hortonworks.com/tutorial/using-hi=
ve-acid-transactions-to-insert-update-and-delete-data/.

In a delete request, the use of a view in a subquery throw an exception :=
=20

FAILED: IllegalStateException Expected 'insert into table default.mydim sel=
ect ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set o=
peration.
{code}

{code:sql}
drop table if exists mydim;
create table mydim (key int, name string, zip string, is_current boolean)
clustered by(key) into 3 buckets
stored as orc tblproperties ('transactional'=3D'true');

insert into mydim values
  (1, 'bob',   '95136', true),
  (2, 'joe',   '70068', true),
  (3, 'steve', '22150', true);

drop table if exists updates_staging_table;
create table updates_staging_table (key int, newzip string);
insert into updates_staging_table values (1, 87102), (3, 45220);
drop view if exists updates_staging_view;
create view updates_staging_view (key, newzip) as select key, newzip from u=
pdates_staging_table;

delete from mydim
where mydim.key in (select key from updates_staging_view);

FAILED: IllegalStateException Expected 'insert into table default.mydim sel=
ect ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set o=
peration.
{code}


--
This message was sent by Atlassian JIRA
(v6.4.14#64029)