The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Behavior of ERROR 3149 for duplicate values

I'm trying to understand the behavior of Error 3149 for duplicate values on primary keys when performing joins. In the tests below, the inner table contains a duplicate value and is joined to another table with various column definitions. What I observed is that when the inner table is joined to the outer table on a primary key, no error is thrown. Is this expected behavior (with regards to errors being thrown)?

Inner table
CREATE TABLE public.table1 (      id int PRIMARY KEY,      val char(1)  );    INSERT INTO public.table1 VALUES (1, 'a');  INSERT INTO public.table1 VALUES (1, 'b');    SELECT * FROM public.table1;   id | val  ----+-----    1 | a    1 | b  (2 rows)    SELECT ANALYZE_CONSTRAINTS('public.table1');   Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values  -------------+------------+--------------+-----------------+-----------------+---------------   public      | table1     | id           | C_PRIMARY       | PRIMARY         | ('1')  (1 row)
Test 1

When table1 is joined to a primary key column in table2, no error is thrown.
CREATE TABLE public.table2 (      id int PRIMARY KEY  );    INSERT INTO public.table2 VALUES (1);    SELECT a.val   FROM   public.table1 a          JOIN public.table2 b            ON a.id = b.id;     val  -----   a   b  (2 rows)
Test 2

When table1 is joined to a non-key column in table2, the error is thrown.
DROP TABLE public.table2 CASCADE;    CREATE TABLE public.table2 (      id int  );    INSERT INTO public.table2 VALUES (1);    SELECT a.val   FROM   public.table1 a          JOIN public.table2 b            ON a.id = b.id;    ERROR 3149:  Duplicate primary/unique key detected in join [(public.table2 x public.table1) using table2_b0 and table1_b0 (PATH ID: 1)]; value [1]
Test 3

When table1 is joined to a primary key column containing the same duplicates in table2, the error is thrown.
DROP TABLE public.table2 CASCADE;    CREATE TABLE public.table2 (      id int PRIMARY KEY  );    INSERT INTO public.table2 VALUES (1);  INSERT INTO public.table2 VALUES (1);    SELECT a.val   FROM   public.table1 a          JOIN public.table2 b            ON a.id = b.id;    ERROR 3149:  Duplicate primary/unique key detected in join [(public.table1 x public.table2) using table1_b0 and table2_b0 (PATH ID: 1)]; value [1]

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.