Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-17013

Delete request with a subquery based on select over a view

Log workAgile BoardRank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersCreate sub-taskConvert to sub-taskMoveLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Blocker
    • Resolution: Fixed
    • None
    • 3.0.0
    • Transactions
    • None
    • ACID

    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.
      

      Attachments

        1. HIVE-17013.01.patch
          2 kB
          Eugene Koifman
        2. HIVE-17013.01.patch
          11 kB
          Eugene Koifman
        3. acid_view_bug.q
          0.7 kB
          Matt McCline

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            ekoifman Eugene Koifman Assign to me
            fescandell Frédéric ESCANDELL
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment