MDEV-16872 Add CAST(expr AS FLOAT)

6 files changed, 123 insertions, 4 deletions

diff --git a/mysql-test/main/cast.result b/mysql-test/main/cast.result
index 1dc6bbbf9e7..d500ef4fa1d 100644
--- a/mysql-test/main/cast.result
+++ b/mysql-test/main/cast.result
@@ -271,12 +271,12 @@ select cast(1000 as double(5,2));
 cast(1000 as double(5,2))
 999.99
 Warnings:
-Warning	1264	Out of range value for column 'cast(1000 as double(5,2))' at row 1
+Note	1264	Out of range value for column 'cast(1000 as double(5,2))' at row 1
 select cast(-1000 as double(5,2));
 cast(-1000 as double(5,2))
 -999.99
 Warnings:
-Warning	1264	Out of range value for column 'cast(-1000 as double(5,2))' at row 1
+Note	1264	Out of range value for column 'cast(-1000 as double(5,2))' at row 1
 select cast(010203101112.121314 as datetime);
 cast(010203101112.121314 as datetime)
 2001-02-03 10:11:12
diff --git a/mysql-test/main/dyncol.result b/mysql-test/main/dyncol.result
index 7a5eeac67cc..d233d0d3be2 100644
--- a/mysql-test/main/dyncol.result
+++ b/mysql-test/main/dyncol.result
@@ -501,12 +501,12 @@ select column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2));
 column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))
 999.99
 Warnings:
-Warning	1264	Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))' at row 1
+Note	1264	Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))' at row 1
 select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2));
 column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))
 9.99
 Warnings:
-Warning	1264	Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))' at row 1
+Note	1264	Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))' at row 1
 #
 # column get decimal
 #
diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result
index fb8919ef061..b154df24585 100644
--- a/mysql-test/main/gis.result
+++ b/mysql-test/main/gis.result
@@ -4038,6 +4038,8 @@ SELECT CAST(POINT(1,1) AS SIGNED) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed'
 SELECT CAST(POINT(1,1) AS UNSIGNED) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned'
+SELECT CAST(POINT(1,1) AS FLOAT) FROM t1;
+ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast'
 SELECT CAST(POINT(1,1) AS DOUBLE) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast'
 SELECT CAST(POINT(1,1) AS DECIMAL(10,1)) FROM t1;
@@ -4054,6 +4056,8 @@ SELECT CAST(a AS SIGNED) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed'
 SELECT CAST(a AS UNSIGNED) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned'
+SELECT CAST(a AS FLOAT) FROM t1;
+ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast'
 SELECT CAST(a AS DOUBLE) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast'
 SELECT CAST(a AS DECIMAL(10,1)) FROM t1;
@@ -4070,6 +4074,8 @@ SELECT CAST(COALESCE(a) AS SIGNED) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed'
 SELECT CAST(COALESCE(a) AS UNSIGNED) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned'
+SELECT CAST(COALESCE(a) AS FLOAT) FROM t1;
+ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast'
 SELECT CAST(COALESCE(a) AS DOUBLE) FROM t1;
 ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast'
 SELECT CAST(COALESCE(a) AS DECIMAL(10,1)) FROM t1;
diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test
index c4115f7ed54..ff09a098107 100644
--- a/mysql-test/main/gis.test
+++ b/mysql-test/main/gis.test
@@ -2109,6 +2109,8 @@ SELECT CAST(POINT(1,1) AS SIGNED) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(POINT(1,1) AS UNSIGNED) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(POINT(1,1) AS FLOAT) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(POINT(1,1) AS DOUBLE) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(POINT(1,1) AS DECIMAL(10,1)) FROM t1;
@@ -2126,6 +2128,8 @@ SELECT CAST(a AS SIGNED) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(a AS UNSIGNED) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(a AS FLOAT) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(a AS DOUBLE) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(a AS DECIMAL(10,1)) FROM t1;
@@ -2143,6 +2147,8 @@ SELECT CAST(COALESCE(a) AS SIGNED) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(COALESCE(a) AS UNSIGNED) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CAST(COALESCE(a) AS FLOAT) FROM t1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(COALESCE(a) AS DOUBLE) FROM t1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CAST(COALESCE(a) AS DECIMAL(10,1)) FROM t1;
diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result
index 8e97ab497ea..217fa3aff2a 100644
--- a/mysql-test/main/type_float.result
+++ b/mysql-test/main/type_float.result
@@ -875,5 +875,73 @@ CONCAT(a)	CONCAT(COALESCE(a))	CONCAT(LEAST(a,a))	CONCAT(MAX(a))	c
 0.671437	0.671437	0.671437	0.671437	0.671437
 DROP TABLE t1, t2;
 #
+# MDEV-16872 Add CAST(expr AS FLOAT)
+#
+SELECT CAST(0.671437 AS FLOAT), CONCAT(CAST(0.671437 AS FLOAT));
+CAST(0.671437 AS FLOAT)	CONCAT(CAST(0.671437 AS FLOAT))
+0.671437	0.671437
+SELECT CAST(1e40 AS FLOAT), CONCAT(CAST(1e40 AS FLOAT));
+CAST(1e40 AS FLOAT)	CONCAT(CAST(1e40 AS FLOAT))
+3.40282e38	3.40282e38
+Warnings:
+Note	1264	Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1
+Note	1264	Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1
+SELECT CAST(-1e40 AS FLOAT), CONCAT(CAST(-1e40 AS FLOAT));
+CAST(-1e40 AS FLOAT)	CONCAT(CAST(-1e40 AS FLOAT))
+-3.40282e38	-3.40282e38
+Warnings:
+Note	1264	Out of range value for column 'CAST(-1e40 AS FLOAT)' at row 1
+Note	1264	Out of range value for column 'CAST(-1e40 AS FLOAT)' at row 1
+SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
+CREATE TABLE t1 (a FLOAT);
+INSERT INTO t1 VALUES (CAST(1e40 AS FLOAT));
+Warnings:
+Note	1264	Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1
+SELECT * FROM t1;
+a
+3.40282e38
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+EXPLAIN EXTENDED SELECT CAST(0.671437 AS FLOAT);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select cast(0.671437 as float) AS `CAST(0.671437 AS FLOAT)`
+CREATE TABLE t1 AS SELECT CAST(0.671437 AS FLOAT) AS c1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1
+0.671437
+DROP TABLE t1;
+CREATE TABLE t1 (a FLOAT);
+CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(CAST(a AS FLOAT)) AS c2 FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(12) DEFAULT NULL,
+  `c2` varchar(12) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a FLOAT DEFAULT CAST(0.671437 AS FLOAT));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` float DEFAULT (cast(0.671437 as float))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a FLOAT);
+INSERT INTO t1 VALUES (1, 0.671437),(2, 0.671437);
+DELETE FROM t1 WHERE a=0.671437;
+SELECT * FROM t1;
+id	a
+1	0.671437
+2	0.671437
+DELETE FROM t1 WHERE a=CAST(0.671437 AS FLOAT);
+DROP TABLE t1;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test
index 5f9958dbe2b..65c8130b5db 100644
--- a/mysql-test/main/type_float.test
+++ b/mysql-test/main/type_float.test
@@ -616,5 +616,44 @@ DROP TABLE t1, t2;
 
 
 --echo #
+--echo # MDEV-16872 Add CAST(expr AS FLOAT)
+--echo #
+
+SELECT CAST(0.671437 AS FLOAT), CONCAT(CAST(0.671437 AS FLOAT));
+SELECT CAST(1e40 AS FLOAT), CONCAT(CAST(1e40 AS FLOAT));
+SELECT CAST(-1e40 AS FLOAT), CONCAT(CAST(-1e40 AS FLOAT));
+
+SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
+CREATE TABLE t1 (a FLOAT);
+INSERT INTO t1 VALUES (CAST(1e40 AS FLOAT));
+SELECT * FROM t1;
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+
+EXPLAIN EXTENDED SELECT CAST(0.671437 AS FLOAT);
+
+CREATE TABLE t1 AS SELECT CAST(0.671437 AS FLOAT) AS c1;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a FLOAT);
+CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(CAST(a AS FLOAT)) AS c2 FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a FLOAT DEFAULT CAST(0.671437 AS FLOAT));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a FLOAT);
+INSERT INTO t1 VALUES (1, 0.671437),(2, 0.671437);
+DELETE FROM t1 WHERE a=0.671437;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE a=CAST(0.671437 AS FLOAT);
+DROP TABLE t1;
+
+
+--echo #
 --echo # End of 10.3 tests
 --echo #