I'm querying data from a table(schema and example data below) in mysql(mysql-connector-python). In this table I have two float values "ourCostPerSegment" and "theirCostPerSegment" the values that are stored in mysql are 0.0069 and 0.02 respectively. When I query the data from python I get values like 0.006899999920278788 and 0.019999999552965164. These issues are causing a margin of error in calculation that is to large.
This is the table schema:
DESCRIBE companyProperties;
+---------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| dateEffective | date | NO | | NULL | |
| isAgeGated | tinyint(1) | NO | | 0 | |
| ourCostPerSegment | float | NO | | NULL | |
| theirCostPerSegment | float | NO | | NULL | |
| monthlyBaseFee | int | NO | | NULL | |
| friendlyName | text | NO | | NULL | |
+---------------------+------------+------+-----+---------+-------+
Table values:
SELECT * FROM companyProperties;
+---------------+------------+-------------------+---------------------+----------------+--------------+
| dateEffective | isAgeGated | ourCostPerSegment | theirCostPerSegment | monthlyBaseFee | friendlyName |
+---------------+------------+-------------------+---------------------+----------------+--------------+
| 2023-08-03 | 1 | 0.0069 | 0.02 | 250 | test |
+---------------+------------+-------------------+---------------------+----------------+--------------+
This is the code that is running the query: This is part of an API request using the FastAPI framework.
python3 --version
Python 3.10.12
conn = db.cursor(dictionary=True, prepared=True)
getCompanyProperties = "SELECT * FROM `{}`.companyProperties ORDER BY dateEffective DESC LIMIT 1".format(companyKey["companyKey"])
conn.execute(getCompanyProperties, [])
companyPropertiesObject = conn.fetchall()
print(companyPropertiesObject)
This is the response:
[{'dateEffective': datetime.date(2023, 8, 3), 'isAgeGated': 1, 'ourCostPerSegment': 0.006899999920278788, 'theirCostPerSegment': 0.019999999552965164, 'monthlyBaseFee': 250, 'friendlyName': 'test'}]
Server Info
SHOW VARIABLES LIKE "%version%";
+--------------------------+-------------------------+
| Variable_name | Value |
+--------------------------+-------------------------+
| admin_tls_version | TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999 |
| innodb_version | 8.0.33 |
| original_server_version | 999999 |
| protocol_version | 10 |
| replica_type_conversions | |
| slave_type_conversions | |
| tls_version | TLSv1.2,TLSv1.3 |
| version | 8.0.33-0ubuntu0.22.04.2 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.13 |
+--------------------------+-------------------------+
I tried to strictly specify the format of the float in mySQL like FLOAT(M,D). This didn't fix the issue. One solution that did work was changing the column type to a TEXT and converting the string that is returned to a float in python but this is not ideal.
I have seen some things about decimal point numbers having issues in binary. How do I get around this issue?
Welcome to the wonderful world of floating point numbers. The issue is that most decimal numbers, like 0.0069 and 0.02 are not representable exacly in binary (just like 1/3 is not exacly representable, the decimals go on forever).
MySQL has a Decimal data type that stores exact decimal values, look into using it if the floating point accuracy is an issue for you.