Filter on Foreign Key with LATERAL JOIN brings yields strange results

Question

thanks for your time!

Basically, I'm trying to filter a NxM table using foreign keys, with 0,1 or N different tags. The problem is that LEFT LATERAL JOIN yields bizarre results. Please, don't mind the strange casting, I'm doing so because I'm using spring boot. Here is a fiddle showing a fake relationship: https://www.db-fiddle.com/f/6bDu33keWACHssLqznk88n/0

Schema (PostgreSQL v13)

CREATE TABLE posts (id int primary key);
CREATE TABLE tags (id int primary key);
CREATE TABLE post_tags (post_id int references posts(id),
                        tags_id int references tags(id),
                       primary key (post_id, tags_id));

INSERT INTO posts VALUES (1), (2), (3), (4);
INSERT INTO tags VALUES (8), (9);
INSERT INTO post_tags VALUES (1,8), (1,9), (2,8);

---

Query #1

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9' AS TEXT), ',') AS INT[])));

id	post_id	tags_id
1	1	9

---

Query #2

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id limit 1) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9' AS TEXT), ',') AS INT[])));

There are no results to be displayed.

---

Query #3

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9,8' AS TEXT), ',') AS INT[])));

id	post_id	tags_id
1	1	9
1	1	8
2	2	8

---

Query #4

select * from posts p
left join lateral (select * from post_tags pt where pt.post_id = p.id limit 1) pt on 1=1
where (1 is null or pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9,8' AS TEXT), ',') AS INT[])));

id	post_id	tags_id
1	1	8
2	2	8

---

View on DB Fiddle

If you notice, query #2 yields no results, although it should. I suspect the limit 1 is not allowing it to function properly. But if I remove it, I get duplicate results (as seen in query #3).

My question is, how can I filter on foreign keys and not having duplicate results?

EDIT ---

I expect the query to return at most 1 result per category that matches the where clause; Query #2 should return:

id	post_id	tags_id
1	1	9

Or in case multi tags are passed, it should return just like query #4 (both matches, post 1 and 2, but not duplicated posts (post id = 1)

Thanks

Answer 1

Obviously there is a problem with the where clause. It is filtering on pt.tag_id, but that column comes from the left join, so it may be null. So when a post has no tags it is always filtered out.

It also occurs to me that you don’t really need a join (which may cause cardinality issues as you are seeing) ; if you just want to filter the posts per tag, exists seems more appropriate:

select p.*
from post p
where exists (
    select 1
    from post_tag pt
    where pt_post_id = p.id
      and pt.tags_id = any(cast(STRING_TO_ARRAY(CAST('9,8' AS TEXT), ',') AS INT[]))
)