Bug #76478 mod functions works incorrectly with arithmetic express

• Submitted: 25 Mar 2015 7:46
• Modified: 26 Mar 2015 2:59
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.22, 5.6.25, 5.5.44, 5.1.73
• OS: Linux
• CPU Architecture: Any

[25 Mar 2015 7:46] Su Dylan

Description:
There seems a bug here:

mysql> select 2 / 3 * 3 % 2;
+---------------+
| 2 / 3 * 3 % 2 |
+---------------+
|        2.0000 |
+---------------+
1 row in set (0.00 sec)

0 is expected.

How to repeat:
select 2 / 3 * 3 % 2;

[25 Mar 2015 11:31] Valeriy Kravchuk

This is easy to confirm:

mysql> select 2 / 3 * 3 % 2 as a, version();
+--------+------------+
| a      | version()  |
+--------+------------+
| 2.0000 | 5.6.23-log |
+--------+------------+
1 row in set (0.00 sec)

Older versions work as expected:

mysql> select 2 / 3 * 3 % 2 as a, version();
+------+-----------+
| a    | version() |
+------+-----------+
|    0 | 4.0.30    |
+------+-----------+
1 row in set (0.00 sec)

According to the manual (http://dev.mysql.com/doc/refman/5.6/en/operator-precedence.html) and common sense, older versions are right :)

[25 Mar 2015 11:54] Valeriy Kravchuk

This way it works in 5.6:

mysql>  select (cast(((2 / 3) * 3) as decimal) % 2);
+--------------------------------------+
| (cast(((2 / 3) * 3) as decimal) % 2) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

but the result was already decimal:

mysql> create view vv as select (((2 / 3) * 3)) as a;
Query OK, 0 rows affected (0.00 sec)

mysql> desc vv;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | decimal(6,4) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

[25 Mar 2015 12:25] MySQL Verification Team

Hello Su Dylan,

Thank you for the report.

Thanks,
Umesh

[25 Mar 2015 12:27] MySQL Verification Team

This seems to be exists from mysql-5.0.96 onwards

[26 Mar 2015 2:53] Su Dylan

If we use ROUND to show 16 decimal values, we will get '1.999999998'.
The result is kind of depending on the default precision.

mysql> select 2 / 3 * 3 % 2 , round( 2 / 3 * 3 % 2 , 16 ), round( 2 / 3 * 3 % 2 , 4 ), 1.999999998 % 2 , version();
+---------------+-----------------------------+----------------------------+-----------------+------------+
| 2 / 3 * 3 % 2 | round( 2 / 3 * 3 % 2 , 16 ) | round( 2 / 3 * 3 % 2 , 4 ) | 1.999999998 % 2 | version()  |
+---------------+-----------------------------+----------------------------+-----------------+------------+
|        2.0000 |          1.9999999980000000 |                     2.0000 |     1.999999998 | 5.6.22-log |
+---------------+-----------------------------+----------------------------+-----------------+------------+
1 row in set (0.00 sec)

So what will be the final decision? Will this be fixed in MySQL 5.x?

[26 Mar 2015 2:59] Su Dylan

With the display result by creating a view (from Valeriy ), the precision of 2/3*3 is decimal(6,4).

Then the following SQL gives the expected result:
mysql> select cast( 2/3*3 as decimal(6,4)) % 2;
+----------------------------------+
| cast( 2/3*3 as decimal(6,4)) % 2 |
+----------------------------------+
|                           0.0000 |
+----------------------------------+
1 row in set (0.00 sec)

Then we will expect 0(or 0.0000) for select 2 / 3 * 3 % 2.