Why unique constraint doesn't work?
sergey_h
Vertica Customer ✭
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)
0
Answers
I'll send your example to engineering to figure out what's going on.
Thank you, we will wait for a reply
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)Just to confirm this bug lives also on 12.0.2 version.