BUG#28821983: Fix rounding errors for decimal values

nmariz · nmariz · commit 2a2a52d452e7 · 2022-04-19T17:01:46.000+01:00
This patch fixes potential rounding errors with arithmetic expressions,
in decimal data types.
This issue was fixed by not quoting decimal values.

Tests added for regression.

Change-Id: Iba31f0f216242f8338fab39d23fbc53ad056e2be
diff --git a/CHANGES.txt b/CHANGES.txt
@@ -16,6 +16,7 @@ v8.0.30
 - WL#15035: Enforce PEP 7 and PEP 8 coding style
 - WL#14822: Refactor the authentication plugin mechanism
 - WL#14815: Support OpenSSL 3.0
+- BUG#28821983: Fix rounding errors for decimal values
 
 v8.0.29
 =======
diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py
@@ -32,6 +32,7 @@
 import weakref
 
 from collections import namedtuple
+from decimal import Decimal
 
 from .abstracts import NAMED_TUPLE_CACHE, MySQLCursorAbstract
 from .constants import ServerFlag
@@ -396,11 +397,12 @@ def _process_params_dict(self, params):
             escape = self._connection.converter.escape
             quote = self._connection.converter.quote
             res = {}
-            for key, value in list(params.items()):
+            for key, value in params.items():
                 conv = value
                 conv = to_mysql(conv)
                 conv = escape(conv)
-                conv = quote(conv)
+                if not isinstance(value, Decimal):
+                    conv = quote(conv)
                 res[key.encode()] = conv
         except Exception as err:
             raise ProgrammingError(
@@ -412,15 +414,18 @@ def _process_params_dict(self, params):
     def _process_params(self, params):
         """Process query parameters."""
         try:
-            res = params
+            res = params[:]
 
             to_mysql = self._connection.converter.to_mysql
             escape = self._connection.converter.escape
             quote = self._connection.converter.quote
 
-            res = [to_mysql(i) for i in res]
-            res = [escape(i) for i in res]
-            res = [quote(i) for i in res]
+            res = [to_mysql(value) for value in res]
+            res = [escape(value) for value in res]
+            res = [
+                quote(value) if not isinstance(params[i], Decimal) else value
+                for i, value in enumerate(res)
+            ]
         except Exception as err:
             raise ProgrammingError(
                 f"Failed processing format-parameters; {err}"
@@ -607,8 +612,6 @@ def remove_comments(match):
                         raise ProgrammingError(
                             "Not all parameters were used in the SQL statement"
                         )
-                    # for p in self._process_params(params):
-                    #    tmp = tmp.replace(b'%s',p,1)
                 values.append(tmp)
             if fmt in stmt:
                 stmt = stmt.replace(fmt, b",".join(values), 1)
diff --git a/src/mysql_capi.c b/src/mysql_capi.c
@@ -1922,6 +1922,10 @@ MySQL_convert_to_mysql(MySQL *self, PyObject *args)
         if (new_value == Py_None) {
             PyTuple_SET_ITEM(prepared, i, PyBytes_FromString("NULL"));
         }
+        else if (strcmp((value)->ob_type->tp_name, "decimal.Decimal") == 0) {
+            Py_INCREF(new_value);
+            PyTuple_SET_ITEM(prepared, i, new_value);
+        }
         else if (PyBytes_Check(new_value)) {
             PyObject *quoted = PyBytes_FromFormat("'%s'", PyBytes_AsString(new_value));
             PyTuple_SET_ITEM(prepared, i, quoted);
diff --git a/tests/test_bugs.py b/tests/test_bugs.py
@@ -6867,3 +6867,49 @@ def test_expression_as_column_without_alias(self):
                 """
             )
             _ = cur.fetchall()
+
+
+class BugOra328821983(tests.MySQLConnectorTests):
+    """BUG#328821983: Fix rounding errors when using decimal.Decimal."""
+
+    @foreach_cnx()
+    def test_decimal_update(self):
+        table = "BugOra328821983"
+        with self.cnx.cursor() as cur:
+            cur.execute(f"DROP TABLE IF EXISTS {table}")
+            cur.execute(
+                f"""
+                CREATE TABLE {table} (
+                    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                    value DECIMAL(32,8) NOT NULL
+                )
+                """
+            )
+            cur.execute(
+                f"INSERT INTO {table} VALUES (NULL, %s)",
+                (Decimal("100000000000.00000001"),),
+            )
+            cur.execute(f"SELECT value FROM {table}")
+            res = cur.fetchall()[0][0]
+            self.assertEqual(res, Decimal("100000000000.00000001"))
+
+            # Use this value to increment the decimal field
+            value = Decimal("0.00000101")
+
+            # Test update with tuple as placeholders
+            query = f"UPDATE {table} SET value=(value + %s) WHERE id=%s"
+            cur.execute(query, (value, 1))
+
+            cur.execute(f"SELECT value FROM {table}")
+            res = cur.fetchall()[0][0]
+            self.assertEqual(res, Decimal("100000000000.00000102"))
+
+            # Test update with dictionary as placeholders
+            query = f"UPDATE {table} SET value=(value + %(value)s) WHERE id=%(id)s"
+            cur.execute(query, {"value": value, "id": 1})
+
+            cur.execute(f"SELECT value FROM {table}")
+            res = cur.fetchall()[0][0]
+            self.assertEqual(res, Decimal("100000000000.00000203"))
+
+            cur.execute(f"DROP TABLE IF EXISTS {table}")
diff --git a/tests/test_cursor.py b/tests/test_cursor.py
@@ -391,7 +391,7 @@ def test__process_params(self):
             b"128",
             b"1281288",
             b"3.14",
-            b"'3.14'",
+            b"3.14",
             b"'back\\\\slash'",
             b"'newline\\n'",
             b"'return\\r'",
@@ -456,7 +456,7 @@ def test__process_params_dict(self):
             b"b": b"128",
             b"c": b"1281288",
             b"d": b"3.14",
-            b"e": b"'3.14'",
+            b"e": b"3.14",
             b"f": b"'back\\\\slash'",
             b"g": b"'newline\\n'",
             b"h": b"'return\\r'",
