Why unique constraint doesn't work?

sergey_hsergey_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)

Answers

  • Bryan_HBryan_H Vertica Employee Administrator

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

  • sergey_hsergey_h Vertica Customer

    @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_hsergey_h Vertica Customer

    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)
    
  • VValdarVValdar Vertica Employee Employee

    Just to confirm this bug lives also on 12.0.2 version.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file