diff options
author | Alexander Barkov <bar@mariadb.com> | 2019-05-16 10:16:09 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.com> | 2019-05-16 10:16:32 +0400 |
commit | b7d22a843e36cd5b8695f8ac2b92789d1cf50e4f (patch) | |
tree | df7f8b4d747ecec69424e24fe989aeda0df16b44 | |
parent | e0e805759f5403352debb9a2c97a4c0ee8f3c10f (diff) | |
download | mariadb-git-b7d22a843e36cd5b8695f8ac2b92789d1cf50e4f.tar.gz |
MDEV-16872 Add CAST(expr AS FLOAT)
-rw-r--r-- | mysql-test/main/cast.result | 4 | ||||
-rw-r--r-- | mysql-test/main/dyncol.result | 4 | ||||
-rw-r--r-- | mysql-test/main/gis.result | 6 | ||||
-rw-r--r-- | mysql-test/main/gis.test | 6 | ||||
-rw-r--r-- | mysql-test/main/type_float.result | 68 | ||||
-rw-r--r-- | mysql-test/main/type_float.test | 39 | ||||
-rw-r--r-- | sql/item_func.cc | 20 | ||||
-rw-r--r-- | sql/item_func.h | 53 | ||||
-rw-r--r-- | sql/sql_type.cc | 24 | ||||
-rw-r--r-- | sql/sql_type.h | 11 | ||||
-rw-r--r-- | sql/sql_yacc.yy | 1 | ||||
-rw-r--r-- | sql/sql_yacc_ora.yy | 1 |
12 files changed, 221 insertions, 16 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 # diff --git a/sql/item_func.cc b/sql/item_func.cc index 5a1be3318f7..a030d2f8681 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1133,19 +1133,26 @@ void Item_decimal_typecast::print(String *str, enum_query_type query_type) } -double Item_double_typecast::val_real() +double Item_real_typecast::val_real_with_truncate(double max_value) { int error; double tmp= args[0]->val_real(); if ((null_value= args[0]->null_value)) return 0.0; - if (unlikely((error= truncate_double(&tmp, max_length, decimals, 0, - DBL_MAX)))) + if (unlikely((error= truncate_double(&tmp, max_length, decimals, + false/*unsigned_flag*/, max_value)))) { + /* + We don't want automatic escalation from a warning to an error + in this scenario: + INSERT INTO t1 (float_field) VALUES (CAST(1e100 AS FLOAT)); + The above statement should work even in the strict mode. + So let's use a note rather than a warning. + */ THD *thd= current_thd; push_warning_printf(thd, - Sql_condition::WARN_LEVEL_WARN, + Sql_condition::WARN_LEVEL_NOTE, ER_WARN_DATA_OUT_OF_RANGE, ER_THD(thd, ER_WARN_DATA_OUT_OF_RANGE), name.str, (ulong) 1); @@ -1159,14 +1166,15 @@ double Item_double_typecast::val_real() } -void Item_double_typecast::print(String *str, enum_query_type query_type) +void Item_real_typecast::print(String *str, enum_query_type query_type) { char len_buf[20*3 + 1]; char *end; str->append(STRING_WITH_LEN("cast(")); args[0]->print(str, query_type); - str->append(STRING_WITH_LEN(" as double")); + str->append(STRING_WITH_LEN(" as ")); + str->append(type_handler()->name().ptr()); if (decimals != NOT_FIXED_DEC) { str->append('('); diff --git a/sql/item_func.h b/sql/item_func.h index 9e90c0d9200..6345dd41f71 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1003,25 +1003,66 @@ public: }; -class Item_double_typecast :public Item_real_func +class Item_real_typecast: public Item_real_func { +protected: + double val_real_with_truncate(double max_value); public: - Item_double_typecast(THD *thd, Item *a, uint len, uint dec): - Item_real_func(thd, a) + Item_real_typecast(THD *thd, Item *a, uint len, uint dec) + :Item_real_func(thd, a) { decimals= (uint8) dec; max_length= (uint32) len; } - double val_real(); + bool need_parentheses_in_default() { return true; } + void print(String *str, enum_query_type query_type); void fix_length_and_dec_generic() { maybe_null= 1; } +}; + + +class Item_float_typecast :public Item_real_typecast +{ +public: + Item_float_typecast(THD *thd, Item *a) + :Item_real_typecast(thd, a, MAX_FLOAT_STR_LENGTH, NOT_FIXED_DEC) + { } + const Type_handler *type_handler() const { return &type_handler_float; } + bool fix_length_and_dec() + { + return + args[0]->type_handler()->Item_float_typecast_fix_length_and_dec(this); + } + const char *func_name() const { return "float_typecast"; } + double val_real() + { + return (double) (float) val_real_with_truncate(FLT_MAX); + } + String *val_str(String*str) + { + Float nr(Item_float_typecast::val_real()); + if (null_value) + return 0; + nr.to_string(str, decimals); + return str; + } + Item *get_copy(THD *thd) + { return get_item_copy<Item_float_typecast>(thd, this); } +}; + + +class Item_double_typecast :public Item_real_typecast +{ +public: + Item_double_typecast(THD *thd, Item *a, uint len, uint dec): + Item_real_typecast(thd, a, len, dec) + { } bool fix_length_and_dec() { return args[0]->type_handler()->Item_double_typecast_fix_length_and_dec(this); } const char *func_name() const { return "double_typecast"; } - virtual void print(String *str, enum_query_type query_type); - bool need_parentheses_in_default() { return true; } + double val_real() { return val_real_with_truncate(DBL_MAX); } Item *get_copy(THD *thd) { return get_item_copy<Item_double_typecast>(thd, this); } }; diff --git a/sql/sql_type.cc b/sql/sql_type.cc index b984dd8eb3b..d07296aad7e 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -4643,6 +4643,14 @@ bool Type_handler:: bool Type_handler:: + Item_float_typecast_fix_length_and_dec(Item_float_typecast *item) const +{ + item->fix_length_and_dec_generic(); + return false; +} + + +bool Type_handler:: Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *item) const { item->fix_length_and_dec_generic(); @@ -4732,6 +4740,13 @@ bool Type_handler_geometry:: bool Type_handler_geometry:: + Item_float_typecast_fix_length_and_dec(Item_float_typecast *item) const +{ + return Item_func_or_sum_illegal_param(item); +} + + +bool Type_handler_geometry:: Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *item) const { return Item_func_or_sum_illegal_param(item); @@ -5760,6 +5775,15 @@ Item *Type_handler_double:: } +Item *Type_handler_float:: + create_typecast_item(THD *thd, Item *item, + const Type_cast_attributes &attr) const +{ + DBUG_ASSERT(!attr.length_specified()); + return new (thd->mem_root) Item_float_typecast(thd, item); +} + + Item *Type_handler_long_blob:: create_typecast_item(THD *thd, Item *item, const Type_cast_attributes &attr) const diff --git a/sql/sql_type.h b/sql/sql_type.h index b317832880a..ef1a44a420c 100644 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@ -51,6 +51,7 @@ class Item_func_neg; class Item_func_signed; class Item_func_unsigned; class Item_double_typecast; +class Item_float_typecast; class Item_decimal_typecast; class Item_char_typecast; class Item_time_typecast; @@ -1391,6 +1392,8 @@ public: virtual bool Item_double_typecast_fix_length_and_dec(Item_double_typecast *item) const; virtual bool + Item_float_typecast_fix_length_and_dec(Item_float_typecast *item) const; + virtual bool Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *item) const; virtual bool Item_char_typecast_fix_length_and_dec(Item_char_typecast *item) const; @@ -1695,6 +1698,11 @@ public: DBUG_ASSERT(0); return true; } + bool Item_float_typecast_fix_length_and_dec(Item_float_typecast *) const + { + DBUG_ASSERT(0); + return true; + } bool Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *) const { DBUG_ASSERT(0); @@ -2590,6 +2598,8 @@ public: bool type_can_have_auto_increment_attribute() const { return true; } uint32 max_display_length(const Item *item) const { return 25; } uint32 calc_pack_length(uint32 length) const { return sizeof(float); } + Item *create_typecast_item(THD *thd, Item *item, + const Type_cast_attributes &attr) const; bool Item_send(Item *item, Protocol *protocol, st_value *buf) const { return Item_send_float(item, protocol, buf); @@ -3366,6 +3376,7 @@ public: bool Item_func_signed_fix_length_and_dec(Item_func_signed *) const; bool Item_func_unsigned_fix_length_and_dec(Item_func_unsigned *) const; bool Item_double_typecast_fix_length_and_dec(Item_double_typecast *) const; + bool Item_float_typecast_fix_length_and_dec(Item_float_typecast *) const; bool Item_decimal_typecast_fix_length_and_dec(Item_decimal_typecast *) const; bool Item_char_typecast_fix_length_and_dec(Item_char_typecast *) const; bool Item_time_typecast_fix_length_and_dec(Item_time_typecast *) const; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 91678afc81c..f89984d848f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11728,6 +11728,7 @@ cast_type_numeric: | UNSIGNED { $$.set(&type_handler_ulonglong); } | UNSIGNED INT_SYM { $$.set(&type_handler_ulonglong); } | DECIMAL_SYM float_options { $$.set(&type_handler_newdecimal, $2); } + | FLOAT_SYM { $$.set(&type_handler_float); } | DOUBLE_SYM opt_precision { $$.set(&type_handler_double, $2); } ; diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 40315c05056..6a05423ebc3 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -11674,6 +11674,7 @@ cast_type_numeric: | UNSIGNED { $$.set(&type_handler_ulonglong); } | UNSIGNED INT_SYM { $$.set(&type_handler_ulonglong); } | DECIMAL_SYM float_options { $$.set(&type_handler_newdecimal, $2); } + | FLOAT_SYM { $$.set(&type_handler_float); } | DOUBLE_SYM opt_precision { $$.set(&type_handler_double, $2); } ; |