ensure correct cast for floats vs. numeric; other fixes

Fixed regression caused by the fix for :ticket:`9618` where floating point
values would lose precision being inserted in bulk, using either the
psycopg2 or psycopg drivers.

Implemented the :class:`_sqltypes.Double` type for SQL Server, having it
resolve to ``REAL``, or :class:`_mssql.REAL`, at DDL rendering time.

Fixed issue in Oracle dialects where ``Decimal`` returning types such as
:class:`_sqltypes.Numeric` would return floating point values, rather than
``Decimal`` objects, when these columns were used in the
:meth:`_dml.Insert.returning` clause to return INSERTed values.

Fixes: #9701
Change-Id: I8865496a6ccac6d44c19d0ca2a642b63c6172da9

4 files changed, 35 insertions, 1 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/__init__.py b/lib/sqlalchemy/dialects/mssql/__init__.py
index 0ef858a97..e6ad5e120 100644
--- a/lib/sqlalchemy/dialects/mssql/__init__.py
+++ b/lib/sqlalchemy/dialects/mssql/__init__.py
@@ -19,6 +19,7 @@ from .base import DATETIME
 from .base import DATETIME2
 from .base import DATETIMEOFFSET
 from .base import DECIMAL
+from .base import DOUBLE_PRECISION
 from .base import FLOAT
 from .base import IMAGE
 from .base import INTEGER
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 4a7e48ab8..e66d01a35 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1156,7 +1156,7 @@ RESERVED_WORDS = {
 
 
 class REAL(sqltypes.REAL):
-    __visit_name__ = "REAL"
+    """the SQL Server REAL datatype."""
 
     def __init__(self, **kw):
         # REAL is a synonym for FLOAT(24) on SQL server.
@@ -1166,6 +1166,21 @@ class REAL(sqltypes.REAL):
         super().__init__(**kw)
 
 
+class DOUBLE_PRECISION(sqltypes.DOUBLE_PRECISION):
+    """the SQL Server DOUBLE PRECISION datatype.
+
+    .. versionadded:: 2.0.11
+
+    """
+
+    def __init__(self, **kw):
+        # DOUBLE PRECISION is a synonym for FLOAT(53) on SQL server.
+        # it is only accepted as the word "DOUBLE PRECISION" in DDL,
+        # the numeric precision value is not allowed to be present
+        kw.setdefault("precision", 53)
+        super().__init__(**kw)
+
+
 class TINYINT(sqltypes.Integer):
     __visit_name__ = "TINYINT"
 
@@ -1670,6 +1685,7 @@ ischema_names = {
     "varbinary": VARBINARY,
     "bit": BIT,
     "real": REAL,
+    "double precision": DOUBLE_PRECISION,
     "image": IMAGE,
     "xml": XML,
     "timestamp": TIMESTAMP,
@@ -1700,6 +1716,9 @@ class MSTypeCompiler(compiler.GenericTypeCompiler):
 
         return " ".join([c for c in (spec, collation) if c is not None])
 
+    def visit_double(self, type_, **kw):
+        return self.visit_DOUBLE_PRECISION(type_, **kw)
+
     def visit_FLOAT(self, type_, **kw):
         precision = getattr(type_, "precision", None)
         if precision is None:
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index f6f10c476..c0e308383 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -819,6 +819,15 @@ class OracleExecutionContext_cx_oracle(OracleExecutionContext):
                                 outconverter=lambda value: value.read(),
                                 arraysize=len_params,
                             )
+                        elif (
+                            isinstance(type_impl, _OracleNumeric)
+                            and type_impl.asdecimal
+                        ):
+                            out_parameters[name] = self.cursor.var(
+                                decimal.Decimal,
+                                arraysize=len_params,
+                            )
+
                         else:
                             out_parameters[name] = self.cursor.var(
                                 dbtype, arraysize=len_params
diff --git a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py
index 739cbc5a9..b98518099 100644
--- a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py
+++ b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py
@@ -55,6 +55,10 @@ class _PsycopgNumeric(sqltypes.Numeric):
                 )
 
 
+class _PsycopgFloat(_PsycopgNumeric):
+    __visit_name__ = "float"
+
+
 class _PsycopgHStore(HSTORE):
     def bind_processor(self, dialect):
         if dialect._has_native_hstore:
@@ -104,6 +108,7 @@ class _PGDialect_common_psycopg(PGDialect):
         PGDialect.colspecs,
         {
             sqltypes.Numeric: _PsycopgNumeric,
+            sqltypes.Float: _PsycopgFloat,
             HSTORE: _PsycopgHStore,
             sqltypes.ARRAY: _PsycopgARRAY,
             INT2VECTOR: _PsycopgINT2VECTOR,