search

vapor / fluent-postgres-driver

🐘 PostgreSQL driver for Fluent.
MIT License
146 stars 53 forks source link

[repro] When using a join, SELECT * messes up the ID of the models #111

Closed vzsg closed 4 years ago

vzsg commented 5 years ago

This PR at the time of writing is a reproduction of an issue that @rafiki270 reported on Slack.

Notably, if a query uses join with another table, this second table's ID field overwrites the values of the first table. This is probably a side-effect of SELECT * and the conflicting column names.

Test output currently:

[psql] [2019-05-15 13:35:27 +0000] CREATE TABLE "Person" ("id" BIGINT NOT NULL CONSTRAINT "pk:Person.id" PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "name" TEXT NOT NULL) []
[psql] [2019-05-15 13:35:27 +0000] CREATE TABLE "Cat" ("id" BIGINT NOT NULL CONSTRAINT "pk:Cat.id" PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "name" TEXT NOT NULL, "ownerID" BIGINT NOT NULL) []
[psql] [2019-05-15 13:35:27 +0000] INSERT INTO "Person" ("id", "name") VALUES ($1, $2) RETURNING * [1 (BIGINT), "Jon" (TEXT)]
[psql] [2019-05-15 13:35:27 +0000] INSERT INTO "Person" ("id", "name") VALUES ($1, $2) RETURNING * [2 (BIGINT), "Lyman" (TEXT)]
[psql] [2019-05-15 13:35:27 +0000] INSERT INTO "Person" ("id", "name") VALUES ($1, $2) RETURNING * [3 (BIGINT), "Liz" (TEXT)]
[psql] [2019-05-15 13:35:27 +0000] INSERT INTO "Cat" ("ownerID", "name", "id") VALUES ($1, $2, $3) RETURNING * [1 (BIGINT), "Garfield" (TEXT), 10 (BIGINT)]
[psql] [2019-05-15 13:35:27 +0000] INSERT INTO "Cat" ("name", "id", "ownerID") VALUES ($1, $2, $3) RETURNING * ["Nermal" (TEXT), 11 (BIGINT), 2 (BIGINT)]
[psql] [2019-05-15 13:35:27 +0000] SELECT * FROM "Person" INNER JOIN "Cat" ON "Person"."id" = "Cat"."ownerID" WHERE "Cat"."name" = ($1) ["Garfield" (TEXT)]
/Users/zsolt/Work/fluent-postgresql/Tests/FluentPostgreSQLTests/FluentPostgreSQLTests.swift:584: error: -[FluentPostgreSQLTests.FluentPostgreSQLTests testJoinIdOverwriting] : XCTAssertEqual failed: ("Person(id: Optional(1), name: "Jon")") is not equal to ("Person(id: Optional(10), name: "Jon")")

Note that Jon has ID 10 in the result.

rafiki270 commented 5 years ago

Issue https://github.com/vapor/fluent-postgresql/issues/112

vzsg commented 5 years ago

Interesting tidbits:

  1. The test is unfortunately not deterministic. Sometimes the test actually succeeds, sometimes instead of the id being 10, the name field contains "Garfield", sometimes both are wrong.

    Perhaps I should add a for loop to run it 10 times.

  2. The issue is not a regression caused by postgresql#134. I tested against 1.4.1 and 1.4.0, both showed the symptoms.

rafiki270 commented 5 years ago

A workaround described here https://github.com/vapor/fluent-postgresql/issues/112