From 3b8a14153da9e7b6694571fa10f6d30c4012ee82 Mon Sep 17 00:00:00 2001 From: Gord Thompson Date: Sun, 20 Dec 2020 10:20:10 -0700 Subject: Fix issues with JSON and float/numeric Decimal accuracy and behavior has been improved when extracting floating point and/or decimal values from JSON strings using the :meth:`_sql.sqltypes.JSON.Comparator.as_float` method, when the numeric value inside of the JSON string has many significant digits; previously, MySQL backends would truncate values with many significant digits and SQL Server backends would raise an exception due to a DECIMAL cast with insufficient significant digits. Both backends now use a FLOAT-compatible approach that does not hardcode significant digits for floating point values. For precision numerics, a new method :meth:`_sql.sqltypes.JSON.Comparator.as_numeric` has been added which accepts arguments for precision and scale, and will return values as Python ``Decimal`` objects with no floating point conversion assuming the DBAPI supports it (all but pysqlite). Fixes: #5788 Change-Id: I6eb51fe172a389548dd6e3c65efec9f1f538012e --- lib/sqlalchemy/dialects/mssql/base.py | 13 +++++++------ lib/sqlalchemy/dialects/mysql/base.py | 29 ++++++++++++++++++++++------- 2 files changed, 29 insertions(+), 13 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 911e1791a..bc5480e2c 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -2108,12 +2108,13 @@ class MSSQLCompiler(compiler.SQLCompiler): self.process(binary.right, **kw), ) elif binary.type._type_affinity is sqltypes.Numeric: - type_expression = ( - "ELSE CAST(JSON_VALUE(%s, %s) AS DECIMAL(10, 6))" - % ( - self.process(binary.left, **kw), - self.process(binary.right, **kw), - ) + type_expression = "ELSE CAST(JSON_VALUE(%s, %s) AS %s)" % ( + self.process(binary.left, **kw), + self.process(binary.right, **kw), + "FLOAT" + if isinstance(binary.type, sqltypes.Float) + else "NUMERIC(%s, %s)" + % (binary.type.precision, binary.type.scale), ) elif binary.type._type_affinity is sqltypes.Boolean: # the NULL handling is particularly weird with boolean, so diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 911c0d522..f90d961d8 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1455,14 +1455,29 @@ class MySQLCompiler(compiler.SQLCompiler): ) ) elif binary.type._type_affinity is sqltypes.Numeric: - # FLOAT / REAL not added in MySQL til 8.0.17 - type_expression = ( - "ELSE CAST(JSON_EXTRACT(%s, %s) AS DECIMAL(10, 6))" - % ( - self.process(binary.left, **kw), - self.process(binary.right, **kw), + if ( + binary.type.scale is not None + and binary.type.precision is not None + ): + # using DECIMAL here because MySQL does not recognize NUMERIC + type_expression = ( + "ELSE CAST(JSON_EXTRACT(%s, %s) AS DECIMAL(%s, %s))" + % ( + self.process(binary.left, **kw), + self.process(binary.right, **kw), + binary.type.precision, + binary.type.scale, + ) + ) + else: + # FLOAT / REAL not added in MySQL til 8.0.17 + type_expression = ( + "ELSE JSON_EXTRACT(%s, %s)+0.0000000000000000000000" + % ( + self.process(binary.left, **kw), + self.process(binary.right, **kw), + ) ) - ) elif binary.type._type_affinity is sqltypes.Boolean: # the NULL handling is particularly weird with boolean, so # explicitly return true/false constants -- cgit v1.2.1