MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES

1 files changed, 137 insertions, 0 deletions

diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 17e8be839a8..b514dbb5b2d 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -587,3 +587,140 @@ SHOW CREATE TABLE t1;
 SELECT * FROM t1;
 DROP TABLE t1;
 SET sql_mode=DEFAULT;
+
+
+--echo #
+--echo # MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES
+--echo #
+
+SET sql_mode=STRICT_ALL_TABLES;
+SELECT CAST('xxx' AS CHAR(1));
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(1));
+INSERT INTO t1 VALUES (CAST('xxx' AS CHAR(1)));
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(3));
+INSERT INTO t1 VALUES ('xxx');
+UPDATE t1 SET a=CAST(a AS CHAR(1));
+DROP TABLE t1;
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET latin1;
+  SET a=CAST('xxx' AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET latin1;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET latin1;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST('xxx' AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Conversion problems still escalate warnings to errors (without right truncation)
+
+DELIMITER $$;
+--error ER_CANNOT_CONVERT_CHARACTER
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_utf8 0xD18F AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Conversion problems still escalate warnings to errors (with right truncation)
+
+DELIMITER $$;
+--error ER_CANNOT_CONVERT_CHARACTER
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(30) CHARACTER SET utf8;
+  SET a=CAST(_utf8 0xD18FD18F AS CHAR(1) CHARACTER SET latin1);
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # CAST(number AS CHAR) escalates warnings to errors on truncation
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (CAST(123 AS CHAR(1)));
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('1');
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET a=CAST(123 AS CHAR(1));
+DROP TABLE t1;
+
+DELIMITER $$;
+--error ER_TRUNCATED_WRONG_VALUE
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(10);
+  SET a=CAST(123 AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # CAST(temporal AS CHAR) escalates warnings to errors on truncation
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (CAST(TIME'10:20:30' AS CHAR(1)));
+DROP TABLE t1;
+
+CREATE OR REPLACE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('1');
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1 SET a=CAST(TIME'10:20:30' AS CHAR(1));
+DROP TABLE t1;
+
+DELIMITER $$;
+--error ER_TRUNCATED_WRONG_VALUE
+BEGIN NOT ATOMIC
+  DECLARE a VARCHAR(10);
+  SET a=CAST(TIME'10:20:30' AS CHAR(1));
+END;
+$$
+DELIMITER ;$$
+
+SET sql_mode=DEFAULT;