[MDEV-21020] ROUND(X,D) function does not have effect when decimal is a column Created: 2019-11-09  Updated: 2020-11-10  Resolved: 2019-11-19

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.1, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Tore Andersson Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

any, docker mariadb/server default

Attachments: File rounding_bug.sql    

 Description   

The ROUND(X,D) function when D is a column from a joined table will cause the ROUND function to not have the proper effect. The value will actually be rounded numerically, but the number of decimals printed, even when there is only a single outout row.

I have two tables, one with currencies and the number of decimals the currency trade with.

  • One with exhange rate.
  • Second with the number of decimal available in the currency.
    Example USD and NOK have two decimals, Japanese YEN (JPY) does not have any decimals.

I make a query which convert the currency and given the rate and rounds it to proper number of decimals.

SELECT
 
  ROUND((100 / exchange_rate), c.num_of_decimal),
 
  target_currency,
 
  num_of_decimal
 
FROM exchange_rate er
 
INNER JOIN currency c ON er.target_currency = c.currency_id
 
WHERE source_currency = 'NOK';
 
 
 
This outputs 4 decimals, even if the largest number in c.num_of_decimal  is 2: 
+------------------------------------------------+-----------------+
 
| round((100 / exchange_rate), c.num_of_decimal) | target_currency |
 
+------------------------------------------------+-----------------+
 
|                                        11.0000 | JPY             |
 
|                                        10.9600 | USD             |
 
+------------------------------------------------+-----------------+
 
 
 
Expected output
 
+------------------------------------------------+-----------------+
 
| round((100 / exchange_rate), c.num_of_decimal) | target_currency |
 
+------------------------------------------------+-----------------+
 
|                                          11.00 | JPY             |
 
|                                          10.96 | USD             |
 
+------------------------------------------------+-----------------+

Attached is a file to recreate the tables and run a few different variations of the query.

 Comments   
Comment by Tore Andersson [ 2019-11-09 ]

Another variation of the query which fail in the same manner:

SELECT
 
  ROUND ((100 / exchange_rate), num_of_decimal)
 
FROM (
 
  SELECT exchange_rate, num_of_decimal
 
  FROM exchange_rate er
 
  INNER JOIN currency c ON er.target_currency = c.currency_id
 
  WHERE source_currency = 'NOK'
 
    AND target_currency = 'USD'
 
) t1;

Comment by Elena Stepanova [ 2019-11-18 ]

Reproducible as described, also on MySQL 8.0. I couldn't find either in MariaDB or MySQL documentation whether it's expected or not, assigning to bar for an expert opinion.

Comment by Alexander Barkov [ 2019-11-19 ]

I can't see bugs here. Everything works as designed.

This expression:

ROUND ((100 / exchange_rate), num_of_decimal)

returns a value of the DECIMAL(20,4) data type. It's always printed with four decimal digits.

To achieve what you need please run this query:

SELECT
 
  FORMAT(ROUND((100 / exchange_rate), c.num_of_decimal), c.num_of_decimal) as c1,
 
  target_currency,
 
  num_of_decimal
 
FROM exchange_rate er INNER JOIN currency c
 
  ON er.target_currency = c.currency_id
 
WHERE source_currency = 'NOK';


+-------+-----------------+----------------+
 
| c1    | target_currency | num_of_decimal |
 
+-------+-----------------+----------------+
 
| 11    | JPY             |              0 |
 
| 10.96 | USD             |              2 |
 
+-------+-----------------+----------------+

Comment by Joffrey MICHAIE [ 2020-11-09 ]

Greetings Bar!

Sorry to re-open this bug/feature request.

I understand that the answer from the ROUND(num, dec) function is/should be of the type of the num input, but then how can we explain that the result for the function differs, depending on if dec is a constant, or a value fetched from a column?

Output from MariaDB 10.4.13

MariaDB [test]> select 0,round(0.0971872481491298, 0);
 
+---+------------------------------+
 
| 0 | round(0.0971872481491298, 0) |
 
+---+------------------------------+
 
| 0 |                            0 |
 
+---+------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select 1,round(0.0971872481491298, 1);
 
+---+------------------------------+
 
| 1 | round(0.0971872481491298, 1) |
 
+---+------------------------------+
 
| 1 |                          0.1 |
 
+---+------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select 2,round(0.0971872481491298, 2);
 
+---+------------------------------+
 
| 2 | round(0.0971872481491298, 2) |
 
+---+------------------------------+
 
| 2 |                         0.10 |
 
+---+------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select 3,round(0.0971872481491298, 3);
 
+---+------------------------------+
 
| 3 | round(0.0971872481491298, 3) |
 
+---+------------------------------+
 
| 3 |                        0.097 |
 
+---+------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select 4,round(0.0971872481491298, 4);
 
+---+------------------------------+
 
| 4 | round(0.0971872481491298, 4) |
 
+---+------------------------------+
 
| 4 |                       0.0972 |
 
+---+------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select 5,round(0.0971872481491298, 5);
 
+---+------------------------------+
 
| 5 | round(0.0971872481491298, 5) |
 
+---+------------------------------+
 
| 5 |                      0.09719 |
 
+---+------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select seq,round(0.0971872481491298, seq) from (select seq from seq_0_to_5) seq;  -- works also with table containing ints 0,1,2,3,4,5
 
+-----+--------------------------------+
 
| seq | round(0.0971872481491298, seq) |
 
+-----+--------------------------------+
 
|   0 |             0.0000000000000000 |
 
|   1 |             0.1000000000000000 |
 
|   2 |             0.1000000000000000 |
 
|   3 |             0.0970000000000000 |
 
|   4 |             0.0972000000000000 |
 
|   5 |             0.0971900000000000 |
 
+-----+--------------------------------+
 
6 rows in set (0.000 sec)

Thanks in advance for your lights!

Joffrey

Comment by Alexander Barkov [ 2020-11-10 ]

Hello joffrey92, good to hear from you

The results are the same - only the number of trailing spaces differs.

The number of trailing spaces depends on the result data type, which is evaluated during the statement preparation time.

And the result data type depends on the first and the second argument:

  • In case of a non-constant in the second argument (like the "sec" field in your example), the result data type is determined only by the first argument only (the "num"), because we don't know all possible values of "sec" at the statement preparation time.
  • In case of a constant, the second argument (the "dec") is evaluated during the statement preparation time and is further taken into account to calculate the result data type. The idea is to produce a minimum possible data type that covers all possible return values. So for example, if you pass the literal 2 to the second argument, the scale of the result is determined as 2 and therefore the result data type is DECIMAL(?,2).

This behaviour is by design.

Generated at Thu Feb 08 09:03:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.