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

2 files changed, 29 insertions, 13 deletions

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