MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings

15 files changed, 92 insertions, 0 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 04f7b262bac..cef0a668d72 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -2273,3 +2273,13 @@ t2_id	GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1)
 EXECUTE stmt;
 t2_id	GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1)
 DROP TABLE t1,t2,t3,t4,t5,t6;
+#
+# MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings
+#
+SELECT MAX('x') << 1, CAST(MAX('x') AS DOUBLE), CAST(MAX('x') AS DECIMAL);
+MAX('x') << 1	CAST(MAX('x') AS DOUBLE)	CAST(MAX('x') AS DECIMAL)
+0	0	0
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'x'
+Warning	1292	Truncated incorrect DOUBLE value: 'x'
+Warning	1292	Truncated incorrect DECIMAL value: 'x'
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index b562aba13c9..cd60996e590 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1718,13 +1718,17 @@ Warnings:
 Warning	1292	Truncated incorrect INTEGER value: '%Z'
 Warning	1292	Truncated incorrect DOUBLE value: ''
 Warning	1292	Incorrect datetime value: '0000-00-00'
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
 create table t1(a time);
 insert into t1 values ('00:00:00'),('00:01:00');
 select 1 from t1 where 1 < some (select cast(a as datetime) from t1);
 1
 1
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '2001-01-01 00:01:00'
 drop table t1;
+SET timestamp=DEFAULT;
 select time('10:10:10') > 10;
 time('10:10:10') > 10
 1
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 9e337f6b5aa..69a14b20787 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1661,6 +1661,9 @@ INSERT INTO g1 VALUES ('a'),('a');
 SELECT 1 FROM g1 WHERE a >= ANY
 (SELECT 1 FROM g1 WHERE a = geomfromtext('') OR a) ;
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 DROP TABLE g1;
 #
 # Bug#16451878 GEOMETRY QUERY CRASHES SERVER
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 689edfa6dab..baf6c8c7e73 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -130,12 +130,16 @@ Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 (SELECT 1.5,2,'a') = ROW(1.5,'2','a')
 1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'c'
 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 ERROR 21000: Operand should contain 1 column(s)
 SELECT 1 as a,(SELECT a+a) b,(SELECT b);
@@ -4665,6 +4669,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
@@ -5933,6 +5939,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index b201ac6a44c..976d03946e6 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -545,6 +545,10 @@ Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`
 select * from t1 group by (a1 in (select col from columns));
 a1	a2
 1 - 00	2 - 00
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '1 - 00'
+Warning	1292	Truncated incorrect DOUBLE value: '1 - 01'
+Warning	1292	Truncated incorrect DOUBLE value: '1 - 02'
 /* ORDER BY clause */
 explain extended
 select * from t1 order by (select col from columns limit 1);
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 195f377e7ff..943b6ff0d02 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -134,12 +134,16 @@ Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 (SELECT 1.5,2,'a') = ROW(1.5,'2','a')
 1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'c'
 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 ERROR 21000: Operand should contain 1 column(s)
 SELECT 1 as a,(SELECT a+a) b,(SELECT b);
@@ -4667,6 +4671,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
@@ -5933,6 +5939,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 26794b452cc..0716afd3f0f 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -137,12 +137,16 @@ Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 (SELECT 1.5,2,'a') = ROW(1.5,'2','a')
 1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'c'
 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 ERROR 21000: Operand should contain 1 column(s)
 SELECT 1 as a,(SELECT a+a) b,(SELECT b);
@@ -4665,6 +4669,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
@@ -5930,6 +5936,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 9a43e64554e..59f36c801cb 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -133,12 +133,16 @@ Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 (SELECT 1.5,2,'a') = ROW(1.5,'2','a')
 1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'c'
 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 ERROR 21000: Operand should contain 1 column(s)
 SELECT 1 as a,(SELECT a+a) b,(SELECT b);
@@ -4661,6 +4665,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
@@ -5926,6 +5932,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 0a88e26bd2d..ff54b383067 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -136,12 +136,16 @@ Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 (SELECT 1.5,2,'a') = ROW(1.5,'2','a')
 1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'c'
 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 ERROR 21000: Operand should contain 1 column(s)
 SELECT 1 as a,(SELECT a+a) b,(SELECT b);
@@ -4671,6 +4675,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
@@ -5939,6 +5945,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 142f9f679bf..a3d914cb6df 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -133,12 +133,16 @@ Warning	1292	Truncated incorrect DOUBLE value: '1.5b'
 SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
 (SELECT 'b',2,'a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'b'
 SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');
 (SELECT 1.5,2,'a') = ROW(1.5,'2','a')
 1
 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 (SELECT 1.5,'c','a') = ROW(1.5,2,'a')
 0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'c'
 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);
 ERROR 21000: Operand should contain 1 column(s)
 SELECT 1 as a,(SELECT a+a) b,(SELECT b);
@@ -4661,6 +4665,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
@@ -5926,6 +5932,8 @@ SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
 1
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
 1
 1
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index d8b5ffaf330..18a79333e10 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2288,8 +2288,14 @@ CREATE TABLE t3 (a int, b int) ;
 PREPARE st1 FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.a != 0 AND ('j','r') IN ( SELECT b,a FROM t3)";
 EXECUTE st1;
 a	a
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'j'
+Warning	1292	Truncated incorrect DOUBLE value: 'r'
 EXECUTE st1;
 a	a
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'j'
+Warning	1292	Truncated incorrect DOUBLE value: 'r'
 DROP TABLE t1, t2, t3;
 #
 # BUG#849776: Wrong result with semijoin + "Impossible where"
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 0fd9ab322e5..13dc990b806 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2302,8 +2302,14 @@ CREATE TABLE t3 (a int, b int) ;
 PREPARE st1 FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.a != 0 AND ('j','r') IN ( SELECT b,a FROM t3)";
 EXECUTE st1;
 a	a
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'j'
+Warning	1292	Truncated incorrect DOUBLE value: 'r'
 EXECUTE st1;
 a	a
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'j'
+Warning	1292	Truncated incorrect DOUBLE value: 'r'
 DROP TABLE t1, t2, t3;
 #
 # BUG#849776: Wrong result with semijoin + "Impossible where"
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 2dce6569d67..d5e9fc776f8 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -566,6 +566,10 @@ Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`
 select * from t1 group by (a1 in (select col from columns));
 a1	a2
 1 - 00	2 - 00
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '1 - 00'
+Warning	1292	Truncated incorrect DOUBLE value: '1 - 01'
+Warning	1292	Truncated incorrect DOUBLE value: '1 - 02'
 /* ORDER BY clause */
 explain extended
 select * from t1 order by (select col from columns limit 1);
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index bd3ed4ad32d..9a71df4dc4a 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -1565,3 +1565,8 @@ EXECUTE stmt;
 EXECUTE stmt;
 
 DROP TABLE t1,t2,t3,t4,t5,t6;
+
+--echo #
+--echo # MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings
+--echo #
+SELECT MAX('x') << 1, CAST(MAX('x') AS DOUBLE), CAST(MAX('x') AS DECIMAL);
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 3fb87e91b17..45db61e396c 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1036,10 +1036,12 @@ SET time_zone=DEFAULT;
 
 SELECT min(timestampadd(month, 1>'', from_days('%Z')));
 
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
 create table t1(a time);
 insert into t1 values ('00:00:00'),('00:01:00');
 select 1 from t1 where 1 < some (select cast(a as datetime) from t1);
 drop table t1;
+SET timestamp=DEFAULT;
 
 select time('10:10:10') > 10;
 select time('10:10:10') > 1010;