WHERE statement fails because placeholder %s is replaced with scientific notation for floating point numbers

[phoecouscousfa]

Hello, I have the problem that my where statement fails when working with placeholder syntax and floating point values:

sql = """
SELECT *
FROM MYTABLE
WHERE my_float = %s
"""
cursor.execute(sql, (0.0001,))

Is transformed to this sql by mysqlclient:

SELECT *
FROM MYTABLE
WHERE my_float = 0.0001e0

0.0001e0 does not match the rows with 0.0001 in the database. If I don't work with placeholders and just place 0.0001 directly in the sql string like this:

sql = """
SELECT *
FROM MYTABLE
WHERE my_float = 0.0001
"""
cursor.execute(sql)

I get the expected rows from the cursor.

I work with python 3.8 and mysqlclient 2.2.4.

[methane]

What is schema?
0.0001 in SQL is not floating point number. So 0.0001e0 is correct.

[methane]

What is your database? mariadb or mysql? what version?

With MySQL 8.0.35, no issue with 0.0001e0.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35 MySQL Community Server - GPL
(snip)

mysql> create table t2 (id integer primary key, f float(10,4));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> insert into t2 (id,f) values (1, 0.0001);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;
+----+--------+
| id | f      |
+----+--------+
|  1 | 0.0001 |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from t2 where f = 0.0001;
+----+--------+
| id | f      |
+----+--------+
|  1 | 0.0001 |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from t2 where f = 0.0001e0;
+----+--------+
| id | f      |
+----+--------+
|  1 | 0.0001 |
+----+--------+
1 row in set (0.01 sec)

[phoecouscousfa]

Yes, I think this is the issue. We use MariaDB instead of MySQL:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5227
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> create table t2 (id integer primary key, f float(10,4));
Query OK, 0 rows affected (0,007 sec)

MariaDB [test]> insert into t2 (id,f) values (1, 0.0001);
Query OK, 1 row affected (0,002 sec)

MariaDB [test]> select * from t2;
+----+--------+
| id | f      |
+----+--------+
|  1 | 0.0001 |
+----+--------+
1 row in set (0,000 sec)

MariaDB [test]> select * from t2 where f = 0.0001;
+----+--------+
| id | f      |
+----+--------+
|  1 | 0.0001 |
+----+--------+
1 row in set (0,001 sec)

MariaDB [test]> select * from t2 where f = 0.0001e0;
Empty set (0,000 sec)

Apart from this issue MySQLdb has worked perfectly with MariaDB.Interestingly this issue does not arise with the official MySQL Connector https://dev.mysql.com/doc/connector-python/en/ with MariaDB as it does not transform floating point numbers to scientific notation.

[phoecouscousfa]

Can you integrate a configuration option to select how floting point placeholders will be replaced?

[methane]

You are relaying on accidental behavior. You shouldn't think f = 0.0001 is "correct".

See https://mariadb.com/kb/en/float/

Using FLOAT might give you some unexpected problems because all calculations in MariaDB are done with double precision. See Floating Point Accuracy.

Float 0.0001, Double 0.0001, and Decimal 0.0001 are all different values. There are no gurantee what = returns. Surprisingly, there is difference between even float and float(10,4) column types!

mysql> create table t (id integer primary key, f1 float, f2 float(10,4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t (id, f1, f2) values (1, 0.0001, 0.0001), (2, 0.0001e0, 0.0001e0);
Query OK, 2 rows affected (0.02 sec)

mysql> select f1, f1=0.0001, f1=0.0001e0, f2, f2=0.0001, f2=0.0001e0 from t;
+--------+-----------+-------------+--------+-----------+-------------+
| f1     | f1=0.0001 | f1=0.0001e0 | f2     | f2=0.0001 | f2=0.0001e0 |
+--------+-----------+-------------+--------+-----------+-------------+
| 0.0001 |         0 |           0 | 0.0001 |         1 |           0 |
| 0.0001 |         0 |           0 | 0.0001 |         1 |           0 |
+--------+-----------+-------------+--------+-----------+-------------+

mysql> select f1, f1-0.0001, f1-0.0001e0, f2, f2-0.0001, f2-0.0001e0 from t;
+--------+--------------------------------+--------------------------------+--------+-----------+--------------------------------+
| f1     | f1-0.0001                      | f1-0.0001e0                    | f2     | f2-0.0001 | f2-0.0001e0                    |
+--------+--------------------------------+--------------------------------+--------+-----------+--------------------------------+
| 0.0001 | -0.000000000002526212488436659 | -0.000000000002526212488436659 | 0.0001 |   -0.0000 | -0.000000000002526212488436659 |
| 0.0001 | -0.000000000002526212488436659 | -0.000000000002526212488436659 | 0.0001 |   -0.0000 | -0.000000000002526212488436659 |
+--------+--------------------------------+--------------------------------+--------+-----------+--------------------------------+
2 rows in set (0.00 sec)

You must avoid accidental behavior by avoiding equality test between floating point numbers, or using explicit casting.

[methane]

When stroing to float column, the value lost double precison. cast(f, double) is not 0.0001 anymore.

mysql> select f2, cast(f2 as double) from t where id=1;
+--------+------------------------+
| f2     | cast(f2 as double)     |
+--------+------------------------+
| 0.0001 | 0.00009999999747378752 |
+--------+------------------------+

So you need to compare values by float.

mysql> select f2, f2=0.0001e0, f2=cast(0.0001e0 as float) from t where id=1;
+--------+-------------+----------------------------+
| f2     | f2=0.0001e0 | f2=cast(0.0001e0 as float) |
+--------+-------------+----------------------------+
| 0.0001 |           0 |                          1 |
+--------+-------------+----------------------------+
1 row in set (0.00 sec)

Stop using float and always use double may reduce this risk.
But floating point is always hard. I recommend you to:

• use decimal instead of floating point.
• avoid exact equality test (e.g. =) between floating point numbers.
• learn hard about floating point and get review from expert.