After JOIN statements run result something wrong when using Docker on M1 MacBook

Please share the join statement and create tables DDL repo.

SELECT m.id, b.id FROM schema.member m INNER JOIN schema.board b
Produces Syntax error.

Please consider something like the following example:

create table member (id    int);
create table board (id    int);

\set DEMO_ROWS 10
INSERT INTO member
with myrows as (select
row_number() over() as id
from ( select 1 from ( select now() as se union all
select now() + :DEMO_ROWS - 1 as se) a timeseries ts as '1 day' over (order by se)) b)
select id
from myrows
order by id;
COMMIT;

SELECT COPY_TABLE ('public.member', 'public.board');

SELECT m.id, b.id
FROM member m
INNER JOIN board b
ON m.id=b.id
ORDER BY 1;

-

@mosheg

First of all, thank you for the detailed example.

However, the DDL you wrote above is working normally, but my DDL is not working properly yet.

I am attaching a sample DDL to you.

drop table member_test;
drop table board_test;

create table member_test(
 member_id int not null primary key
);


create table board_test(
 board_id varchar(32) not null primary key,
 member_id int
);

START TRANSACTION;
INSERT INTO member_test(member_id) VALUES(1);
INSERT INTO member_test(member_id) VALUES(2);
INSERT INTO member_test(member_id) VALUES(3);
COMMIT;

START TRANSACTION;
INSERT INTO board_test(board_id, member_id) VALUES ('W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8', 1);
INSERT INTO board_test(board_id, member_id) VALUES ('nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5', 2);
INSERT INTO board_test(board_id, member_id)VALUES ('8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez', 3);
COMMIT;

SELECT *
FROM member_test m
INNER JOIN board_test b
ON m.member_id=b.member_id
ORDER BY 1;

The following is the result of performing the sample DDL as shown above.

First Run Query

dbadmin@06421830995b()=> SELECT *
epdb_dev-> FROM member_test m
epdb_dev-> INNER JOIN board_test b
epdb_dev-> ON m.member_id=b.member_id;
 member_id |             board_id             | member_id
-----------+----------------------------------+-----------
         1 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
         2 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
         3 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
(3 rows)

Second Run Query

dbadmin@06421830995b(*)=> SELECT *
epdb_dev-> FROM member_test m
epdb_dev-> INNER JOIN board_test b
epdb_dev-> ON m.member_id=b.member_id;
ERROR 3149:  Duplicate primary/unique key detected in join [(public.board_test x public.member_test) using board_test_super and member_test_super (PATH ID: 1)]; value [member_id=1]

Query Explain

 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT *
 FROM member_test m
 INNER JOIN board_test b
 ON m.member_id=b.member_id
 ;

 Access Path:
 +-JOIN HASH [Cost: 135, Rows: 3] (PATH ID: 1)
 |  Join Cond: (m.member_id = b.member_id)
 |  Materialize at Output: b.board_id
 | +-- Outer -> STORAGE ACCESS for b [Cost: 49, Rows: 3] (PATH ID: 2)
 | |      Projection: public.board_test_super
 | |      Materialize: b.member_id
 | |      Filter: (b.member_id IS NOT NULL)
 | |      Runtime Filter: (SIP1(HashJoin): b.member_id)
 | +-- Inner -> STORAGE ACCESS for m [Cost: 49, Rows: 3] (PATH ID: 3)
 | |      Projection: public.member_test_super
 | |      Materialize: m.member_id

In this case, the above error appears, and the case where the actual garbage value is generated can be reproduced by using the DDL as below.

drop table member;
drop table board;

create table member(
 member_id         int not null
);


create table board(
 board_id         varchar(32) not null,
 member_id int
);


START TRANSACTION;
INSERT INTO member(member_id) VALUES(1);
INSERT INTO member(member_id) VALUES(2);
INSERT INTO member(member_id) VALUES(3);
COMMIT;

START TRANSACTION;
INSERT INTO board(board_id, member_id) VALUES ('W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8', 1);
INSERT INTO board(board_id, member_id) VALUES ('nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5', 2);
INSERT INTO board(board_id, member_id)VALUES ('8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez', 3);
COMMIT;


SELECT *
FROM member m
INNER JOIN board b
ON m.member_id=b.member_id;
ORDER BY 1;

Result

dbadmin@354ddc2c3d8a()=> SELECT *
epdb_dev-> FROM member m
epdb_dev-> INNER JOIN board b
epdb_dev-> ON m.member_id=b.member_id;
      member_id      |             board_id             | member_id
---------------------+----------------------------------+-----------
                   1 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
                   2 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
                   3 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
 7883960210924265844 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
 3906083473050778213 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
 4121135822717137972 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
 7953186949000202802 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
                   2 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
                   3 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
 7883960210924265844 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
 3906083473050778213 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
 4121135822717137972 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
 7953186949000202802 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
 8098984466286210401 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
                   3 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
 7883960210924265844 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
 3906083473050778213 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
 4121135822717137972 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
 7953186949000202802 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
 8098984466286210401 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
 7953205644807332452 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
(21 rows)

Could you tell me how to solve this problem?

Hi @liquidbear ,

This is a wild guess, can you try your query this way?

SELECT *
FROM member m
INNER JOIN board b /*+ EARLY_MATERIALIZATION */
ON m.member_id=b.member_id;

Sorry I don't have a M1 macbook to test by myself.

@VValdar

Thank you for your advice.

However, we confirmed that the same problem occurred.

Result

epdb_dev-> FROM member m
epdb_dev-> INNER JOIN board b /*+ EARLY_MATERIALIZATION */
epdb_dev-> ON m.member_id=b.member_id;
 member_id |             board_id             | member_id
-----------+----------------------------------+-----------
         1 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
         2 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
         3 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
(3 rows)


dbadmin@0dd25399f0a7(*)=> SELECT *
epdb_dev-> FROM member m
epdb_dev-> INNER JOIN board b /*+ EARLY_MATERIALIZATION */
epdb_dev-> ON m.member_id=b.member_id;
 member_id |             board_id             | member_id
-----------+----------------------------------+-----------
         1 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
         2 | W5pJt6mH9z5q2HSlcm1N9W4y43jzgoX8 |         1
         2 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
         3 | nWc2nb4r2JOukPs4aIwtpmIaH7Q6rpr5 |         2
         3 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
         0 | 8sEvAOzV4sdqguWpAzkkEwGyxrDpO6Ez |         3
(6 rows)

On v12.0.2-0 I've got the error in the 2nd query run, only when some of the projections from previous run were not dropped:
ERROR 3149: Duplicate primary/unique key detected in join [(public.board_test x public.member_test) using board_test_super and member_test_super (PATH ID: 1)]; value [member_id=1]
Please add CASCADE to all "drop" statements.
And before running the test again please verify that all your tables and all projections are dropped.