2 files changed, 48 insertions, 0 deletions
diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
index 845c0f21e3e..cd4859c32e8 100644
--- a/mysql-test/r/set_statement.result
+++ b/mysql-test/r/set_statement.result
@@ -1217,3 +1217,28 @@ set @rnd=1;
 select @rnd;
 @rnd
 0
+#
+# MDEV-24860: Incorrect behaviour of SET STATEMENT in case
+#             it is executed as a prepared statement
+#
+PREPARE stmt FROM "SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t1 AS SELECT CONCAT('abc') AS c1";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+# Show definition of the table t1 created using Prepared Statement
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` varchar(3) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+# Create the table t1 with the same definition as it used before
+# using regular statement execution mode.
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t1 AS SELECT CONCAT('abc') AS c1;
+# Show that the table has the same definition as it is in case the table
+# created in prepared statement mode.
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` varchar(3) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
diff --git a/mysql-test/t/set_statement.test b/mysql-test/t/set_statement.test
index 1c70ed6281a..a5f5c03098d 100644
--- a/mysql-test/t/set_statement.test
+++ b/mysql-test/t/set_statement.test
@@ -1136,3 +1136,26 @@ while ($1)
 --enable_query_log
 --echo # @rnd should be 0
 select @rnd;
+
+--echo #
+--echo # MDEV-24860: Incorrect behaviour of SET STATEMENT in case
+--echo #             it is executed as a prepared statement
+--echo #
+PREPARE stmt FROM "SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t1 AS SELECT CONCAT('abc') AS c1";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo # Show definition of the table t1 created using Prepared Statement
+SHOW CREATE TABLE t1;
+
+DROP TABLE t1;
+
+--echo # Create the table t1 with the same definition as it used before
+--echo # using regular statement execution mode.
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t1 AS SELECT CONCAT('abc') AS c1;
+
+--echo # Show that the table has the same definition as it is in case the table
+--echo # created in prepared statement mode.
+SHOW CREATE TABLE t1;
+
+DROP TABLE t1;