hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aron Hamvas (Jira)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-22560) ACID MERGE fails if NOT NULL constraint is used for column and PDD is enabled
Date Thu, 28 Nov 2019 08:56:00 GMT

     [ https://issues.apache.org/jira/browse/HIVE-22560?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Aron Hamvas updated HIVE-22560:
-------------------------------
    Description: 
If hive.optimize.ppd=true is set, ACID MERGE can fail if the column used in matching has NOT
NULL constraint.

To reproduce the issue:
{code:sql}
CREATE TABLE SIMPLECDCMASTER (CLI_NUM VARCHAR(13) NOT NULL , FIELD1 VARCHAR(100));
CREATE TABLE SIMPLECDCDELTA (CLI_NUM VARCHAR(13), FIELD1 VARCHAR(100));

insert into simplecdcdelta values (123, 'abc');

merge into simplecdcmaster as m using simplecdcdelta as d on m.cli_num = d.cli_num
when matched then update set field1=d.field1
when not matched then insert values (d.cli_num, d.field1);
{code}

The NOT NULL constraint is then enforced in the Filter operator processing the output of the
right outer join, where, of course, the not matched records have nulls in all columns.

  was:
If hive.optimize.ppd=true is set, ACID MERGE can fail if the column used in matching has NOT
NULL constraint.

To reproduce the issue:
{code:sql}
CREATE TABLE SIMPLECDCMASTER (CLI_NUM VARCHAR(13) NOT NULL , FIELD1 VARCHAR(100));
CREATE TABLE SIMPLECDCDELTA (CLI_NUM VARCHAR(13), FIELD1 VARCHAR(100));

insert into simplecdcdelta values (123, 'abc');

merge into simplecdcmaster as m using simplecdcdelta as d on m.cli_num = d.cli_num
when matched then update set field1=d.field1
when not matched then insert values (d.cli_num, d.field1);
{code}

The NOT NULL constrained is then enforced in the Filter operator processing the output of
the right outer join, where, of course, the not matched records have nulls in all columns.


> ACID MERGE fails if NOT NULL constraint is used for column and PDD is enabled
> -----------------------------------------------------------------------------
>
>                 Key: HIVE-22560
>                 URL: https://issues.apache.org/jira/browse/HIVE-22560
>             Project: Hive
>          Issue Type: Bug
>          Components: Query Planning
>    Affects Versions: 3.1.2
>            Reporter: Aron Hamvas
>            Priority: Major
>
> If hive.optimize.ppd=true is set, ACID MERGE can fail if the column used in matching
has NOT NULL constraint.
> To reproduce the issue:
> {code:sql}
> CREATE TABLE SIMPLECDCMASTER (CLI_NUM VARCHAR(13) NOT NULL , FIELD1 VARCHAR(100));
> CREATE TABLE SIMPLECDCDELTA (CLI_NUM VARCHAR(13), FIELD1 VARCHAR(100));
> insert into simplecdcdelta values (123, 'abc');
> merge into simplecdcmaster as m using simplecdcdelta as d on m.cli_num = d.cli_num
> when matched then update set field1=d.field1
> when not matched then insert values (d.cli_num, d.field1);
> {code}
> The NOT NULL constraint is then enforced in the Filter operator processing the output
of the right outer join, where, of course, the not matched records have nulls in all columns.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message