DELETE on a table with a non-deterministic DEFAULT is needlessly marked binlog-unsafe, forcing row-by-row delete under binlog_format=MIXED

Problem

A table with a non-deterministic DEFAULT expression (e.g. id UUID DEFAULT UUID()) sets TABLE_SHARE::non_determinstic_insert when the .frm is opened (sql/table.cc, VCOL_NON_DETERMINISTIC | VCOL_SESSION_FUNC check).

In THD::decide_logging_format() (sql/sql_class.cc) this per-share flag marks any write statement to such a table as BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION:

if (share->non\_determinstic\_insert &&

    (sql\_command\_flags() & CF\_CAN\_GENERATE\_ROW\_EVENTS

     && !(sql\_command\_flags() & CF\_SCHEMA\_CHANGE)))

  has\_write\_tables\_with\_unsafe\_statements= true;

This includes plain DELETE, which never evaluates column DEFAULTs. With binlog_format=MIXED the statement is therefore switched to ROW, which in turn disables both server-side DELETE fast paths in mysql_delete() (sql/sql_delete.cc):

• the handler::delete_all_rows() path (guarded by !thd->is_current_stmt_binlog_format_row()),
• the direct delete path handler::direct_delete_rows() for engines with HA_CAN_DIRECT_UPDATE_AND_DELETE (guarded by !binlog_is_row).

As a result DELETE FROM t (even without WHERE) is executed row by row via rnd_next() + ha_delete_row() and binlogged as Delete_rows events — a significant and avoidable penalty for engines with an efficient bulk/direct delete (Spider, DuckDB engine, etc.), and extra binlog volume for everyone.

Analysis

A plain DELETE is statement-safe with respect to non-deterministic DEFAULTs:

• DELETE never evaluates column DEFAULT expressions; WHERE reads stored values;
• DELETE ... RETURNING DEFAULT(col) sends non-deterministic values to the client only, not into replicated state.

The check cannot be dropped entirely for DELETE, though: a DELETE trigger may implicitly evaluate the non-deterministic DEFAULT of another (or the same) table by inserting rows without listing the column — the trigger body then contains no unsafe token and only the share flag catches it via the prelocked table list.

Suggested fix

Narrow the condition in THD::decide_logging_format() so that the non_determinstic_insert flag does not mark the statement unsafe when the statement cannot evaluate defaults, e.g.:

!((lex->sql\_command == SQLCOM\_DELETE ||

   lex->sql\_command == SQLCOM\_DELETE\_MULTI) &&

  !lex->requires\_prelocking())

(no triggers / stored routines involved — nothing can evaluate a DEFAULT).

There is precedent for narrowing exactly this condition: MDEV-24617 added the !(sql_command_flags() & CF_SCHEMA_CHANGE) part because OPTIMIZE on a SEQUENCE (SEQUENCEs also set non_determinstic_insert) was falsely flagged unsafe.

Expected outcome

With binlog_format=MIXED, DELETE FROM t / DELETE ... WHERE on a table with DEFAULT UUID() and no triggers is logged as a statement and takes the delete_all_rows() / direct delete fast path; a DELETE whose trigger implicitly evaluates a non-deterministic DEFAULT keeps being logged in ROW format.