The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Why unique constraint doesn't work?

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:
Just to confirm this bug lives also on 12.0.2 version.