[CONPY-259] When using callproc() to execute a SELECT statement, the results have different precision compared to directly executing SELECT Created: 2023-04-06  Updated: 2023-04-11  Resolved: 2023-04-11

Status: Closed
Project: MariaDB Connector/Python
Component/s: Generic
Affects Version/s: 1.1.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Wenqian Deng Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

MariaDB 10.11

Python Version: 3.9

 Description   

I create a table with a column type of FLOAT and have a stored procedure that selects all records in the table. When I call this stored procedure using cursor.callproc(), the precision of the returned results is different from the results of direct SELECT.
In the test case I provided, cursor.callproc() returned `1898969600.0`, while "SELECT * FROM t0" returned `1898970000.0`. And the command-line client returns `1898970000`.

The test case:

def execute(con, sql):
 
    try:
 
        cursor = con.cursor()
 
        cursor.execute(sql)
 
        cursor.close()
 
    except Exception as e:
 
        print(e)
 
 
 
conn_params = {
 
    "user": "user",
 
    "password": "password",
 
    "host": "127.0.0.1",
 
}
 
 
 
con = mariadb.connect(**conn_params)
 
execute(con, "DROP DATABASE IF EXISTS test")
 
execute(con, "CREATE DATABASE test")
 
execute(con, "USE test")
 
execute(con, "CREATE TABLE t0(c0 FLOAT UNIQUE)");
 
execute(con, "CREATE PROCEDURE `t0_select_all`() BEGIN SELECT * FROM t0; END;");
 
execute(con, "INSERT INTO t0 VALUES (1898969556)");
 
 
 
# callproc
 
cursor = con.cursor()
 
cursor.callproc("t0_select_all")
 
print(cursor.fetchall())
 
cursor.close()
 
 
 
# direct select
 
cursor = con.cursor()
 
cursor.execute("SELECT * FROM t0")
 
for row in cursor:
 
    print(*row, sep=" * ")
 
cursor.close()
 
 
 
con.commit()
 
con.close()



 Comments   
Comment by Georg Richter [ 2023-04-11 ]

This is something we cannot fix:

When converting a float (simple precision) to a string, the result from server is different when converting a double to string:

MariaDB [test]> select c0, c0+0 from t0\G
 
*************************** 1. row ***************************
 
  c0: 1898970000
 
c0+0: 1898969600

MariaDB Connector/Python uses binary client/server protocol for callproc() method, which means the value is transferred as a 4 byte floating point value and then converted to double (Python doesn't have float), while cursor's execute() method uses text protocol (unless placeholders are used) which transfers the value as a string.

Example:

# use text protocol
 
cursor = con.cursor(binary=False)
 
cursor.execute("SELECT c0 FROM t0")
 
print(cursor.fetchall())
 
 
 
# use binary protocol
 
cursor = con.cursor(binary=True)
 
cursor.execute("SELECT c0 FROM t0")
 
print(cursor.fetchall())

returns:

[(1898970000.0,)]
 
[(1898969600.0,)]

I would recommend to use either DOUBLE or DECIMAL instead of simple precision FLOAT.

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