Bug #37443 Simple math operators do not work when using int constants with DECIMAL values.

• Submitted: 17 Jun 2008 9:36
• Modified: 17 Jun 2008 14:02
• Reporter: Heribert Steuer
• Status: Duplicate
• Category: MySQL Server: Data Types
• Severity: S1 (Critical)
• Version: 5.0.51a
• OS: FreeBSD
• CPU Architecture: Any
• Tags: decimal, INT, Math, multiplication

[17 Jun 2008 9:36] Heribert Steuer

Description:
Simple math operators do not work when using constants with DECIMAL values.
It seems that adding a simple "*1" to the end of the term results in a 
wrong result. Funny thing is that it works sometimes

It seems that casting does not work well in that situation.

How to repeat:
mysql> create table test (d1 decimal(50,30), d2 decimal (50,30), i1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (d1,d2,i1) values (1,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT d1/10*d2 FROM test;
+----------------------------------+
| d1/10*d2                         |
+----------------------------------+
| 0.200000000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

Here comes the funny part .... 

mysql> SELECT d1/10*d2*1 FROM test;
+----------------------------------+
| d1/10*d2*1                       |
+----------------------------------+
| 0.000000000000000000000000000000 |
+----------------------------------+
1 row in set (0.01 sec)

When we make mysql think the constants are floats it works well...

mysql> SELECT d1/10*d2*1.0 FROM test;
+----------------------------------+
| d1/10*d2*1.0                     |
+----------------------------------+
| 0.200000000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

The really weired thing is, that working with int constants works in that case...

mysql> SELECT d1/10*1 FROM test;
+----------------------------------+
| d1/10*1                          |
+----------------------------------+
| 0.100000000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

[17 Jun 2008 14:02] Sveta Smirnova

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #36270