[jira] [Created] (HIVE-19295) Some multiple inserts do work on MR engine

["Oleksiy Sayankin (JIRA)" <ji...@apache.org>]

Oleksiy Sayankin created HIVE-19295:
---------------------------------------

             Summary: Some multiple inserts do work on MR engine
                 Key: HIVE-19295
                 URL: https://issues.apache.org/jira/browse/HIVE-19295
             Project: Hive
          Issue Type: Bug
            Reporter: Oleksiy Sayankin
            Assignee: Oleksiy Sayankin


*General Info*

Hive version : 2.3.3

{code}
commit 3f7dde31aed44b5440563d3f9d8a8887beccf0be
Author: Daniel Dai <da...@hortonworks.com>
Date:   Wed Mar 28 16:46:29 2018 -0700

    Preparing for 2.3.3 release

{code}

Hadoop version: 2.7.2.

Engine

{code}
hive> set hive.execution.engine;
hive.execution.engine=mr
{code}

*STEP 1. Create test data*

{code}
DROP TABLE IF EXISTS customer_target;
DROP TABLE IF EXISTS customer_source;
{code}

{code}
CREATE TABLE customer_target (id STRING, first_name STRING, last_name STRING, age INT); 
{code}

{code}
insert into customer_target values ('001', 'John', 'Smith', 45), ('002', 'Michael', 'Watson', 27), ('003', 'Den', 'Brown', 33);
SELECT id, first_name, last_name, age  FROM customer_target;
{code}

{code}
+------+-------------+------------+------+
|  id  | first_name  | last_name  | age  |
+------+-------------+------------+------+
| 002  | Michael     | Watson     | 27   |
| 001  | John        | Smith      | 45   |
| 003  | Den         | Brown      | 33   |
+------+-------------+------------+------+
{code}



{code}
CREATE TABLE customer_source (id STRING, first_name STRING, last_name STRING, age INT);

insert into customer_source values ('001', 'Dorothi', 'Hogward', 77), ('007', 'Alex', 'Bowee', 1), ('088', 'Robert', 'Dowson', 25);
SELECT id, first_name, last_name, age  FROM customer_source;
{code}

{code}
+------+-------------+------------+------+
|  id  | first_name  | last_name  | age  |
+------+-------------+------------+------+
| 088  | Robert      | Dowson     | 25   |
| 001  | Dorothi     | Hogward    | 77   |
| 007  | Alex        | Bowee      | 1    |
+------+-------------+------------+------+
{code}

*STEP 2. Do multiple insert*

{code}
FROM
  `default`.`customer_target` `trg`
  JOIN
  `default`.`customer_source` `src`
  ON `src`.`id` = `trg`.`id`
INSERT INTO `default`.`customer_target`    -- update clause
 select  `trg`.`id`, `src`.`first_name`, `src`.`last_name`, `trg`.`age`
   WHERE `src`.`id` = `trg`.`id`
 sort by `trg`.id 
INSERT INTO `default`.`customer_target`    -- insert clause
  select `src`.`id`, `src`.`first_name`, `src`.`last_name`, `src`.`age`
   WHERE `trg`.`id` IS NULL   
{code}


*ACTUAL RESULT*

{code}
FAILED: SemanticException [Error 10087]: The same output cannot be present multiple times:  customer_target
{code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)