Behavior of ERROR 3149 for duplicate values

norbert_krupa

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]