Quoted Tables Break Implicit Many to Many in Python but not Node

[Insighttful]

Bug description

When a table is quoted, implicit many to many relationship works by defining an A and B column based on the alphabetic order of the table names in an intermediary table to handle the relationships.

If one of the tables requires quoting, Node client properly handles the initial " character and maintains the proper order of the the tables. However, the Python client orders the table starting with the " character last. If the quoted table name should come first, then this breaks the functionality of many to many relationships. IDs meant for column A are used for column B and vice versa.

A symptom of this is seeing prisma.errors.ForeignKeyViolationError errors thrown when using the many to many relationship.

For example: prisma.errors.ForeignKeyViolationError: Foreign key constraint failed on the field: _foo_to_bar_A_fkey (index)

On further inspection, reproducing the Prisma logs as Postgres queries:

-- What Python prisma mistakenly does to update many-to-many relationships for case and hotspot
-- Implicit structure transparently defined by Prisma expects case to be A and hotspot to be B
INSERT INTO "public"."_foundd_case_to_hotspot" ("A", "B")
VALUES
    ('414a4c8b-80f1-4eda-8998-45132ee132dd', 'b16c2da8-9e6d-49e9-862b-521138934758'),
    ('414a4c8b-80f1-4eda-8998-45132ee132dd', '9f48ec72-387a-4e45-b459-0258d5364bc5'),
    ('414a4c8b-80f1-4eda-8998-45132ee132dd', '6c4b54fc-6aa3-4704-8ef1-7d8a1c0e0673')
ON CONFLICT DO NOTHING;

-- What Node prisma properly does to update many-to-many relationships for case and hotspot using same database
INSERT INTO "public"."_foundd_case_to_hotspot" ("A", "B")
VALUES
    ('b16c2da8-9e6d-49e9-862b-521138934758', '414a4c8b-80f1-4eda-8998-45132ee132dd'),
    ('9f48ec72-387a-4e45-b459-0258d5364bc5', '414a4c8b-80f1-4eda-8998-45132ee132dd'),
    ('6c4b54fc-6aa3-4704-8ef1-7d8a1c0e0673', '414a4c8b-80f1-4eda-8998-45132ee132dd')
ON CONFLICT DO NOTHING;

-- In reality Python prisma is passing cases in column "B" thus constraint is failing since "B" is for hotspots
SELECT *
FROM "case" c
WHERE c.id = 'b16c2da8-9e6d-49e9-862b-521138934758';
-- successful results confirms a case is being passed in column B, while it should be in column A

How to reproduce

Steps to reproduce the behavior:

1. Create 2 tables 'case', and 'hotspot'
2. Enable logging
3. Attempt:

await db.hotspot.update(
            where={"id": case_id},
            data={
                "hotspots": {"connect": hotspot_ids},
            },
        )

6. Query will fail
7. Examine the A,B parameterized values
8. The case id will be in position B, while record ids will be in position A (They should be opposite)
9. Attempt the same query using Node Prisma
10. Query with Node will succeed (case id will be in position A, record ids in position B)

Expected behavior

When a quoted table is involved in an implicit many to many relationship, the Python client should follow the same behavior as the Node client and ignore a leading " character when negotiating the order of the intermediary table references.

Note that one cannot always avoid quoted tables since the original architect of a database may not have accounted for reserved keywords in Python when they named the tables. As stated previously Node Prisma client can use the implicit many to many under these conditions while Python cannot.

Prisma information

Intermediary table as defined by Prisma

-- auto-generated definition
create table _foundd_case_to_hotspot
(
    "A" text not null
        references "case"  -- < issue is here
            on update cascade on delete cascade,
    "B" text not null
        references hotspot
            on update cascade on delete cascade
);

alter table _foundd_case_to_hotspot
    owner to postgres;

create unique index "_foundd_case_to_hotspot_AB_unique"
    on _foundd_case_to_hotspot ("A", "B");

create index "_foundd_case_to_hotspot_B_index"
    on _foundd_case_to_hotspot ("B");

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Python version: 3.11.4
• Prisma version: 0.10.0

prisma                  : 4.15.0
prisma client python    : 0.10.0
platform                : darwin
expected engine version : 8fbc245156db7124f997f4cecdd8d1219e360944
installed extras        : []
install path            : /Users/anon/Developer/foundd_engine/.venv/lib/python3.11/site-packages/prisma
binary cache dir        : /Users/anon/.cache/prisma-python/binaries/4.15.0/8fbc245156db7124f997f4cecdd8d1219e360944