MDEV-11478 Result data type aggregation for pluggable data types

2 files changed, 81 insertions, 0 deletions

diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 2645417f3e5..cfaae1a46e0 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1554,6 +1554,8 @@ format(rpad('111111111.1',
 # But in mysqltest --disable_prepare_warnings affects SELECT queries only
 # and can't suppress prepare time warnings for DO.
 #
+
+--error ER_CANT_AGGREGATE_2TYPES
 SELECT
 round(
   concat( (
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 09d7a29744f..b11fe18ec6d 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -1717,5 +1717,84 @@ SELECT c FROM t1;
 DROP TABLE t1;
 
 --echo #
+--echo #
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p2(query TEXT)
+BEGIN
+  DECLARE errcount INT DEFAULT 0;
+  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
+  BEGIN
+    SET errcount = errcount+1;
+    #SHOW WARNINGS;
+    GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT;
+    SELECT @p AS `ERROR: `;
+  END;
+  SELECT query AS ``;
+  EXECUTE IMMEDIATE query;
+  IF errcount = 0
+  THEN
+    SHOW CREATE TABLE t2;
+    DROP TABLE t2;
+  END IF;
+END;
+$$
+CREATE PROCEDURE p1(query TEXT)
+BEGIN
+  SELECT query AS `-------------------------------------`;
+  EXECUTE IMMEDIATE query;
+  CALL p2('CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1');
+  DROP TABLE t1;
+END;
+$$
+DELIMITER ;$$
+
+--disable_query_log
+CALL p1('CREATE TABLE t1 (a CHAR(10), b Point)');
+CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point)');
+CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point)');
+CALL p1('CREATE TABLE t1 (a TEXT, b Point)');
+CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point)');
+CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point)');
+
+CALL p1('CREATE TABLE t1 (a TINYINT, b Point)');
+CALL p1('CREATE TABLE t1 (a SMALLINT, b Point)');
+CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point)');
+CALL p1('CREATE TABLE t1 (a INT, b Point)');
+CALL p1('CREATE TABLE t1 (a BIGINT, b Point)');
+CALL p1('CREATE TABLE t1 (a FLOAT, b Point)');
+CALL p1('CREATE TABLE t1 (a DOUBLE, b Point)');
+CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point)');
+CALL p1('CREATE TABLE t1 (a BIT(8), b Point)');
+
+CALL p1('CREATE TABLE t1 (a TIME, b Point)');
+CALL p1('CREATE TABLE t1 (a DATE, b Point)');
+CALL p1('CREATE TABLE t1 (a DATETIME, b Point)');
+CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point)');
+CALL p1('CREATE TABLE t1 (a YEAR, b Point)');
+
+--echo # This creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458)
+CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)');
+CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)');
+--enable_query_log
+
+--echo # This does not preserve geometry type (MDEV-9405)
+CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+
+
+--echo #
 --echo # End of 10.2 tests
 --echo #