Hi, Alexander, On Jan 11, Alexander Barkov wrote:

revision-id: 424ee6eac78 (mariadb-10.5.23-57-g424ee6eac78) parent(s): c9902a20b3a author: Alexander Barkov committer: Alexander Barkov timestamp: 2024-01-10 15:35:25 +0400 message:

MDEV-32837 long unique does not work like unique key when using replace

diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ec9e379768f..edc2be207f3 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2065,7 +2065,8 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, select_result *sink) we just should not expose this fact to users by invoking ON UPDATE triggers. */ - if (last_uniq_key(table,key_nr) && + if ((!table->s->long_unique_table || table->versioned()) && + last_uniq_key(table,key_nr) && !table->file->referenced_by_foreign_key() && (!table->triggers || !table->triggers->has_delete_triggers())) {

1. why table->versioned() ? 2. use show status in your test for handler_update/delete to see what REPLACE actually did 3. technically, you can keep the optimization if the long unique is the last *and there are no in-engine uniques*, this can be checked with && !(table->key_info->flags & HA_NOSAME) 4. but here's the problem that last_uniq_key() doesn't see long uniques, so it'll treat every one of them as the last. So it should be something like bool is_long_unique= table->s->key_info[key_nr].flags & HA_UNIQUE_HASH; if ((is_long_unique ? last_uniq_key(table, table->s->key_info, key_nr) && !(table->key_info->flags & HA_NOSAME) : last_uniq_key(table, table->key_info, key_nr)) && !table->file->referenced_by_foreign_key() && (!table->triggers || !table->triggers->has_delete_triggers())) I suspect this will correctly use the UPDATE optimization in all cases (only long unique / mix of uniques)x(last / not last key conflicting) Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org