Why unique constraint doesn't work?

sergey_h

Hello All,

Why unique constraint doesn't work when i use hint_ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ ?

OLAP=> CREATE TABLE public.kss
OLAP-> (
OLAP(>     id int,
OLAP(>     id2 int,
OLAP(>     ts timestamp,
OLAP(>     CONSTRAINT C_UNIQUE UNIQUE (id, id2) ENABLED
OLAP(> )
OLAP->  ORDER BY id,
OLAP->           ts
OLAP-> SEGMENTED BY hash(id) ALL NODES;
CREATE TABLE

If i use query without hint - constraint work

OLAP=> insert into public.kss with a as (select 2 as id, 2 as id2, current_timestamp as ts union all select 2, 2, current_timestamp) select * from a;
ERROR 6745:  Duplicate key values: 'id=2,id2=2' -- violates constraint 'public.kss.C_UNIQUE'

If i use hint - constraint work doesn't work

OLAP=> insert into public.kss with /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ a as (select 2 as id, 2 as id2, current_timestamp as ts union all select 2, 2, current_timestamp) select * from a;
 OUTPUT
--------
      2

OLAP=> select * from public.kss;
 id | id2 |             ts
----+-----+----------------------------
  2 |   2 | 2022-11-21 13:26:02.791231
  2 |   2 | 2022-11-21 13:26:02.791231
(2 rows)

Why is this happening?

OLAP=> SELECT version();
               version
--------------------------------------
 Vertica Analytic Database v11.1.1-11
(1 row)

Bryan_H

I'll send your example to engineering to figure out what's going on.

sergey_h

@Bryan_H said:
I'll send your example to engineering to figure out what's going on.

Thank you, we will wait for a reply

sergey_h

The behavior on the other version is slightly different, but also not correct:

OLAP=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v11.0.2-2
(1 row)

OLAP=> insert into public.kss with a as (select 2 as id, 2 as id2, current_timestamp as ts union all select 2, 2, current_timestamp) select * from a;
ERROR 6745:  Duplicate key values: 'id=2,id2=2' -- violates constraint 'public.kss.C_UNIQUE'


OLAP=> select * from public.kss;
 id | id2 | ts
----+-----+----
(0 rows)


OLAP=> insert into public.kss with /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ a as (select 4 as id, 4 as id2, current_timestamp as ts union all select 4, 4, current_timestamp) select * from a;
ERROR 6745:  Duplicate key values: 'id=4,id2=4' -- violates constraint 'public.kss.C_UNIQUE'
DETAIL:  Note: there may be additional errors


OLAP=> select * from public.kss;
 id | id2 |             ts
----+-----+----------------------------
  4 |   4 | 2022-11-22 10:17:01.244367
  4 |   4 | 2022-11-22 10:17:01.244367
(2 rows)

OLAP=> commit;
COMMIT

OLAP=> select * from public.kss;
 id | id2 |             ts
----+-----+----------------------------
  4 |   4 | 2022-11-22 10:17:01.244367
  4 |   4 | 2022-11-22 10:17:01.244367
(2 rows)

VValdar

Just to confirm this bug lives also on 12.0.2 version.