Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
any, docker mariadb/server default
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.