Mikro-orm: [bug] case statement update optimization failure

[lookfirst]

Describe the bug

This is going to be hard to reproduce as a test case since it relies on weird ordering in a case statement. Thus, I will describe what I'm trying to do and what code is getting generated and why I think it is failing.

What I'm trying to do is "move" a PSU (power supply) from one rig (machine box) to another rig.

The code is effectively this:

        if (oldRig.psu) {
            newRig.psu = oldRig.psu;
            newRig.psu.status = RigStatus.MOVED;
            oldRig.psu = null;
        }
        oldRig.status = RigStatus.REMOVED;

        await this.em.persistAndFlush([oldRig, newRig]);

The SQL that is generated is this:

update "Rig"
set "status"    =
        case when ("id" = '00E067229BC8') then 'REMOVED' else "status" end,
    "psu"       = case
                      when ("id" = '00E067229BC8') then NULL
                      when ("id" = '00E0671F7819') then 'PS970312'
                      else "psu" end,
    "updatedAt" = case
                      when ("id" = '00E067229BC8') then '2020-11-02T22:28:41.749Z'
                      when ("id" = '00E0671F7819') then '2020-11-02T22:28:41.749Z'
                      else "updatedAt" end
where "id" in ('00E067229BC8', '00E0671F7819')

This runs fine in my unit tests against my local postgres... I can see everything generated just fine:

    [query] update "Rig" set "status" = case when ("id" = 'RANDOMID') then 'REMOVED' else "status" end, "psu" = case when ("id" = 'RANDOMID') then NULL when ("id" = 'ID2') then 'psu:1:e52c9f25d976' else "psu" end, "updatedAt" = case when ("id" = 'RANDOMID') then '2020-11-02T23:56:10.058Z' when ("id" = 'ID2') then '2020-11-02T23:56:10.058Z' else "updatedAt" end where "id" in ('RANDOMID', 'ID2') [took 0 ms]

But in production, I get this error:

 "message": "update \"Rig\" set \"status\" = case when (\"id\" = '00E067229BC8') then 'REMOVED' else \"status\" end, \"psu\" = case when (\"id\" = '00E067229BC8') then NULL when (\"id\" = '00E0671F7819') then 'PS970312' else \"psu\" end, \"updatedAt\" = case when (\"id\" = '00E067229BC8') then '2020-11-02T23:32:32.752Z' when (\"id\" = '00E0671F7819') then '2020-11-02T23:32:32.752Z' else \"updatedAt\" end where \"id\" in ('00E067229BC8', '00E0671F7819') - duplicate key value violates unique constraint \"Rig_psu_unique\"", 

The error being:

duplicate key value violates unique constraint \"Rig_psu_unique\"",

The Mikro generated DDL for Rig.psu looks like this:

    psu         varchar(255)
        constraint "Rig_psu_unique"
            unique
        constraint rig_psu_foreign
            references "Psu"
            on update cascade on delete set null

The Rig.psu entity definition looks like this (it hasn't changed since Mikro 3.x):

    @OneToOne(() => Psu, (psu) => psu.rig, { owner: true, nullable: true })
    @Field(() => Psu, { nullable: true })
    psu?: Psu | null;

Expected behavior
No error of course. =) This also ran just fine in Mikro 3.x, so I suspect that something is changed in 4.x... namely generating these case statements.

Workaround

I can probably change this to run the two modifications separately in a transaction.

Additional context

I suspect that the PG query planner doesn't know how to deal with the fact that I'm going to remove something later.

Versions

| Dependency | Version |
| - | - |
| typescript | 4.0.5 |
| mikro-orm | 4.2.3 |
| pgsql | 8.2.1 |

[lookfirst]

Ah, I see you did some big change for case statements to optimize things. Well, this is one 'case' where it won't work. I need some way to turn this off or some sort of workaround. So far, I can't find anything... the optimizer always tries to use a case statement.

[B4nan]

Interesting... Anyway, you can disable batch updates (globally) via useBatchUpdates: false.

[B4nan]

This is pretty much the same issue:

https://stackoverflow.com/questions/5403437/atomic-multi-row-update-with-a-unique-constraint

In pg we could use SET CONSTRAINTS ALL DEFERRED. The fact that we are batch updating 1:1 property can be detected easily, so it would be there only when needed.

But looks like mysql does not support that 🤷

https://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit

Or we could split it into 2 queries automatically for sure :] I guess that will be safer way, also it will work for all drivers out of box.

edit: actually looks like this is problem only with postgres, both sqlite and mysql are working just fine

[B4nan]

The solution in 4.3.0 was bad (let's say too aggressive) and had some side effects, I took a bit different approach that should hopefully be applied only in the right time. Will be shipping 4.3.1 soon, would be great if you could verify it still works fine with latest dev - 4.3.1-dev.9.