`GROUP BY` with >1 column sharing the same name (ie: `id`) returns duplicates value for each column sharing that name.

[peetss]

Describe the bug

Demonstrate this issue by simply adding a GROUP BY clause to the first query on the playground.

To Reproduce

-- create tables
CREATE TABLE "user" ("id" SERIAL NOT NULL, "name" text NOT NULL, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"));
CREATE TABLE "photo" ("id" SERIAL NOT NULL, "url" text NOT NULL, "userId" integer, CONSTRAINT "PK_723fa50bf70dcfd06fb5a44d4ff" PRIMARY KEY ("id"));
ALTER TABLE "photo" ADD CONSTRAINT "FK_4494006ff358f754d07df5ccc87" FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "user" ADD IF NOT EXISTS "name" text not null;
ALTER TABLE "user" ADD data jsonb;

-- insert data
INSERT INTO "photo"("url", "userId") VALUES ('photo-of-me-1.jpg', DEFAULT) RETURNING "id";
INSERT INTO "photo"("url", "userId") VALUES ('photo-of-me-2.jpg', DEFAULT) RETURNING "id";
INSERT INTO "user"("name", "data") VALUES ('me', '{"tags":["nice"]}') RETURNING "id";
UPDATE "photo" SET "userId" = 1 WHERE "id" = 1;
UPDATE "photo" SET "userId" = 1 WHERE "id" = 2;
INSERT INTO "photo"("url", "userId") VALUES ('photo-of-you-1.jpg', DEFAULT) RETURNING "id";
INSERT INTO "photo"("url", "userId") VALUES ('photo-of-you-2.jpg', DEFAULT) RETURNING "id";
INSERT INTO "user"("name") VALUES ('you') RETURNING "id";
UPDATE "photo" SET "userId" = 2 WHERE "id" = 3;

UPDATE "photo" SET "userId" = 2 WHERE "id" = 4;

-- ============== query data ===============

-- Joins supported, with a best effort to use indinces.
SELECT "user"."id" AS "user_id", "photo"."id" AS "photo_id"
    FROM "user" "user"
    LEFT JOIN "photo" "photo" ON "photo"."userId"="user"."id"
        GROUP BY user_id;

pg-mem version

2.6.13

[JonathanSoszka]

I'm seeing the exact same issue

[peetss]

Sadly I was unable to fix this and had to move to a real postgres database to move forward.

[Jurtracey]

Having the same issue here!

[peetss]

I eventually solved this problem but sadly had to move away from pg-mem to do so, more information here.

[Igggr]

Same issue. Write 2 tests. First test uses GROUP BY with differently-named columns. It passed. Second use GROUP BY with equally-name columns. It failled.

   // PASSED
    it('can group by few differently-named columns', () => {
        expect(many(`create table category(id int, title text);
                     create table product(id int, category_id int, name text);
                     create table orders(id int, product_id int, amount int);

                     insert into category (id, title) VALUES (1, 'clothes'), (2, 'food');
                     insert into product (id, category_id, name) VALUES (3, 1, 't-shirt'), (4, 1, 'jeans'), (5, 2, 'apple'), (6, 2, 'banana');
                     insert into orders (id, product_id, amount) VALUES (7, 3, 10), (8, 3, 15), (9, 4, 20), (10, 4, 8), (11, 5, 9);

                    select c.title AS category, p.name AS name, SUM(o.amount) AS "totalAmount"
                    FROM category c
                    LEFT JOIN product p ON c.id = p.category_id
                    LEFT JOIN orders o ON o.product_id = p.id
                    GROUP BY p.name, c.title
                    `))
            .toEqual([{
                category: 'clothes',
                name: 't-shirt',
                totalAmount: 25,
            },
            {
                category: 'clothes',
                name: 'jeans',
                totalAmount: 28,
            },
            {
                category: 'food',
                name: 'apple',
                totalAmount: 9,
            },
            {
                category: 'food',
                name: 'banana',
                totalAmount: null,
            }
            ]);
    });

    // FAILED
    it('can group by few equally-named columns', () => {
        expect(many(`create table category(id int, name text);
                     create table product(id int, category_id int, name text);
                     create table orders(id int, product_id int, amount int);

                     insert into category (id, name) VALUES (1, 'clothes'), (2, 'food');
                     insert into product (id, category_id, name) VALUES (3, 1, 't-shirt'), (4, 1, 'jeans'), (5, 2, 'apple'), (6, 2, 'banana');
                     insert into orders (id, product_id, amount) VALUES (7, 3, 10), (8, 3, 15), (9, 4, 20), (10, 4, 8), (11, 5, 9);

                    select c.name AS category, p.name AS name, SUM(o.amount) AS "totalAmount"
                    FROM category c
                    LEFT JOIN product p ON c.id = p.category_id
                    LEFT JOIN orders o ON o.product_id = p.id
                    GROUP BY p.name, c.name
                    `))
            .toEqual([{
                category: 'clothes',
                name: 't-shirt',
                totalAmount: 25,
            },
            {
                category: 'clothes',
                name: 'jeans',
                totalAmount: 28,
            },
            {
                category: 'food',
                name: 'apple',
                totalAmount: 9,
            },
            {
                category: 'food',
                name: 'banana',
                totalAmount: null,
            }
            ]);
    });