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)

Answers

  • Bryan_HBryan_H Administrator

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

  • @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

  • 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 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.