Bug #55537 | TRUNCATE() and ROUND() functions are inconsistent depending on (non)const params | ||
---|---|---|---|

Submitted: | 25 Jul 2010 16:37 | Modified: | 26 Jul 2010 16:45 |

Reporter: | Shlomi Noach (OCA) | Email Updates: | |

Status: | Verified | Impact on me: | |

Category: | MySQL Server: General | Severity: | S3 (Non-critical) |

Version: | 5.1.41-log, 4.1, 5.0, 5.1, 5.6.99 | OS: | Any |

Assigned to: | CPU Architecture: | Any | |

Tags: | ROUND(), TRUNCATE() |

[25 Jul 2010 16:37]
Shlomi Noach

[25 Jul 2010 17:20]
Valeriy Kravchuk

I think the following test shows what happens more clearly: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 --column- type test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.1.48-community MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select n, truncate(pi(), n), truncate(pi(),3) from n; Field 1: `n` Catalog: `def` Database: `test` Table: `n` Org_table: `n` Type: LONG Collation: binary (63) Length: 11 Max_length: 1 Decimals: 0 Flags: NUM Field 2: `truncate(pi(), n)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: DOUBLE Collation: binary (63) Length: 23 Max_length: 8 Decimals: 6 Flags: BINARY NUM Field 3: `truncate(pi(),3)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: DOUBLE Collation: binary (63) Length: 20 Max_length: 5 Decimals: 3 Flags: NOT_NULL BINARY NUM +------+-------------------+------------------+ | n | truncate(pi(), n) | truncate(pi(),3) | +------+-------------------+------------------+ | 1 | 3.100000 | 3.141 | | 2 | 3.140000 | 3.141 | | 3 | 3.141000 | 3.141 | | 4 | 3.141500 | 3.141 | | 5 | 3.141590 | 3.141 | +------+-------------------+------------------+ 5 rows in set (0.00 sec) Note decimals 6 vs 3 in columns metadata. I am not sure it is possible to get the same value for decimals in case of table column defined as INT, and I am not sure what is the bug here.

[25 Jul 2010 17:33]
Shlomi Noach

The bug is in that the parameter D is an INT, either where I provide it as const or as table column. They're both an INT. Why should it make for different results? By the same reasoning, why should SELECT x+1 be different from SELECT x+n FROM numbers WHERE n=1 (abstract example). There is a function, and it receives an INT parameter. IT could be a constant, a user variable, a row value, the result of a stored function, the result of a UDF, what have you. It should act consistently. I hope I'm clear on why this is a bug. Regards

[26 Jul 2010 7:30]
Sveta Smirnova

Thank you for the feedback. Verified as described.

[26 Jul 2010 16:17]
Peter Gulutzan

The bug report says results are "inconsistent" -- true.. The bug report says results are "wrong (according to documentation)" -- I'm not so sure. The MySQL Reference Manual says "The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal)." http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_round In this sentence "type" means integer or double or decimal, and nothing is said about precision scale etc. We're talking about non-standard functions, and in another DBMS I see that ROUND() can change such things. So it's up to MySQL to decide how to behave in this case: 1. leave it alone, an inconsistency is not a bug 2 the number of post-decimal positions is always the same as D, the second argument 3. the number of post-decimal positions is always the same as the number in X, the first argument I'd favour '3.', but as a feature request.

[26 Jul 2010 16:45]
Shlomi Noach

Hi Peter, With regard to ROUND(): "...The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal)..." > OK, but in my examples above, the first argument was always PI(), unchanged. So why is this relevant? "...and nothing is said about precision scale etc." > Then how about TRUNCATE() ? The docs say: "Returns the number X, truncated to D decimal places" The examples show: mysql> SELECT TRUNCATE(-1.999,1); -> -1.9 This isn't a math theorem; I cannot follow math logic to the point of Q.E.D. But it is easy to deduce that TRUNCATE() removes digits -- not turn them into zeros. This is what a reasonable reader understands, and this is what the example shows. I suppose we can rewrite the documentation so that the behavior is as expected; but really -- this is just a bug. Best regards

[27 Jul 2010 1:24]
Peter Gulutzan

>> With regard to ROUND(): >> "...The return type is the same type as that of the first >> argument (assuming that it is integer, double, or decimal)..." > OK, but in my examples above, the first argument was always PI(), unchanged. So why is > this relevant? Because PI() is a DOUBLE, and ROUND(PI(),...) is a DOUBLE. >>"...and nothing is said about precision scale etc." > > Then how about TRUNCATE() ? > The docs say: "Returns the number X, truncated to D decimal places" > The examples show: > mysql> SELECT TRUNCATE(-1.999,1); > -> -1.9 > This isn't a math theorem; I cannot follow math logic to the point of Q.E.D. But it is > easy to deduce that TRUNCATE() removes digits -- not turn them into zeros. This is what a > reasonable reader understands, and this is what the example shows. > I suppose we can rewrite the documentation so that the behavior is as expected; but > really -- this is just a bug. We're better off if we can predict an expression's data type + precision + scale + length, in advance, and consistently. Sometimes it's not easy to do so with non-constant inputs, and we might come up with something less than ideal. We do see that there's inconsistency.