Null values in unique index not equal in merge statement

Database
Fan

Null values in unique index not equal in merge statement

Hello all!

I have table with DDL:

CREATE SET TABLE aidar.merge_1 ,FALLBACK ,
DUAL BEFORE JOURNAL,
NO AFTER JOURNAL,
WITH JOURNAL TABLE = aidar.bcd ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID DECIMAL(38,10) NOT NULL,
ID_2 DECIMAL(38,10) NOT NULL,
ID_3 DECIMAL(38,10),
S_VARCHAR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( ID ,ID_2 ,ID_3 );


It has 2 rows:

1  1  1  'aa'

1  1  null  'aa'

I try to run merge statement:

MERGE INTO  aidar.MERGE_1  t USING
values (1,1,null,'aaa') as b(key_1,key_2,key_3,col_1)
ON
ID = b.key_1 and ID_2 = b.key_2 and ID_3 = b.key_3
WHEN MATCHED THEN UPDATE
SET "S_VARCHAR" = b.col_1
WHEN NOT MATCHED THEN INSERT
(
"ID","ID_2","ID_3","S_VARCHAR"
) VALUES
(b.key_1, b.key_2, b.key_3, b.col_1)

Teradata show me error:

Error: [Teradata Database] [TeraJDBC 13.00.00.07] [Error 2801] [SQLState 23000] Duplicate unique prime key error in aidar.MERGE_1.

Can anybody suggest me how to solve this error please?

Thanks


4 REPLIES
Enthusiast

Re: Null values in unique index not equal in merge statement

Hi, 

for WHEN MATCHED condition the second row in the table is matched and will be updated without any issue.

the first row will go to WHEN NOT MATCHED part, so it will try to insert values (1,1,null,'aaa'),

as you have defined a UPI on ID, ID_2, and ID_3 and there is already a row with the PI values 1,1,null, so it returns the error of Duplicate UPI key error. 

Khurram
Fan

Re: Null values in unique index not equal in merge statement

Do you mean that merge try to execute on each row?

I just want to update the second row by merge statement. Can you tell me please how I can do it?

Fan

Re: Null values in unique index not equal in merge statement

I tried with same empty table.

I inserted 1,1,1,'a' and 1,2,2,'a'. Then I ran MERGE statement:

MERGE INTO  aidar.MERGE_1_test  t USING values
(1,2,2,'aa') as b(key_1,key_2,key_3,col_1)
ON
ID = b.key_1 and ID_2 = b.key_2 and ID_3 = b.key_3
WHEN MATCHED THEN UPDATE
SET "S_VARCHAR" = b.col_1
WHEN NOT MATCHED THEN INSERT
(
"ID","ID_2","ID_3","S_VARCHAR")
VALUES (b.key_1, b.key_2, b.key_3, b.col_1);

All works fine. Then I try to insert new row with merge statement:

MERGE INTO  aidar.MERGE_1_test  t USING values
(2,2,null,'b') as b(key_1,key_2,key_3,col_1)
ON
ID = b.key_1 and ID_2 = b.key_2 and ID_3 = b.key_3
WHEN MATCHED THEN UPDATE
SET "S_VARCHAR" = b.col_1
WHEN NOT MATCHED THEN INSERT
(
"ID","ID_2","ID_3","S_VARCHAR"
) VALUES (b.key_1, b.key_2, b.key_3, b.col_1);

2,2,null,'b' row inserted fine.

But then I tried to update last inserted row (2,2,null,'b') to (2,2,null,'b') I have duplicate error again.

So I think there is some problems with checking NULL in ON clause.

Fan

Re: Null values in unique index not equal in merge statement

Ok. I found that in SQL queries null not equal to null always. But in unique indexes they are equal.

So I think MERGE statement tries to insert in both cases.

Thank you Khurram!