From b7d22a843e36cd5b8695f8ac2b92789d1cf50e4f Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 16 May 2019 10:16:09 +0400 Subject: MDEV-16872 Add CAST(expr AS FLOAT) --- mysql-test/main/cast.result | 4 +-- mysql-test/main/dyncol.result | 4 +-- mysql-test/main/gis.result | 6 ++++ mysql-test/main/gis.test | 6 ++++ mysql-test/main/type_float.result | 68 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/type_float.test | 39 ++++++++++++++++++++++ 6 files changed, 123 insertions(+), 4 deletions(-) (limited to 'mysql-test') 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 @@ -615,6 +615,45 @@ SELECT * FROM t2; 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 # -- cgit v1.2.1