MDEV-8806 Numeric CAST produce different warnings for strings literals vs functions

14 files changed, 89 insertions, 6 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index c407e7bb875..04f7b262bac 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -2107,6 +2107,9 @@ select avg(export_set( 3, 'y', sha(i))), group_concat(d) from t1 group by d orde
 avg(export_set( 3, 'y', sha(i)))	group_concat(d)
 0	2008-10-02
 0	2010-12-12
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'y,y,356a192b7913b04c54574d18c28d46e6395428ab,356a192b7913b04c54574d18c28d46e6395428ab,356a192b7913b04c54574d18c28d46e6395428ab,3'
+Warning	1292	Truncated incorrect DOUBLE value: 'y,y,da4b9237bacccdf19c0760cab7aec4a8359010b0,da4b9237bacccdf19c0760cab7aec4a8359010b0,da4b9237bacccdf19c0760cab7aec4a8359010b0,d'
 drop table t1;
 #
 # MDEV-4290: crash in st_select_lex::mark_as_dependent
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index c49e55d503a..d768672efe4 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -702,6 +702,9 @@ SELECT floor(log10(format(concat_ws(5445796E25, 5306463, 30837), -358821)))
 as foo;
 foo
 2
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '53064635.445796e3130837'
+Warning	1292	Truncated incorrect DOUBLE value: '179,769,313,486,231,570,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,'
 #
 # Bug #58137 char(0) column cause:
 #            my_gcvt: Assertion `width > 0 && to != ((void *)0)' failed
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index a9548aa70e9..df69aff8b59 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -295,6 +295,8 @@ INSERT INTO t1 VALUES ('', 0);
 SELECT COALESCE(a) = COALESCE(b) FROM t1;
 COALESCE(a) = COALESCE(b)
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: ''
 DROP TABLE t1;
 #
 # Bug #54461: crash with longblob and union or update with subquery
diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result
index d114cccaf72..fcff0484b65 100644
--- a/mysql-test/r/func_regexp_pcre.result
+++ b/mysql-test/r/func_regexp_pcre.result
@@ -445,6 +445,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select regexp_replace('abc','b','x') AS `REGEXP_REPLACE('abc','b','x')`
 CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x')+0;
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'axc'
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -745,6 +747,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select regexp_substr('abc','b') AS `REGEXP_SUBSTR('abc','b')`
 CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b')+0;
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index d392da84ea7..9bc80613710 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1160,6 +1160,8 @@ Warning	1292	Truncated incorrect DOUBLE value: 'notnumber'
 SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
 str	num
 notnumber	0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'notnum'
 DROP TABLE t1,t2;
 CREATE TABLE t1(
 id int(11) NOT NULL auto_increment,
@@ -1352,6 +1354,8 @@ cast(rtrim('  20.06 ') as decimal(19,2))
 select cast(ltrim('  20.06 ') as decimal(19,2));
 cast(ltrim('  20.06 ') as decimal(19,2))
 20.06
+Warnings:
+Note	1292	Truncated incorrect DECIMAL value: '20.06 '
 select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
 cast(rtrim(ltrim('  20.06 ')) as decimal(19,2))
 20.06
@@ -2915,7 +2919,7 @@ format(rpad('111111111.1',
 1111111,
 '999999999999999999999999999999999999999999'),0,'be_BY')
 ;
-DO
+SELECT
 round(
 concat( (
 coalesce( (
@@ -2930,9 +2934,14 @@ sha1('P'),
 )
 )
 )
-);
+) AS r;
+r
+0
 Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9'
 Warning	1292	Truncated incorrect DECIMAL value: '[.DC2.]'
+Warning	1292	Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9'
+Warning	1292	Truncated incorrect DOUBLE value: '0.000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'
 SET @@global.max_allowed_packet:= @tmp_max;
 SELECT @tmp_max:= @@global.max_allowed_packet;
 @tmp_max:= @@global.max_allowed_packet
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index c0cf6d45ae2..b562aba13c9 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -163,6 +163,8 @@ Saturday	5
 select monthname("1972-03-04"),monthname("1972-03-04")+0;
 monthname("1972-03-04")	monthname("1972-03-04")+0
 March	0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'March'
 select time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
 time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T')	date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00	00|12|0|12|00|AM|12:00:00 AM|00|00:00:00
@@ -1966,6 +1968,9 @@ SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1;
 a
 2000-02-23
 2005-05-04
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 DROP TABLE t1;
 CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
@@ -2015,22 +2020,37 @@ SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))*1;
 a
 2000-02-23
 2005-05-04
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')))*1;
 a
 2005-05-04
 2000-02-23
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')));
 a
 2005-05-04
 2000-02-23
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 SELECT * FROM t1 GROUP BY ABS(FROM_UNIXTIME(concat(a,'10')));
 a
 2000-02-23
 2005-05-04
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 SELECT * FROM t1 GROUP BY @a:=(FROM_UNIXTIME(concat(a,'10'))*1);
 a
 2000-02-23
 2005-05-04
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 DROP TABLE t1;
 SET TIME_ZONE='+02:00';
 #
@@ -2042,10 +2062,18 @@ SELECT a, FROM_UNIXTIME(CONCAT(a,'10')) AS f1, FROM_UNIXTIME(CONCAT(a,'10'))+0 A
 a	f1	f2
 2005-05-04	1970-01-01 02:33:25	19700101023325.000000
 2000-02-23	1970-01-01 02:33:20	19700101023320.000000
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 SELECT * FROM t1 GROUP BY FROM_UNIXTIME(CONCAT(a,'10'))+0;
 a
 2000-02-23
 2005-05-04
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 DROP TABLE t1;
 CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
@@ -2053,16 +2081,28 @@ SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))/1;
 a
 2000-02-23
 2005-05-04
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2000-02-2310'
 DROP TABLE t1;
 CREATE TABLE t1 (a DATE);
 INSERT INTO t1 VALUES ('2005-05-04');
 SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1;
 f2
 0.000000
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
 SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1;
 f2
 8
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
 CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1;
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
+Warning	1292	Truncated incorrect DECIMAL value: '2005-05-0410'
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index a937cc960f3..d8b5ffaf330 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2094,6 +2094,9 @@ f1	f2	f3	f3
 4	0	0	0
 4	0	0	0
 0	NULL	NULL	NULL
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'foo'
+Warning	1292	Truncated incorrect DOUBLE value: 'foo'
 DROP TABLE t1, t2, t3, t4;
 set @tmp803457=@@optimizer_switch;
 #
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 73dd69681d2..0fd9ab322e5 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2108,6 +2108,9 @@ f1	f2	f3	f3
 4	0	0	0
 4	0	0	0
 0	NULL	NULL	NULL
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'foo'
+Warning	1292	Truncated incorrect DOUBLE value: 'foo'
 DROP TABLE t1, t2, t3, t4;
 set @tmp803457=@@optimizer_switch;
 #
diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result
index b6f0f048c68..41dbc744228 100644
--- a/mysql-test/r/xml.result
+++ b/mysql-test/r/xml.result
@@ -1184,6 +1184,8 @@ aa bb
 SELECT ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)');
 ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)')
 
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: ''
 #
 # Bug#62429 XML: ExtractValue, UpdateXML max arg length 127 chars
 #
@@ -1243,6 +1245,8 @@ DROP TABLE t1;
 SELECT ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)') AS e;
 e
 
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: ''
 SELECT ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,/a/c)') AS e;
 e
 bc
diff --git a/mysql-test/suite/rpl/r/rpl_row_create_select.result b/mysql-test/suite/rpl/r/rpl_row_create_select.result
index ac95d50df9d..81bbd3bc2fd 100644
--- a/mysql-test/suite/rpl/r/rpl_row_create_select.result
+++ b/mysql-test/suite/rpl/r/rpl_row_create_select.result
@@ -8,7 +8,7 @@ include/master-slave.inc
 #value of 21.
 CREATE TABLE t1 AS SELECT REPEAT('A', 1000) DIV 1 AS a;
 Warnings:
-Warning	1918	Encountered illegal value '' when converting to DECIMAL
+Warning	1292	Truncated incorrect DECIMAL value: 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -16,7 +16,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 CREATE TABLE t2 AS SELECT CONVERT(REPEAT('A', 255) USING UCS2) DIV 1 AS a;
 Warnings:
-Warning	1918	Encountered illegal value '' when converting to DECIMAL
+Warning	1292	Truncated incorrect DECIMAL value: 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
diff --git a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test
index 866e387cddb..575fdb2e89d 100644
--- a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test
+++ b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test
@@ -149,7 +149,9 @@ insert into t1 select "yesterday_24_";
 
 create table t2 ENGINE=MyISAM select rpad(UUID(),100,' ');
 create table t3 select 1 union select UUID();
+--disable_warnings
 create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
+--enable_warnings
 create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
 # what if UUID() is first:
 --disable_warnings
diff --git a/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test b/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
index 70a2063c23c..57954698a2c 100644
--- a/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
+++ b/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
@@ -192,7 +192,9 @@ DROP TEMPORARY TABLE t1;
 # INSERT INTO t1 VALUES(1);
 
 --echo # The rows event will binlogged after 'INSERT INTO t1 VALUES(1)'
+--disable_warnings
 INSERT INTO t1 VALUES(uuid()+0);
+--enable_warnings
 COMMIT;
 
 source include/show_binlog_events.inc;
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 13f9f5c9b6d..2875a2bfcb4 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -737,7 +737,10 @@ DROP TABLE t1;
 --echo #
 --echo # Bug#59648 my_strtoll10_mb2: Assertion `(*endptr - s) % 2 == 0' failed.
 --echo #
+--disable_warnings
+# Enable warnings when "MDEV-8844 Unreadable control characters printed as is in warnings" is fixed
 SELECT HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850));
+--enable_warnings
 SELECT CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED);
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 14af10b0320..511f1f31736 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1549,7 +1549,12 @@ format(rpad('111111111.1',
              '999999999999999999999999999999999999999999'),0,'be_BY')
 ;
 
-DO
+#
+# Originally it was DO instead of SELECT here.
+# But in mysqltest --disable_prepare_warnings affects SELECT queries only
+# and can't suppress prepare time warnings for DO.
+#
+SELECT
 round(
   concat( (
     coalesce( (
@@ -1564,7 +1569,7 @@ round(
           )
     )
   )
-);
+) AS r;
 
 --connection conn1
 SET @@global.max_allowed_packet:= @tmp_max;