Implement generic Double and related fixed types

Added :class:`.Double`, :class:`.DOUBLE`, :class:`.DOUBLE_PRECISION`
datatypes to the base ``sqlalchemy.`` module namespace, for explicit use of
double/double precision as well as generic "double" datatypes. Use
:class:`.Double` for generic support that will resolve to DOUBLE/DOUBLE
PRECISION/FLOAT as needed for different backends.

Implemented DDL and reflection support for ``FLOAT`` datatypes which
include an explicit "binary_precision" value. Using the Oracle-specific
:class:`_oracle.FLOAT` datatype, the new parameter
:paramref:`_oracle.FLOAT.binary_precision` may be specified which will
render Oracle's precision for floating point types directly. This value is
interpreted during reflection. Upon reflecting back a ``FLOAT`` datatype,
the datatype returned is one of :class:`_types.DOUBLE_PRECISION` for a
``FLOAT`` for a precision of 126 (this is also Oracle's default precision
for ``FLOAT``), :class:`_types.REAL` for a precision of 63, and
:class:`_oracle.FLOAT` for a custom precision, as per Oracle documentation.

As part of this change, the generic :paramref:`_sqltypes.Float.precision`
value is explicitly rejected when generating DDL for Oracle, as this
precision cannot be accurately converted to "binary precision"; instead, an
error message encourages the use of
:meth:`_sqltypes.TypeEngine.with_variant` so that Oracle's specific form of
precision may be chosen exactly. This is a backwards-incompatible change in
behavior, as the previous "precision" value was silently ignored for
Oracle.

Fixes: #5465
Closes: #7674
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7674
Pull-request-sha: 5c68419e5aee2e27bf21a8ac9eb5950d196c77e5

Change-Id: I831f4af3ee3b23fde02e8f6393c83e23dd7cd34d

12 files changed, 244 insertions, 36 deletions

diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index dc1c536c8..7ceb33c7c 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -223,6 +223,9 @@ from .types import Date as Date
 from .types import DATETIME as DATETIME
 from .types import DateTime as DateTime
 from .types import DECIMAL as DECIMAL
+from .types import DOUBLE as DOUBLE
+from .types import Double as Double
+from .types import DOUBLE_PRECISION as DOUBLE_PRECISION
 from .types import Enum as Enum
 from .types import FLOAT as FLOAT
 from .types import Float as Float
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 7ec2b3dc2..5c2de0911 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1082,6 +1082,7 @@ colspecs = {
     _FloatType: _FloatType,
     sqltypes.Numeric: NUMERIC,
     sqltypes.Float: FLOAT,
+    sqltypes.Double: DOUBLE,
     sqltypes.Time: TIME,
     sqltypes.Enum: ENUM,
     sqltypes.MatchType: _MatchType,
diff --git a/lib/sqlalchemy/dialects/mysql/types.py b/lib/sqlalchemy/dialects/mysql/types.py
index 855fac1b3..2a4b4ad5f 100644
--- a/lib/sqlalchemy/dialects/mysql/types.py
+++ b/lib/sqlalchemy/dialects/mysql/types.py
@@ -152,7 +152,7 @@ class DECIMAL(_NumericType, sqltypes.DECIMAL):
         )
 
 
-class DOUBLE(_FloatType):
+class DOUBLE(_FloatType, sqltypes.DOUBLE):
     """MySQL DOUBLE type."""
 
     __visit_name__ = "DOUBLE"
diff --git a/lib/sqlalchemy/dialects/oracle/__init__.py b/lib/sqlalchemy/dialects/oracle/__init__.py
index c83e0573d..8994b4a10 100644
--- a/lib/sqlalchemy/dialects/oracle/__init__.py
+++ b/lib/sqlalchemy/dialects/oracle/__init__.py
@@ -24,6 +24,7 @@ from .base import NUMBER
 from .base import NVARCHAR
 from .base import NVARCHAR2
 from .base import RAW
+from .base import REAL
 from .base import ROWID
 from .base import TIMESTAMP
 from .base import VARCHAR
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 578450d24..1ae58b8f4 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -558,10 +558,12 @@ from ...sql import visitors
 from ...types import BLOB
 from ...types import CHAR
 from ...types import CLOB
+from ...types import DOUBLE_PRECISION
 from ...types import FLOAT
 from ...types import INTEGER
 from ...types import NCHAR
 from ...types import NVARCHAR
+from ...types import REAL
 from ...types import TIMESTAMP
 from ...types import VARCHAR
 
@@ -625,8 +627,49 @@ class NUMBER(sqltypes.Numeric, sqltypes.Integer):
             return sqltypes.Integer
 
 
-class DOUBLE_PRECISION(sqltypes.Float):
-    __visit_name__ = "DOUBLE_PRECISION"
+class FLOAT(sqltypes.FLOAT):
+    """Oracle FLOAT.
+
+    This is the same as :class:`_sqltypes.FLOAT` except that
+    an Oracle-specific :paramref:`_oracle.FLOAT.binary_precision`
+    parameter is accepted, and
+    the :paramref:`_sqltypes.Float.precision` parameter is not accepted.
+
+    Oracle FLOAT types indicate precision in terms of "binary precision", which
+    defaults to 126. For a REAL type, the value is 63. This parameter does not
+    cleanly map to a specific number of decimal places but is roughly
+    equivalent to the desired number of decimal places divided by 0.3103.
+
+    .. versionadded:: 2.0
+
+    """
+
+    __visit_name__ = "FLOAT"
+
+    def __init__(
+        self,
+        binary_precision=None,
+        asdecimal=False,
+        decimal_return_scale=None,
+    ):
+        r"""
+        Construct a FLOAT
+
+        :param binary_precision: Oracle binary precision value to be rendered
+         in DDL. This may be approximated to the number of decimal characters
+         using the formula "decimal precision = 0.30103 * binary precision".
+         The default value used by Oracle for FLOAT / DOUBLE PRECISION is 126.
+
+        :param asdecimal: See :paramref:`_sqltypes.Float.asdecimal`
+
+        :param decimal_return_scale: See
+         :paramref:`_sqltypes.Float.decimal_return_scale`
+
+        """
+        super().__init__(
+            asdecimal=asdecimal, decimal_return_scale=decimal_return_scale
+        )
+        self.binary_precision = binary_precision
 
 
 class BINARY_DOUBLE(sqltypes.Float):
@@ -742,6 +785,7 @@ ischema_names = {
     "RAW": RAW,
     "FLOAT": FLOAT,
     "DOUBLE PRECISION": DOUBLE_PRECISION,
+    "REAL": REAL,
     "LONG": LONG,
     "BINARY_DOUBLE": BINARY_DOUBLE,
     "BINARY_FLOAT": BINARY_FLOAT,
@@ -760,6 +804,9 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler):
     def visit_float(self, type_, **kw):
         return self.visit_FLOAT(type_, **kw)
 
+    def visit_double(self, type_, **kw):
+        return self.visit_DOUBLE_PRECISION(type_, **kw)
+
     def visit_unicode(self, type_, **kw):
         if self.dialect._use_nchar_for_unicode:
             return self.visit_NVARCHAR2(type_, **kw)
@@ -795,24 +842,50 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler):
         return self._generate_numeric(type_, "BINARY_FLOAT", **kw)
 
     def visit_FLOAT(self, type_, **kw):
-        # don't support conversion between decimal/binary
-        # precision yet
-        kw["no_precision"] = True
+        kw["_requires_binary_precision"] = True
         return self._generate_numeric(type_, "FLOAT", **kw)
 
     def visit_NUMBER(self, type_, **kw):
         return self._generate_numeric(type_, "NUMBER", **kw)
 
     def _generate_numeric(
-        self, type_, name, precision=None, scale=None, no_precision=False, **kw
+        self,
+        type_,
+        name,
+        precision=None,
+        scale=None,
+        _requires_binary_precision=False,
+        **kw,
     ):
         if precision is None:
-            precision = type_.precision
+
+            precision = getattr(type_, "precision", None)
+
+        if _requires_binary_precision:
+            binary_precision = getattr(type_, "binary_precision", None)
+
+            if precision and binary_precision is None:
+                # https://www.oracletutorial.com/oracle-basics/oracle-float/
+                estimated_binary_precision = int(precision / 0.30103)
+                raise exc.ArgumentError(
+                    "Oracle FLOAT types use 'binary precision', which does "
+                    "not convert cleanly from decimal 'precision'.  Please "
+                    "specify "
+                    f"this type with a separate Oracle variant, such as "
+                    f"{type_.__class__.__name__}(precision={precision})."
+                    f"with_variant(oracle.FLOAT"
+                    f"(binary_precision="
+                    f"{estimated_binary_precision}), 'oracle'), so that the "
+                    "Oracle specific 'binary_precision' may be specified "
+                    "accurately."
+                )
+            else:
+                precision = binary_precision
 
         if scale is None:
             scale = getattr(type_, "scale", None)
 
-        if no_precision or precision is None:
+        if precision is None:
             return name
         elif scale is None:
             n = "%(name)s(%(precision)s)"
@@ -1964,8 +2037,19 @@ class OracleDialect(default.DefaultDialect):
                 else:
                     coltype = NUMBER(precision, scale)
             elif coltype == "FLOAT":
-                # TODO: support "precision" here as "binary_precision"
-                coltype = FLOAT()
+                # https://docs.oracle.com/cd/B14117_01/server.101/b10758/sqlqr06.htm
+                if precision == 126:
+                    # The DOUBLE PRECISION datatype is a floating-point
+                    # number with binary precision 126.
+                    coltype = DOUBLE_PRECISION()
+                elif precision == 63:
+                    # The REAL datatype is a floating-point number with a
+                    # binary precision of 63, or 18 decimal.
+                    coltype = REAL()
+                else:
+                    # non standard precision
+                    coltype = FLOAT(binary_precision=precision)
+
             elif coltype in ("VARCHAR2", "NVARCHAR2", "CHAR", "NCHAR"):
                 coltype = self.ischema_names.get(coltype)(length)
             elif "WITH TIME ZONE" in coltype:
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 698ea277f..e265cd0f7 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1449,6 +1449,7 @@ from ...types import BIGINT
 from ...types import BOOLEAN
 from ...types import CHAR
 from ...types import DATE
+from ...types import DOUBLE_PRECISION
 from ...types import FLOAT
 from ...types import INTEGER
 from ...types import NUMERIC
@@ -1575,10 +1576,6 @@ class BYTEA(sqltypes.LargeBinary):
     __visit_name__ = "BYTEA"
 
 
-class DOUBLE_PRECISION(sqltypes.Float):
-    __visit_name__ = "DOUBLE_PRECISION"
-
-
 class INET(sqltypes.TypeEngine):
     __visit_name__ = "INET"
 
@@ -2896,8 +2893,8 @@ class PGTypeCompiler(compiler.GenericTypeCompiler):
         else:
             return "FLOAT(%(precision)s)" % {"precision": type_.precision}
 
-    def visit_DOUBLE_PRECISION(self, type_, **kw):
-        return "DOUBLE PRECISION"
+    def visit_double(self, type_, **kw):
+        return self.visit_DOUBLE_PRECISION(type, **kw)
 
     def visit_BIGINT(self, type_, **kw):
         return "BIGINT"
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 385de373e..79068c75f 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1179,7 +1179,7 @@ ischema_names = {
     "DATE_CHAR": sqltypes.DATE,
     "DATETIME": sqltypes.DATETIME,
     "DATETIME_CHAR": sqltypes.DATETIME,
-    "DOUBLE": sqltypes.FLOAT,
+    "DOUBLE": sqltypes.DOUBLE,
     "DECIMAL": sqltypes.DECIMAL,
     "FLOAT": sqltypes.FLOAT,
     "INT": sqltypes.INTEGER,
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index b140f9297..131281a16 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -4888,6 +4888,12 @@ class GenericTypeCompiler(TypeCompiler):
     def visit_FLOAT(self, type_, **kw):
         return "FLOAT"
 
+    def visit_DOUBLE(self, type_, **kw):
+        return "DOUBLE"
+
+    def visit_DOUBLE_PRECISION(self, type_, **kw):
+        return "DOUBLE PRECISION"
+
     def visit_REAL(self, type_, **kw):
         return "REAL"
 
@@ -5006,6 +5012,9 @@ class GenericTypeCompiler(TypeCompiler):
     def visit_float(self, type_, **kw):
         return self.visit_FLOAT(type_, **kw)
 
+    def visit_double(self, type_, **kw):
+        return self.visit_DOUBLE(type_, **kw)
+
     def visit_numeric(self, type_, **kw):
         return self.visit_NUMERIC(type_, **kw)
 
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 819f1dc9a..d022a24ca 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -578,7 +578,30 @@ class Float(Numeric):
         Construct a Float.
 
         :param precision: the numeric precision for use in DDL ``CREATE
-           TABLE``.
+           TABLE``. Backends **should** attempt to ensure this precision
+           indicates a number of digits for the generic
+           :class:`_sqltypes.Float` datatype.
+
+           .. note:: For the Oracle backend, the
+              :paramref:`_sqltypes.Float.precision` parameter is not accepted
+              when rendering DDL, as Oracle does not support float precision
+              specified as a number of decimal places. Instead, use the
+              Oracle-specific :class:`_oracle.FLOAT` datatype and specify the
+              :paramref:`_oracle.FLOAT.binary_precision` parameter. This is new
+              in version 2.0 of SQLAlchemy.
+
+              To create a database agnostic :class:`_types.Float` that
+              separately specifies binary precision for Oracle, use
+              :meth:`_types.TypeEngine.with_variant` as follows::
+
+                    from sqlalchemy import Column
+                    from sqlalchemy import Float
+                    from sqlalchemy.dialects import oracle
+
+                    Column(
+                        "float_data",
+                        Float(5).with_variant(oracle.FLOAT(binary_precision=16), "oracle")
+                    )
 
         :param asdecimal: the same flag as that of :class:`.Numeric`, but
           defaults to ``False``.   Note that setting this flag to ``True``
@@ -595,7 +618,7 @@ class Float(Numeric):
 
          .. versionadded:: 0.9.0
 
-        """
+        """  # noqa: E501
         self.precision = precision
         self.asdecimal = asdecimal
         self.decimal_return_scale = decimal_return_scale
@@ -611,6 +634,20 @@ class Float(Numeric):
             return None
 
 
+class Double(Float):
+    """A type for double ``FLOAT`` floating point types.
+
+    Typically generates a ``DOUBLE`` or ``DOUBLE_PRECISION`` in DDL,
+    and otherwise acts like a normal :class:`.Float` on the Python
+    side.
+
+    .. versionadded:: 2.0
+
+    """
+
+    __visit_name__ = "double"
+
+
 class DateTime(_LookupExpressionAdapter, TypeEngine[dt.datetime]):
 
     """A type for ``datetime.datetime()`` objects.
@@ -2769,35 +2806,93 @@ class TupleType(TypeEngine[Tuple[Any]]):
 
 class REAL(Float):
 
-    """The SQL REAL type."""
+    """The SQL REAL type.
+
+    .. seealso::
+
+        :class:`_types.Float` - documentation for the base type.
+
+    """
 
     __visit_name__ = "REAL"
 
 
 class FLOAT(Float):
 
-    """The SQL FLOAT type."""
+    """The SQL FLOAT type.
+
+    .. seealso::
+
+        :class:`_types.Float` - documentation for the base type.
+
+    """
 
     __visit_name__ = "FLOAT"
 
 
+class DOUBLE(Double):
+    """The SQL DOUBLE type.
+
+    .. versionadded:: 2.0
+
+    .. seealso::
+
+        :class:`_types.Double` - documentation for the base type.
+
+    """
+
+    __visit_name__ = "DOUBLE"
+
+
+class DOUBLE_PRECISION(Double):
+    """The SQL DOUBLE PRECISION type.
+
+    .. versionadded:: 2.0
+
+    .. seealso::
+
+        :class:`_types.Double` - documentation for the base type.
+
+    """
+
+    __visit_name__ = "DOUBLE_PRECISION"
+
+
 class NUMERIC(Numeric):
 
-    """The SQL NUMERIC type."""
+    """The SQL NUMERIC type.
+
+    .. seealso::
+
+        :class:`_types.Numeric` - documentation for the base type.
+
+    """
 
     __visit_name__ = "NUMERIC"
 
 
 class DECIMAL(Numeric):
 
-    """The SQL DECIMAL type."""
+    """The SQL DECIMAL type.
+
+    .. seealso::
+
+        :class:`_types.Numeric` - documentation for the base type.
+
+    """
 
     __visit_name__ = "DECIMAL"
 
 
 class INTEGER(Integer):
 
-    """The SQL INT or INTEGER type."""
+    """The SQL INT or INTEGER type.
+
+    .. seealso::
+
+        :class:`_types.Integer` - documentation for the base type.
+
+    """
 
     __visit_name__ = "INTEGER"
 
@@ -2807,14 +2902,26 @@ INT = INTEGER
 
 class SMALLINT(SmallInteger):
 
-    """The SQL SMALLINT type."""
+    """The SQL SMALLINT type.
+
+    .. seealso::
+
+        :class:`_types.SmallInteger` - documentation for the base type.
+
+    """
 
     __visit_name__ = "SMALLINT"
 
 
 class BIGINT(BigInteger):
 
-    """The SQL BIGINT type."""
+    """The SQL BIGINT type.
+
+    .. seealso::
+
+        :class:`_types.BigInteger` - documentation for the base type.
+
+    """
 
     __visit_name__ = "BIGINT"
 
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 6f02d5557..fb12d23c8 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -344,7 +344,7 @@ class ComponentReflectionTest(fixtures.TablesTest):
                 metadata,
                 Column("user_id", sa.INT, primary_key=True),
                 Column("test1", sa.CHAR(5), nullable=False),
-                Column("test2", sa.Float(5), nullable=False),
+                Column("test2", sa.Float(), nullable=False),
                 Column(
                     "parent_user_id",
                     sa.Integer,
@@ -361,7 +361,7 @@ class ComponentReflectionTest(fixtures.TablesTest):
                 metadata,
                 Column("user_id", sa.INT, primary_key=True),
                 Column("test1", sa.CHAR(5), nullable=False),
-                Column("test2", sa.Float(5), nullable=False),
+                Column("test2", sa.Float(), nullable=False),
                 schema=schema,
                 test_needs_fk=True,
             )
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 94bab009a..0940eab9b 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -731,7 +731,7 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase):
 
     def test_render_literal_float(self, literal_round_trip):
         literal_round_trip(
-            Float(4),
+            Float(),
             [15.7563, decimal.Decimal("15.7563")],
             [15.7563],
             filter_=lambda n: n is not None and round(n, 5) or None,
@@ -783,17 +783,17 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase):
     @testing.requires.floats_to_four_decimals
     def test_float_as_decimal(self, do_numeric_test):
         do_numeric_test(
-            Float(precision=8, asdecimal=True),
-            [15.7563, decimal.Decimal("15.7563"), None],
-            [decimal.Decimal("15.7563"), None],
+            Float(asdecimal=True),
+            [15.756, decimal.Decimal("15.756"), None],
+            [decimal.Decimal("15.756"), None],
             filter_=lambda n: n is not None and round(n, 4) or None,
         )
 
     def test_float_as_float(self, do_numeric_test):
         do_numeric_test(
-            Float(precision=8),
-            [15.7563, decimal.Decimal("15.7563")],
-            [15.7563],
+            Float(),
+            [15.756, decimal.Decimal("15.756")],
+            [15.756],
             filter_=lambda n: n is not None and round(n, 5) or None,
         )
 
diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py
index 9464cc9c4..45e31aaf7 100644
--- a/lib/sqlalchemy/types.py
+++ b/lib/sqlalchemy/types.py
@@ -28,6 +28,9 @@ from .sql.sqltypes import Date
 from .sql.sqltypes import DATETIME
 from .sql.sqltypes import DateTime
 from .sql.sqltypes import DECIMAL
+from .sql.sqltypes import DOUBLE
+from .sql.sqltypes import Double
+from .sql.sqltypes import DOUBLE_PRECISION
 from .sql.sqltypes import Enum
 from .sql.sqltypes import FLOAT
 from .sql.sqltypes import Float
@@ -105,6 +108,9 @@ __all__ = [
     "BigInteger",
     "Numeric",
     "Float",
+    "Double",
+    "DOUBLE",
+    "DOUBLE_PRECISION",
     "DateTime",
     "Date",
     "Time",