MDEV-24617 OPTIMIZE on a sequence causes unexpected ER_BINLOG_UNSAFE_STATEMENT

The warning out of OPTIMIZE
  Statement is unsafe because it uses a system function
was indeed counterfactual and was resulted by checking an
insufficiently strict property of lex' sql_command_flags.

Fixed with deploying an additional checking of weather
the current sql command that modifes a share->non_determinstic_insert
table is capable of generating ROW format events.
The extra check rules out the unsafety to OPTIMIZE et al, while the
existing check continues to do so to CREATE TABLE (which is
perculiarly tagged as ROW-event generative sql command).

As a side effect sql_sequence.binlog test gets corrected and
binlog_stm_unsafe_warning.test is reinforced to add up
an unsafe CREATE..SELECT test.

5 files changed, 64 insertions, 5 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
index 135271d325e..971fae6208f 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
@@ -104,6 +104,17 @@ Note	1592	Unsafe statement written to the binary log using statement format sinc
 SHOW WARNINGS;
 Level	Code	Message
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
+CREATE TABLE t3 (a INT(11) DEFAULT NULL);
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (a INT(11) DEFAULT NULL, b BIGINT(20) DEFAULT uuid_short()) SELECT * FROM t3;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
+SHOW WARNINGS;
+Level	Code	Message
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
+CREATE OR REPLACE TABLE t4 (a INT(11) DEFAULT NULL) SELECT * FROM t3;
+SHOW WARNINGS;
+Level	Code	Message
 DROP FUNCTION sf_bug50192;
 DROP TRIGGER tr_bug50192;
-DROP TABLE t1, t2;
+DROP TABLE t1, t2, t3, t4;
diff --git a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
index 70566da4cfa..578f0ff5fc8 100644
--- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
+++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
@@ -189,8 +189,20 @@ SHOW WARNINGS;
 SELECT sf_bug50192();
 SHOW WARNINGS;
 
+# The test proves MDEV-24617 fixes leave in force
+# unsafe warnings in non-deterministic CREATE..SELECT cases.
+# Below an inserted default value to `b` of the target table is replication
+# unsafe. A warning must be out.
+CREATE TABLE t3 (a INT(11) DEFAULT NULL);
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (a INT(11) DEFAULT NULL, b BIGINT(20) DEFAULT uuid_short()) SELECT * FROM t3;
+SHOW WARNINGS;
+# no warning out of a deterministic "rhs" of SELECT
+CREATE OR REPLACE TABLE t4 (a INT(11) DEFAULT NULL) SELECT * FROM t3;
+SHOW WARNINGS;
+
 # cleanup
 
 DROP FUNCTION sf_bug50192;
 DROP TRIGGER tr_bug50192;
-DROP TABLE t1, t2;
+DROP TABLE t1, t2, t3, t4;
diff --git a/mysql-test/suite/sql_sequence/binlog.result b/mysql-test/suite/sql_sequence/binlog.result
index f01b3234e96..32788c988fc 100644
--- a/mysql-test/suite/sql_sequence/binlog.result
+++ b/mysql-test/suite/sql_sequence/binlog.result
@@ -12,6 +12,20 @@ select next value for s1, minimum_value from s1 where maximum_value> 4;
 next value for s1	minimum_value
 4	1
 alter sequence s1 maxvalue 1000;
+optimize table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	optimize	note	The storage engine for the table doesn't support optimize
+analyze table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	analyze	note	The storage engine for the table doesn't support analyze
+repair table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	repair	status	OK
+check table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	check	note	The storage engine for the table doesn't support check
+rename table s1 to tmp_s;
+rename table tmp_s to s1;
 drop sequence s1;
 include/show_binlog_events.inc
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
@@ -30,4 +44,14 @@ master-bin.000001	#	Query	#	#	COMMIT
 master-bin.000001	#	Gtid	#	#	GTID #-#-#
 master-bin.000001	#	Query	#	#	use `test`; alter sequence s1 maxvalue 1000
 master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; optimize table s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; analyze table s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; repair table s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; rename table s1 to tmp_s
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; rename table tmp_s to s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
 master-bin.000001	#	Query	#	#	use `test`; DROP SEQUENCE `s1` /* generated by server */
diff --git a/mysql-test/suite/sql_sequence/binlog.test b/mysql-test/suite/sql_sequence/binlog.test
index 5f2d52d7864..6a12e670a1e 100644
--- a/mysql-test/suite/sql_sequence/binlog.test
+++ b/mysql-test/suite/sql_sequence/binlog.test
@@ -1,5 +1,5 @@
---source include/have_udf.inc
---source include/have_log_bin.inc
+--source include/have_sequence.inc
+--source include/have_binlog_format_mixed_or_row.inc
 --source include/binlog_start_pos.inc
 
 #
@@ -21,6 +21,17 @@ select next value for s1, minimum_value from s1 where maximum_value> 4;
 #
 alter sequence s1 maxvalue 1000;
 
+# MDEV-24617 OPTIMIZE on a sequence causes unexpected
+# ER_BINLOG_UNSAFE_STATEMENT The test below verifies no unsafe
+# warnings anymore for any relavant commands that like OPTIMIZE can
+# not produce ROW format events therefore the unsafe warning either.
+optimize table s1;
+analyze table s1;
+repair table s1;
+check table s1;
+rename table s1 to tmp_s;
+rename table tmp_s to s1;
+
 drop sequence s1;
 
 --let $binlog_file = LAST
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index bbc46408c04..7f5a6345770 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -6210,7 +6210,8 @@ int THD::decide_logging_format(TABLE_LIST *tables)
             table->file->ht)
           multi_write_engine= TRUE;
         if (share->non_determinstic_insert &&
-            !(sql_command_flags[lex->sql_command] & CF_SCHEMA_CHANGE))
+            (sql_command_flags[lex->sql_command] & CF_CAN_GENERATE_ROW_EVENTS
+             && !(sql_command_flags[lex->sql_command] & CF_SCHEMA_CHANGE)))
           has_write_tables_with_unsafe_statements= true;
 
         trans= table->file->has_transactions();