Transaction not failing on update on unique key constraint

[michaelcozzolino]

Bug Report

Q	A
Version	4.0.4

Summary

Current behaviour

I am doing this raw query

        $groupIdsWithSortIndices = [
            1941,
            0,
            332,
            1,
            3494,
            2,
        ];
        $connection = $this->entityManager->getConnection();
        $connection->beginTransaction();
        try {
            $updateGroupsQuery = sprintf(
                <<<SQL
                        create temporary table `sort_indices`
                            (
                                id int not null primary key ,
                                sort_index int not null,
                                constraint id_sort_index_unique unique(id, sort_index)
                            );
                        
                        insert into sort_indices (id, sort_index) values %s; 
                        
                        update `groups` g
                        inner join `sort_indices` si on (si.`id` = g.`id`)
                        set g.`sortIndex` = si.`sort_index`;

                        drop temporary table sort_indices;
                    SQL,
                implode(
                    ', ',
                    array_fill(0, count($groupIdsWithSortIndices) / 2, '(?, ?)')
                )
            );
            $connection->executeStatement("
                    SET GLOBAL general_log = 'ON';
                    SET GLOBAL log_output = 'table';
            ");
            $connection->executeStatement($updateGroupsQuery, $groupIdsWithSortIndices);

            $connection->commit();
        } catch (Exception $exception) {
            $connection->rollBack();
            throw  $exception;
        }

The table is

create table if not exists `groups`
(
    id         int auto_increment primary key,
    product_id int          null,
    type_id    int          null,
    sortIndex  int          not null,
    format     varchar(255) null,
    constraint product_group_type_sort_index_unique_key
        unique (product_id, type_id, sortIndex),
    constraint FK_6DC044C54584665A
        foreign key (product_id) references shop.product (id),
    constraint FK_6DC044C5C54C8C93
        foreign key (type_id) references shop.group_type (id)
)
    collate = utf8_unicode_ci;

and if the update is using an already existing sort index i don't get any sql unique key constraint fail, while if i do
insert into groups (product_id, type_id, sortIndex) values (10,1,0); and the sort index already exists for that product and type id the query execution fails.

Expected behaviour

The query execution must fail also on update and not only on insert.

---

Another potential bug i realized is that the bound parameters must be integer but they are string. the logged sql query is the following
insert into sort_indices (id, sort_index) values ('1941', '0'), ('33'2, '1'), ('3494', '2');