Merge remote-tracking branch 'origin/bb-10.2-ext' into 10.310.3-MDEV-10953

3 files changed, 150 insertions, 0 deletions

diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index bb3251b3e31..29a9510db00 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1853,6 +1853,16 @@ SELECT f1,HEX(f2) FROM t1                  WHERE f1='YQ==' AND (f2= from_base64(
 SELECT f1,HEX(f2) FROM t1                  WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ=="));
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-12685 Oracle-compatible function CHR()
+--echo #
+select chr(65);
+create database mysqltest1 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin';
+use mysqltest1;
+select charset(chr(65)), length(chr(65)),char_length(chr(65));
+select charset(chr(14844588)), length(chr(14844588)),char_length(chr(14844588));
+drop database mysqltest1;
+use test;
 
 --echo #
 --echo # End of 10.1 tests
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 34797337b1d..ab9c792d523 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -2294,5 +2294,18 @@ CALL p1('geometrycollection');
 DROP PROCEDURE p1;
 
 --echo #
+--echo # MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1)
+--echo #
+EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ?' USING POINT(1,1);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ?';
+EXECUTE stmt USING POINT(1,1);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 04ab71588be..ce8b2bc9c2a 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -1545,5 +1545,132 @@ SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-9495 Wrong field type for a UNION of a signed and an unsigned INT expression
+--echo #
+CREATE TABLE t1 (a INT, b INT UNSIGNED);
+INSERT INTO t1 VALUES (0x7FFFFFFF,0xFFFFFFFF);
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2 ORDER BY a;
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT COALESCE(a,b), COALESCE(b,a) FROM t1;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-9497 UNION and COALESCE produce different field types for DECIMAL+INT
+--echo #
+CREATE TABLE t1 AS SELECT COALESCE(10.1,CAST(10 AS UNSIGNED)) AS a;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 AS SELECT 10.1 AS a UNION SELECT CAST(10 AS UNSIGNED);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-12594 UNION between fixed length double columns does not always preserve scale
+--echo #
+CREATE TABLE t1 (a FLOAT(20,4), b FLOAT(20,3), c FLOAT(20,4));
+INSERT INTO t1 VALUES (1111,2222,3333);
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE OR REPLACE TABLE t2 SELECT a FROM t1 UNION SELECT c FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE OR REPLACE TABLE t2 SELECT b FROM t1 UNION SELECT b FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT c FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+DROP TABLE t1;
+
+--echo # Corner case
+CREATE TABLE t1 (a FLOAT(255,4), b FLOAT(255,3));
+INSERT INTO t1 VALUES (1111,2222);
+CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-12595 UNION converts INT to BIGINT
+--echo #
+CREATE TABLE t1 AS SELECT
+  1,
+  -1,
+  COALESCE(1,1),
+  COALESCE(-1,-1),
+  COALESCE(1,-1),
+  COALESCE(-1,1);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT 1 AS c1,1 AS c2,-1 AS c3,-1 AS c4 UNION SELECT 1,-1,1,-1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-12599 UNION is not symmetric when mixing INT and CHAR
+--echo #
+
+CREATE OR REPLACE TABLE t1 AS SELECT 1 AS c1, 'a' AS c2 UNION SELECT 'a', 1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 AS SELECT 11112222 AS c1, 'a' AS c2 UNION SELECT 'a', 11112222;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+CREATE OR REPLACE TABLE t1 AS SELECT 111122223333 AS c1, 'a' AS c2 UNION SELECT 'a', 111122223333;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 AS SELECT 1111222233334444 AS c1, 'a' AS c2 UNION SELECT 'a', 1111222233334444;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT(3), b VARCHAR(1));
+CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BIGINT(3), b VARCHAR(1));
+CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BIGINT(12), b VARCHAR(1));
+CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
 --echo # End of 10.3 tests
 --echo #