Skip to main content
Question

Why unique constraint doesn't work?

  • November 21, 2022
  • 4 replies
  • 6 views
sergey_h
Forum|alt.badge.img+1

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)

    4 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • November 21, 2022

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

    sergey_h
    Forum|alt.badge.img+1
    • sergey_h
    • Author
    • Participating Frequently
    • November 22, 2022

    @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
    Forum|alt.badge.img+1
    • sergey_h
    • Author
    • Participating Frequently
    • November 22, 2022

    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
    Forum|alt.badge.img+1
    • VValdar
    • Participating Frequently
    • November 22, 2022

    Just to confirm this bug lives also on 12.0.2 version.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement