[BUG]: $returningId returns the wrong ids when onDuplicateKeyUpdate is used

[aleixjf]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.38.3

What version of drizzle-kit are you using?

0.30.1

Other packages

No response

Describe the Bug

If i do the following:

const values: (typeof phones.$inferInsert)[] = Array.from(
            new Set(
                Array.from(entities.values()).map((entity) => ({
                    idClient,
                    countryCode: entity.countryCode ?? undefined,
                    phone: entity.phone ?? undefined,
                    extension: entity.extension ?? undefined,
                }))
            ).values()
        );
        
this.orm
                .insert(phones)
                .values(values)
                // ? This prevents MySQL throwing errors on duplicated keys.
                .onDuplicateKeyUpdate({set: {phone: sql`phone`}})
                .$returningId()

The $returningId will return the correct ids of the actual inserts, but will return a wrong id (I believe it is the row index in the table of the updated row, rather than its primary key) of the ones it handles with

.onDuplicateKeyUpdate({set: {phone: sql`phone`}})

This is the phones schema:

import {sql} from "drizzle-orm";
import {
    int,
    mysqlTable,
    timestamp,
    unique,
    varchar,
} from "drizzle-orm/mysql-core";

export const phones = mysqlTable(
    "phones",
    {
        id: int("id").autoincrement().primaryKey(),
        countryCode: varchar("countryCode", {length: 10}).default(""),
        phone: varchar("phone", {length: 20}).default(""),
        extension: varchar("extension", {length: 50}).default(""),
        type: varchar("type", {length: 50}),
        createdAt: timestamp("created_at", {mode: "string"})
            .defaultNow()
            .notNull(),
        updatedAt: timestamp("updated_at", {mode: "string"}) // Tracks updates
            .onUpdateNow()
            .notNull(),
    },
    (table) => [
        // primaryKey({columns: [table.id]}),
        unique("UNQ_PHONES::CC_PHONE_EXTENSION").on(
            table.countryCode,
            table.phone,
            table.extension
        ),
    ]
);

This can be easily tested following like this:

1. Create the table and create new entries (run the query once)
2. Delete the table entries
3. Execute the insert query twice to insert the same entities 2 times, the second one leading to the use of the onDuplicateKeyUpdate

(Steps 1 and 2 are required to make sure that the ids of the entries don't match the row number because of the autoincrement property)

By comparing the same query executed twice, i get the following results:

[Nest] 14313  - 07/01/2025, 11:52:27 VERBOSE [console] Returning IDs
[ { id: 22659 }, { id: 22660 }, { id: 22661 }, { id: 22662 }, { id: 22663 } ]

[Nest] 14313  - 07/01/2025, 11:53:11 VERBOSE [console] Returning IDs
[ { id: 0 },  { id: 1 },  { id: 2 },  { id: 3 },  { id: 4 } ]