[#CONPY-87] Error with numeric values returned from query.

Comment by Georg Richter [ 2020-07-16 ]

Hi, thank you for your bug report.

Did you read https://mariadb.com/kb/en/floating-point-accuracy/ ?

Comment by Michal Sarna [ 2020-07-17 ]

Hi.
I have not. But what was weird for me is that returned values were not all the same. Hope You will be able to track down this. I've added _round _so now it's showing properly

Comment by Georg Richter [ 2020-07-17 ]

You will get only correct values, if it is converted to Decimal, so the corresponding datatype needs to be decimal instead of float or double:

mysql> select "1.1" * 3;

+--------------------+

| "1.1" * 3          |

+--------------------+

| 3.3000000000000003 |

+--------------------+

1 row in set (0,00 sec)

>>> # double/float in python is not accurate

>>> print(1.1 * 3)

3.3000000000000003

>>> import decimal

>>> print(Decimal("1.1") * 3)

3.3

or with a cursor:

>>> cursor.execute("drop table t1");

>>> cursor.execute("create temporary table t1 (a float, b double, c decimal(10,3))");

>>> cursor.execute("insert into t1 values (1.1, 1.1, 1.1)");

>>> cursor.execute("select a * 3, b * 3, c * 3 from t1");

>>> cursor.fetchone()

(3.3000000715255737, 3.3000000000000003, Decimal('3.300'))

Comment by Michal Sarna [ 2020-07-17 ]

Ok ... and where precisely in my script is any type of multiplication ?
And funny thing is that connector is working properly with static select but with any value passed as argument You get trash data on random lines.
So please check again.

Comment by Georg Richter [ 2020-07-17 ]

The multiplication should just show you that float is not accurate and the use of FLOAT should be avoided, as mentionen in MariaDB documentation: "Using FLOAT might give you some unexpected problems because all calculations in MariaDB are done with double precision"

The difference in output with and without placeholder is that one uses binary protocol (transferring float as 4-byte binary) and the other uses text protocol.

Comment by Michal Sarna [ 2020-07-17 ]

OK. I've get it. Changed in DB from float double.

Thx for Your time.

[CONPY-87] Error with numeric values returned from query. Created: 2020-07-16 Updated: 2020-07-17 Resolved: 2020-07-17
Status:	Closed
Project:	MariaDB Connector/Python
Component/s:	Generic
Affects Version/s:	1.0.0
Fix Version/s:	N/A

[CONPY-87] Error with numeric values returned from query. Created: 2020-07-16 Updated: 2020-07-17 Resolved: 2020-07-17