RBR + NO_TABLE_OPTIONS: CREATE ... SELECT ends up with wrong table options on replica

Notes:

• It is not one of many known replication limitations related to having different system variables on the master and slave, otherwise we wouldn't need SQL_MODE, we could just change DEFAULT_STORAGE_ENGINE on the master and achieve the same effect. Here the test case sets SQL_MODE in a session just for convenience. For a more realistic scenario, it can be run instead with --mysqld=--sql-mode=... as a startup option, to be used by both servers.
• The test case uses SQL_MODE=ORACLE. It is not the only way, any value which contains NO_TABLE_OPTIONS has the same effect; but ORACLE is something that we know for a fact our users use in real life.

--source include/have_binlog_format_row.inc

--source include/master-slave.inc

# Or run the test case with --mysqld=--sql-mode=ORACLE

SET SQL_MODE = ORACLE;

CREATE TABLE t1 ENGINE=Aria AS SELECT 1 AS a;

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';

--connection slave

--source include/sync_with_master_gtid.inc

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';

--connection master

DROP TABLE t1;

--source include/rpl_end.inc

[connection master]

SET SQL_MODE = ORACLE;

CREATE TABLE t1 ENGINE=Aria AS SELECT 1 AS a;

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';

TABLE_NAME      ENGINE

t1      Aria

connection slave;

include/sync_with_master_gtid.inc

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';

TABLE_NAME      ENGINE

t1      MyISAM

The reason seems fairly obvious, in RBR CREATE .. SELECT is re-written for the binlog as CREATE statement and INSERT row event; and CREATE statement is written according to the given SQL_MODE, so it doesn't have ENGINE (or any other table options, for that matter, which could cause different problems), so it is executed with defaults on the slave.