Bug #40063 rolled back create...select with side effects replicated incorrectly with SBL

Description:
When a CREATE...SELECT statement is rolled back, e.g., due to a duplicate key error, it will not be written to the binlog. If the SELECT had side-effects (such as updates to a non-transactional table), then those side-effects will not be replicated to the slave, in case @@binlog_format = STATEMENT.

Cf. BUG#20265. BUG#28976 is similar but not the same.

How to repeat:
source include/master-slave.inc;

--echo ==== Init ====

# table that will be different on master and slave
CREATE TABLE myisam_table (a INT) ENGINE = MYISAM;

# auxiliary table with two rows
CREATE TABLE table_with_two_rows (a INT);
INSERT INTO table_with_two_rows VALUES (0), (0);

# function that inserts into myisam_table and returns a constant
DELIMITER |;
CREATE FUNCTION insert_into_myisam()
 RETURNS INT
 BEGIN
   INSERT INTO myisam_table VALUES (1);
   RETURN 1;
 END|
DELIMITER ;|

--echo ==== Test ====

# Will fail with duplicate key error. This causes the statement to not
# be binlogged, despite it had side effects.
--error ER_DUP_ENTRY
CREATE TABLE new_table (a INT PRIMARY KEY)
  SELECT insert_into_myisam() AS a FROM table_with_two_rows;

# Show that new_table does not exist.
SHOW TABLES;
# Show that two rows were inserted in the myisam_table.
SELECT * FROM myisam_table;

sync_slave_with_master;

# Show that new_table does not exist on slave.
SHOW TABLES;
# Show that the two rows were *not* inserted in the myisam_table.
SELECT * FROM myisam_table;

--echo ==== Clean up ====

connection master;
DROP TABLE myisam_table;
DROP TABLE table_with_two_rows;
DROP FUNCTION insert_into_myisam;
sync_slave_with_master;

exit;

Suggested fix:
One of the following:

 (1) Document as a limitation of statement-based logging, mark CREATE-SELECT as unsafe.

 (2) When a CREATE-SELECT is rolled back and the statement would have been binlogged in statement mode (as determined by decide_binlog_format()), write just the SELECT to the binlog. (It is only necessary to write SELECT to the binlog in case some non-transactional table was modified.)

 (3) When a CREATE-SELECT is rolled back, write it to the binlog and insert a DROP TABLE IF EXISTS afterwards.

I think (3) is not good since CREATE has an implicit commit: if the CREATE succeeds on slave, the slave will be in a state that never existed on master between the CREATE...SELECT and the DROP TABLE IF EXISTS.

Note: the test case should also have

source include/have_binlog_format_statement.inc;

Duplicate of bug#39804