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

Bug #37443	Simple math operators do not work when using int constants with DECIMAL values.
Submitted:	17 Jun 2008 11:36	Modified:	17 Jun 2008 16:02
Reporter:	Heribert Steuer
Status:	Duplicate
Category:	Server: Types	Severity:	S1 (Critical)
Version:	5.0.51a	OS:	FreeBSD
Assigned to:		Target Version:
Tags:	Math, multiplication, INT, decimal

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)

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