Recursive SQL query leaking data from parent to child

Question

i have a recursive model of comments, where comments are owned by users, and each comment is related to a list of comments as their replies, this is a DDL for reproduce this database (im using postgres:13 image):

CREATE TABLE public.users (
    id varchar NOT NULL,
    "name" varchar NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

CREATE TABLE public."comments" (
    id uuid NOT NULL,
    body varchar NULL,
    reference_id varchar NULL,
    parent_id uuid NULL,
    user_id varchar NULL,
    CONSTRAINT comments_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_reference_id ON public.comments USING btree (reference_id);
CREATE INDEX idx_user_id ON public.comments USING btree (user_id);

INSERT INTO public.users
(id, "name")
VALUES('0', 'olbert');
INSERT INTO public.users
(id, "name")
VALUES('1', 'whitman');

INSERT INTO public."comments"
(id, body, reference_id, parent_id, user_id)
VALUES('56743f89-648a-4eac-848e-400fd0c777c2'::uuid, 'test comment', 'test', NULL, '0');
INSERT INTO public."comments"
(id, body, reference_id, parent_id, user_id)
VALUES('6ebc4e7b-17c7-413c-ad34-e0c995d9b807'::uuid, 'test comment', 'test', '56743f89-648a-4eac-848e-400fd0c777c2'::uuid, '1');

Then, im trying to query this recursively using this query:

with recursive output_comments as (
        select c.id, c.body, c.parent_id, c.reference_id, c.user_id, u.name as user_name
        from comments c left join users u on c.user_id = u.id
        where reference_id = 'test' and parent_id is null
    union all
        select c.id, c.body, c.parent_id, c.reference_id, c.user_id, u.name as user_name
        from output_comments
        inner join comments c on output_comments.id = c.parent_id
        left join users u on output_comments.user_id = u.id
) select * from output_comments;

But what happens is that the reply gets the user of the parent comment, instead of its own user (its creator), so instead of returning something like this:

56743f89-648a-4eac-848e-400fd0c777c2, test comment, null, test, 0, olbert
6ebc4e7b-17c7-413c-ad34-e0c995d9b807, test comment, 56743f89-648a-4eac-848e-400fd0c777c2, test, 1, whitman

It returns soemthing like this:

56743f89-648a-4eac-848e-400fd0c777c2, test comment, null, test, 0, olbert
6ebc4e7b-17c7-413c-ad34-e0c995d9b807, test comment, 56743f89-648a-4eac-848e-400fd0c777c2, test, 1, olbert

Im not an expert sql alchemist, so i decided to ask for help, thanks!

Answer 1

Try this

with recursive output_comments as (
        select c.id, c.body, c.parent_id, c.reference_id, c.user_id, u.name as user_name
        from comments c left join users u on c.user_id = u.id
        where reference_id = 'test' and parent_id is null
        
        
    union all
        select c.id, c.body, c.parent_id, c.reference_id, c.user_id, u.name as user_name
        from comments c left join users u on c.user_id = u.id
        inner join output_comments oc on (oc.id= c.parent_id)
) select * from output_comments;

Output: