MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL

5 files changed, 72 insertions, 12 deletions

diff --git a/mysql-test/r/type_binary.result b/mysql-test/r/type_binary.result
index b00d1585b29..9d44d718a02 100644
--- a/mysql-test/r/type_binary.result
+++ b/mysql-test/r/type_binary.result
@@ -146,3 +146,37 @@ hex(f2)	hex(f3)
 0000	
 drop table t1;
 End of 5.0 tests
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL
+#
+SET NAMES utf8;
+CREATE TABLE t1 (a BINARY(30));
+INSERT INTO t1 VALUES ('1äÖüß@µ*$');
+SELECT CAST(a AS DECIMAL) FROM t1;
+CAST(a AS DECIMAL)
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00'
+DROP TABLE t1;
+CREATE TABLE t1 (a VARBINARY(30));
+INSERT INTO t1 VALUES ('1äÖüß@µ*$');
+SELECT CAST(a AS DECIMAL) FROM t1;
+CAST(a AS DECIMAL)
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$'
+DROP TABLE t1;
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES ('1äÖüß@µ*$');
+SELECT CAST(a AS DECIMAL) FROM t1;
+CAST(a AS DECIMAL)
+1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '1\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$'
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result
index ae9440859df..f4cbc188e69 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -3404,7 +3404,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$-- '
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_varbinary_1000` as decimal(37,2)) AS `CAST(my_varbinary_1000 AS DECIMAL(37,2))`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3424,7 +3424,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$-- '
 DROP VIEW v1;
 
 
@@ -3492,7 +3492,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_varchar_1000` as decimal(37,2)) AS `CAST(my_varchar_1000 AS DECIMAL(37,2))`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3512,7 +3512,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
 DROP VIEW v1;
 
 
diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result
index 81f7a0584d3..623fb38ef50 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -3405,7 +3405,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$-- '
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_varbinary_1000` as decimal(37,2)) AS `CAST(my_varbinary_1000 AS DECIMAL(37,2))`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3425,7 +3425,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$-- '
 DROP VIEW v1;
 
 
@@ -3493,7 +3493,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_varchar_1000` as decimal(37,2)) AS `CAST(my_varchar_1000 AS DECIMAL(37,2))`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3513,7 +3513,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
 DROP VIEW v1;
 
 
diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result
index 81f7a0584d3..623fb38ef50 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -3405,7 +3405,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$-- '
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_varbinary_1000` as decimal(37,2)) AS `CAST(my_varbinary_1000 AS DECIMAL(37,2))`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3425,7 +3425,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---\xC3\xA4\xC3\x96\xC3\xBC\xC3\x9F@\xC2\xB5*$-- '
 DROP VIEW v1;
 
 
@@ -3493,7 +3493,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_varchar_1000` as decimal(37,2)) AS `CAST(my_varchar_1000 AS DECIMAL(37,2))`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3513,7 +3513,7 @@ Warning	1292	Truncated incorrect DECIMAL value: ''
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
 Warning	1292	Truncated incorrect DECIMAL value: '<---------1000 characters-------------------------------------------------------------------------------------------------------'
 Warning	1918	Encountered illegal value '' when converting to DECIMAL
-Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
+Warning	1292	Truncated incorrect DECIMAL value: ' ---äÖüß@µ*$-- '
 DROP VIEW v1;
 
 
diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test
index 4d5a5312472..b583e257aa9 100644
--- a/mysql-test/t/type_binary.test
+++ b/mysql-test/t/type_binary.test
@@ -100,3 +100,29 @@ select hex(f2), hex(f3) from t1;
 drop table t1;
 
 --echo End of 5.0 tests
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL
+--echo #
+SET NAMES utf8;
+CREATE TABLE t1 (a BINARY(30));
+INSERT INTO t1 VALUES ('1äÖüß@µ*$');
+SELECT CAST(a AS DECIMAL) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a VARBINARY(30));
+INSERT INTO t1 VALUES ('1äÖüß@µ*$');
+SELECT CAST(a AS DECIMAL) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES ('1äÖüß@µ*$');
+SELECT CAST(a AS DECIMAL) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+