[HIVE-17013] Delete request with a subquery based on select over a view

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Fix Version/s: 3.0.0
• Component/s: Transactions
• Tags: ACID
• Assignee: Eugene Koifman Assign to me
• Reporter: Frédéric ESCANDELL
• Created: 03/Jul/17 07:41
• Updated: 22/May/18 23:59
• Resolved: 17/Sep/17 05:35

Description

Hi,

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

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

FAILED: IllegalStateException Expected 'insert into table default.mydim select ROW_ID from default.mydim sort by ROW_ID' to be in sub-query or set operation.

{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'='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 updates_staging_table;

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

FAILED: IllegalStateException Expected 'insert into table default.mydim select ROW__ID from default.mydim sort by ROW__ID' to be in sub-query or set operation.