MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode

- Adding optional qualifiers to data types:
    CREATE TABLE t1 (a schema.DATE);
  Qualifiers now work only for three pre-defined schemas:

    mariadb_schema
    oracle_schema
    maxdb_schema

  These schemas are virtual (hard-coded) for now, but may turn into real
  databases on disk in the future.

- mariadb_schema.TYPE now always resolves to a true MariaDB data
  type TYPE without sql_mode specific translations.

- oracle_schema.DATE translates to MariaDB DATETIME.

- maxdb_schema.TIMESTAMP translates to MariaDB DATETIME.

- Fixing SHOW CREATE TABLE to use a qualifier for a data type TYPE
  if the current sql_mode translates TYPE to something else.

The above changes fix the reported problem, so this script:

    SET sql_mode=ORACLE;
    CREATE TABLE t2 AS SELECT mariadb_date_column FROM t1;

is now replicated as:

    SET sql_mode=ORACLE;
    CREATE TABLE t2 (mariadb_date_column mariadb_schema.DATE);

and the slave can unambiguously treat DATE as the true MariaDB DATE
without ORACLE specific translation to DATETIME.

Similar,

    SET sql_mode=MAXDB;
    CREATE TABLE t2 AS SELECT mariadb_timestamp_column FROM t1;

is now replicated as:

    SET sql_mode=MAXDB;
    CREATE TABLE t2 (mariadb_timestamp_column mariadb_schema.TIMESTAMP);

so the slave treats TIMESTAMP as the true MariaDB TIMESTAMP
without MAXDB specific translation to DATETIME.

4 files changed, 373 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result b/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result
new file mode 100644
index 00000000000..9aca6fa3542
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result
@@ -0,0 +1,86 @@
+include/master-slave.inc
+[connection master]
+SET SQL_MODE=DEFAULT;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES ('2001-01-01');
+SET SQL_MODE= ORACLE;
+CREATE TABLE t2 SELECT * FROM t1;
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a DATE)
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Annotate_rows	#	#	INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Annotate_rows	#	#	INSERT INTO t1 VALUES ('2001-01-01')
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE "t2" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+master-bin.000001	#	Annotate_rows	#	#	CREATE TABLE t2 SELECT * FROM t1
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+SET SQL_MODE= DEFAULT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SET SQL_MODE= ORACLE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+connection slave;
+SELECT * FROM t1;
+a
+NULL
+2001-01-01
+SELECT * FROM t2;
+a
+NULL
+2001-01-01
+SET SQL_MODE= DEFAULT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SET SQL_MODE= ORACLE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+connection master;
+DROP TABLE t1, t2;
+include/rpl_end.inc
diff --git a/mysql-test/suite/compat/oracle/r/type_date.result b/mysql-test/suite/compat/oracle/r/type_date.result
index 0989fc593d2..40d2a834056 100644
--- a/mysql-test/suite/compat/oracle/r/type_date.result
+++ b/mysql-test/suite/compat/oracle/r/type_date.result
@@ -6,3 +6,153 @@ t1	CREATE TABLE "t1" (
   "a" datetime DEFAULT NULL
 )
 DROP TABLE t1;
+#
+# MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode
+#
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a unknown.DATE);
+ERROR HY000: Unknown data type: 'unknown.date'
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (
+def_date DATE,
+mdb_date mariadb_schema.DATE,
+ora_date oracle_schema.DATE,
+max_date maxdb_schema.DATE
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `def_date` date DEFAULT NULL,
+  `mdb_date` date DEFAULT NULL,
+  `ora_date` datetime DEFAULT NULL,
+  `max_date` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SET sql_mode=ORACLE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "def_date" mariadb_schema.date DEFAULT NULL,
+  "mdb_date" mariadb_schema.date DEFAULT NULL,
+  "ora_date" datetime DEFAULT NULL,
+  "max_date" mariadb_schema.date DEFAULT NULL
+)
+DROP TABLE t1;
+SET sql_mode=ORACLE;
+CREATE TABLE t1 (
+def_date DATE,
+mdb_date mariadb_schema.DATE,
+ora_date oracle_schema.DATE,
+max_date maxdb_schema.DATE
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "def_date" datetime DEFAULT NULL,
+  "mdb_date" mariadb_schema.date DEFAULT NULL,
+  "ora_date" datetime DEFAULT NULL,
+  "max_date" mariadb_schema.date DEFAULT NULL
+)
+SET sql_mode=DEFAULT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `def_date` datetime DEFAULT NULL,
+  `mdb_date` date DEFAULT NULL,
+  `ora_date` datetime DEFAULT NULL,
+  `max_date` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# ALTER..MODIFY and ALTER..CHANGE understand qualifiers 
+#
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('2001-01-01');
+SET sql_mode=ORACLE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+SELECT * FROM t1;
+a
+2001-01-01
+ALTER TABLE t1 MODIFY a DATE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" datetime DEFAULT NULL
+)
+SELECT * FROM t1;
+a
+2001-01-01 00:00:00
+ALTER TABLE t1 MODIFY a mariadb_schema.DATE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" mariadb_schema.date DEFAULT NULL
+)
+SELECT * FROM t1;
+a
+2001-01-01
+ALTER TABLE t1 MODIFY a oracle_schema.DATE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" datetime DEFAULT NULL
+)
+SELECT * FROM t1;
+a
+2001-01-01 00:00:00
+ALTER TABLE t1 CHANGE a b mariadb_schema.DATE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "b" mariadb_schema.date DEFAULT NULL
+)
+SELECT * FROM t1;
+b
+2001-01-01
+ALTER TABLE t1 CHANGE b a oracle_schema.DATE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a" datetime DEFAULT NULL
+)
+SELECT * FROM t1;
+a
+2001-01-01 00:00:00
+DROP TABLE t1;
+#
+# Qualified syntax is not supported yet in SP
+# See MDEV-23353 Qualified data types in SP
+#
+SET sql_mode=ORACLE;
+CREATE FUNCTION f1() RETURN mariadb_schema.DATE AS
+BEGIN
+RETURN CURRENT_DATE;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mariadb_schema.DATE AS
+BEGIN
+RETURN CURRENT_DATE;
+END' at line 1
+CREATE PROCEDURE p1(a mariadb_schema.DATE) AS
+BEGIN
+NULL;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS
+BEGIN
+NULL;
+END' at line 1
+CREATE PROCEDURE p1() AS
+a mariadb_schema.DATE;
+BEGIN
+NULL;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';
+BEGIN
+NULL;
+END' at line 2
diff --git a/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test b/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test
new file mode 100644
index 00000000000..b2aff23333b
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test
@@ -0,0 +1,38 @@
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+SET SQL_MODE=DEFAULT;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES ('2001-01-01');
+
+SET SQL_MODE= ORACLE;
+CREATE TABLE t2 SELECT * FROM t1;
+
+--let $binlog_file = LAST
+source include/show_binlog_events.inc;
+
+SET SQL_MODE= DEFAULT;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+SET SQL_MODE= ORACLE;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+--sync_slave_with_master
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+SET SQL_MODE= DEFAULT;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+SET SQL_MODE= ORACLE;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+# Cleanup
+--connection master
+DROP TABLE t1, t2;
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/compat/oracle/t/type_date.test b/mysql-test/suite/compat/oracle/t/type_date.test
index 61f7aa53944..36a5e99795f 100644
--- a/mysql-test/suite/compat/oracle/t/type_date.test
+++ b/mysql-test/suite/compat/oracle/t/type_date.test
@@ -2,3 +2,102 @@ SET sql_mode=ORACLE;
 CREATE TABLE t1 (a DATE);
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode
+--echo #
+
+SET sql_mode=DEFAULT;
+--error ER_UNKNOWN_ERROR
+CREATE TABLE t1 (a unknown.DATE);
+
+
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (
+  def_date DATE,
+  mdb_date mariadb_schema.DATE,
+  ora_date oracle_schema.DATE,
+  max_date maxdb_schema.DATE
+);
+SHOW CREATE TABLE t1;
+SET sql_mode=ORACLE;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+SET sql_mode=ORACLE;
+CREATE TABLE t1 (
+  def_date DATE,
+  mdb_date mariadb_schema.DATE,
+  ora_date oracle_schema.DATE,
+  max_date maxdb_schema.DATE
+);
+SHOW CREATE TABLE t1;
+SET sql_mode=DEFAULT;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # ALTER..MODIFY and ALTER..CHANGE understand qualifiers 
+--echo #
+
+SET sql_mode=DEFAULT;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('2001-01-01');
+SET sql_mode=ORACLE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+ALTER TABLE t1 MODIFY a DATE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+ALTER TABLE t1 MODIFY a mariadb_schema.DATE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+ALTER TABLE t1 MODIFY a oracle_schema.DATE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+ALTER TABLE t1 CHANGE a b mariadb_schema.DATE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+ALTER TABLE t1 CHANGE b a oracle_schema.DATE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Qualified syntax is not supported yet in SP
+--echo # See MDEV-23353 Qualified data types in SP
+--echo #
+
+SET sql_mode=ORACLE;
+DELIMITER $$;
+# Change to this when merging to 10.5:
+#--error ER_UNKNOWN_DATA_TYPE
+--error ER_PARSE_ERROR
+CREATE FUNCTION f1() RETURN mariadb_schema.DATE AS
+BEGIN
+  RETURN CURRENT_DATE;
+END;
+$$
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1(a mariadb_schema.DATE) AS
+BEGIN
+  NULL;
+END;
+$$
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1() AS
+  a mariadb_schema.DATE;
+BEGIN
+  NULL;
+END;
+$$
+DELIMITER ;$$