| Bug #96087 | DIV + const expression return wrong data | ||
|---|---|---|---|
| Submitted: | 4 Jul 2019 3:06 | Modified: | 4 Jul 2019 9:04 |
| Reporter: | raolh rao | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.16, 5.7.26, 5.6.44 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Jul 2019 5:17]
MySQL Verification Team
Hello raolh rao, Thank you for the report and test case. regards, Umesh
[4 Jul 2019 5:20]
MySQL Verification Team
Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html. If you have any questions, please contact the MySQL community team.
[24 Jul 2019 5:26]
MySQL Verification Team
Hello Raolh, My apologies for the delay in getting back to you on this. Please note that in order to accept your contribution you should either have your own personal OCA signed (you can follow the procedure outlined in the "Contributions" tab of this bug report) or if you want us to include you in Alibaba’s employees covered by Alibaba company OCA then please get in touch with Roger Shang(who already tried to contact you and awaiting your confirmation). Once he gets your confirmation then he can formally forward your request to our community manager for the same. regards, Umesh
Description: precision of Field_new_decimal is too small, which lead decimal overflow then result is wrong. The reason of Field_new_decimal precision is too small is like this: precision = length - (scale > 0 ? 1 : 0) - (unsigned_flag || !length ? 0 : 1); you can find this in function my_decimal_length_to_precision() if unsigned_flag is false and scale ==0, then precision =length -1 But, this compute method is wrong，because a signed number is very possible have no Positive or negative symbols, so in this scene precision =length How to repeat: Drop table if exists random_select_42_tab0; CREATE TABLE `random_select_42_tab0` ( `col0` int(11) DEFAULT NULL, `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; insert into random_select_42_tab0 values(35, 97, 1); set session sql_mode=""; MySQL [test]> SELECT - - 5 + 96 DIV + col2, sum(col1) FROM random_select_42_tab0 ; +-----------------------+-----------+ | - - 5 + 96 DIV + col2 | sum(col1) | +-----------------------+-----------+ | 101 | 97 | +-----------------------+-----------+ MySQL [test]> select * from (SELECT - - 5 + 96 DIV + col2, sum(col1) FROM random_select_42_tab0) v1; +-----------------------+-----------+ | - - 5 + 96 DIV + col2 | sum(col1) | +-----------------------+-----------+ | 99 | 97 | +-----------------------+-----------+ Suggested fix: --- a/sql/item_func.h +++ b/sql/item_func.h class Item_func_int_div final : public Item_int_func { public: Item_func_int_div(Item *a, Item *b) : Item_int_func(a, b) {} Item_func_int_div(const POS &pos, Item *a, Item *b) : Item_int_func(pos, a, b) {} longlong val_int() override; const char *func_name() const override { return "DIV"; } bool resolve_type(THD *thd) override; void print(const THD *thd, String *str, enum_query_type query_type) const override { print_op(thd, str, query_type); } + uint decimal_precision() const override { + /* + * the precision of expression '46/2' is 2, but in Item::decimal_precision algrithm is: + * precision = length - (scale>0 ? 1:0 - (unsigned_flag || length>0 ? 1: 0)) + * that precision is 1, which is wrong. + */ + if ( args[0]->type() == Item::INT_ITEM) + return max_char_length() - (decimals>0 ? 1:0); + else + return Item::decimal_precision(); + } bool check_partition_func_processor(uchar *) override { return false; } bool check_function_as_value_generator(uchar *) override { return false; } };