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

8 files changed, 86 insertions, 18 deletions

diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index ba162b490..fa8f37c1f 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -15,6 +15,8 @@ from sqlalchemy import Date
 from sqlalchemy import DATETIME
 from sqlalchemy import DateTime
 from sqlalchemy import DECIMAL
+from sqlalchemy import DOUBLE
+from sqlalchemy import Double
 from sqlalchemy import exc
 from sqlalchemy import extract
 from sqlalchemy import FLOAT
@@ -776,6 +778,8 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
         (Float, "t.col"),
         (m.MSFloat, "t.col"),
         (m.MSDouble, "t.col"),
+        (DOUBLE, "t.col"),
+        (Double, "t.col"),
         (m.MSReal, "t.col"),
         (m.MSYear, "t.col"),
         (m.MSYear(2), "t.col"),
@@ -798,6 +802,8 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
         (m.FLOAT, "CAST(t.col AS FLOAT)"),
         (Float, "CAST(t.col AS FLOAT)"),
         (FLOAT, "CAST(t.col AS FLOAT)"),
+        (Double, "CAST(t.col AS DOUBLE)"),
+        (DOUBLE, "CAST(t.col AS DOUBLE)"),
         (m.DOUBLE, "CAST(t.col AS DOUBLE)"),
         (m.FLOAT, "CAST(t.col AS FLOAT)"),
         argnames="type_,expected",
diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py
index 22ffc888a..c506c306e 100644
--- a/test/dialect/oracle/test_compiler.py
+++ b/test/dialect/oracle/test_compiler.py
@@ -1,6 +1,7 @@
 # coding: utf-8
 from sqlalchemy import and_
 from sqlalchemy import bindparam
+from sqlalchemy import cast
 from sqlalchemy import Computed
 from sqlalchemy import exc
 from sqlalchemy import except_
@@ -23,6 +24,7 @@ from sqlalchemy import testing
 from sqlalchemy import text
 from sqlalchemy import type_coerce
 from sqlalchemy import TypeDecorator
+from sqlalchemy import types as sqltypes
 from sqlalchemy import union
 from sqlalchemy.dialects.oracle import base as oracle
 from sqlalchemy.dialects.oracle import cx_oracle
@@ -1380,6 +1382,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=dd,
         )
 
+    def test_double_to_oracle_double(self):
+        """test #5465"""
+        d1 = sqltypes.Double
+
+        self.assert_compile(
+            cast(column("foo"), d1), "CAST(foo AS DOUBLE PRECISION)"
+        )
+
 
 class SequenceTest(fixtures.TestBase, AssertsCompiledSQL):
     def test_basic(self):
diff --git a/test/dialect/oracle/test_reflection.py b/test/dialect/oracle/test_reflection.py
index df8dff30f..215f4ecd5 100644
--- a/test/dialect/oracle/test_reflection.py
+++ b/test/dialect/oracle/test_reflection.py
@@ -1,8 +1,10 @@
 # coding: utf-8
 
 
+from sqlalchemy import Double
 from sqlalchemy import exc
 from sqlalchemy import FLOAT
+from sqlalchemy import Float
 from sqlalchemy import ForeignKey
 from sqlalchemy import ForeignKeyConstraint
 from sqlalchemy import func
@@ -19,10 +21,12 @@ from sqlalchemy import testing
 from sqlalchemy import text
 from sqlalchemy import Unicode
 from sqlalchemy import UniqueConstraint
+from sqlalchemy.dialects import oracle
 from sqlalchemy.dialects.oracle.base import BINARY_DOUBLE
 from sqlalchemy.dialects.oracle.base import BINARY_FLOAT
 from sqlalchemy.dialects.oracle.base import DOUBLE_PRECISION
 from sqlalchemy.dialects.oracle.base import NUMBER
+from sqlalchemy.dialects.oracle.base import REAL
 from sqlalchemy.testing import assert_warns
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
@@ -803,17 +807,24 @@ class TypeReflectionTest(fixtures.TestBase):
         connection,
     ):
         specs = [
-            (DOUBLE_PRECISION(), FLOAT()),
-            # when binary_precision is supported
-            # (DOUBLE_PRECISION(), oracle.FLOAT(binary_precision=126)),
+            (DOUBLE_PRECISION(), DOUBLE_PRECISION()),
+            (Double(), DOUBLE_PRECISION()),
+            (REAL(), REAL()),
             (BINARY_DOUBLE(), BINARY_DOUBLE()),
             (BINARY_FLOAT(), BINARY_FLOAT()),
-            (FLOAT(5), FLOAT()),
-            # when binary_precision is supported
-            # (FLOAT(5), oracle.FLOAT(binary_precision=5),),
-            (FLOAT(), FLOAT()),
-            # when binary_precision is supported
-            # (FLOAT(5), oracle.FLOAT(binary_precision=126),),
+            (oracle.FLOAT(5), oracle.FLOAT(5)),
+            (
+                Float(5).with_variant(
+                    oracle.FLOAT(binary_precision=16), "oracle"
+                ),
+                oracle.FLOAT(16),
+            ),  # using conversion
+            (FLOAT(), DOUBLE_PRECISION()),
+            # from https://docs.oracle.com/cd/B14117_01/server.101/b10758/sqlqr06.htm  # noqa E501
+            # DOUBLE PRECISION == precision 126
+            # REAL == precision 63
+            (oracle.FLOAT(126), DOUBLE_PRECISION()),
+            (oracle.FLOAT(63), REAL()),
         ]
         self._run_test(metadata, connection, specs, ["precision"])
 
diff --git a/test/dialect/oracle/test_types.py b/test/dialect/oracle/test_types.py
index 715a90000..d3c522f25 100644
--- a/test/dialect/oracle/test_types.py
+++ b/test/dialect/oracle/test_types.py
@@ -12,7 +12,10 @@ from sqlalchemy import CHAR
 from sqlalchemy import DATE
 from sqlalchemy import Date
 from sqlalchemy import DateTime
+from sqlalchemy import Double
+from sqlalchemy import DOUBLE_PRECISION
 from sqlalchemy import event
+from sqlalchemy import exc
 from sqlalchemy import FLOAT
 from sqlalchemy import Float
 from sqlalchemy import Integer
@@ -41,6 +44,7 @@ from sqlalchemy.sql.sqltypes import NullType
 from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
+from sqlalchemy.testing import expect_raises_message
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import is_
 from sqlalchemy.testing import mock
@@ -300,6 +304,18 @@ class TypesTest(fixtures.TestBase):
             datetime.timedelta(days=35, seconds=5743),
         )
 
+    def test_no_decimal_float_precision(self):
+        with expect_raises_message(
+            exc.ArgumentError,
+            "Oracle FLOAT types use 'binary precision', which does not "
+            "convert cleanly from decimal 'precision'.  Please specify this "
+            "type with a separate Oracle variant, such as "
+            r"FLOAT\(precision=5\).with_variant\(oracle.FLOAT\("
+            r"binary_precision=16\), 'oracle'\), so that the Oracle "
+            "specific 'binary_precision' may be specified accurately.",
+        ):
+            FLOAT(5).compile(dialect=oracle.dialect())
+
     def test_numerics(self, metadata, connection):
         m = metadata
         t1 = Table(
@@ -309,7 +325,8 @@ class TypesTest(fixtures.TestBase):
             Column("numericcol", Numeric(precision=9, scale=2)),
             Column("floatcol1", Float()),
             Column("floatcol2", FLOAT()),
-            Column("doubleprec", oracle.DOUBLE_PRECISION),
+            Column("doubleprec1", DOUBLE_PRECISION),
+            Column("doubleprec2", Double()),
             Column("numbercol1", oracle.NUMBER(9)),
             Column("numbercol2", oracle.NUMBER(9, 3)),
             Column("numbercol3", oracle.NUMBER),
@@ -322,7 +339,8 @@ class TypesTest(fixtures.TestBase):
                 numericcol=5.2,
                 floatcol1=6.5,
                 floatcol2=8.5,
-                doubleprec=9.5,
+                doubleprec1=9.5,
+                doubleprec2=14.5,
                 numbercol1=12,
                 numbercol2=14.85,
                 numbercol3=15.76,
@@ -343,6 +361,7 @@ class TypesTest(fixtures.TestBase):
                     (6.5, float),
                     (8.5, float),
                     (9.5, float),
+                    (14.5, float),
                     (12, int),
                     (decimal.Decimal("14.85"), decimal.Decimal),
                     (15.76, float),
@@ -1154,7 +1173,7 @@ class SetInputSizesTest(fixtures.TestBase):
         (SmallInteger, 25, int, False),
         (Integer, 25, int, False),
         (Numeric(10, 8), decimal.Decimal("25.34534"), None, False),
-        (Float(15), 25.34534, None, False),
+        (oracle.FLOAT(15), 25.34534, None, False),
         (oracle.BINARY_DOUBLE, 25.34534, "NATIVE_FLOAT", False),
         (oracle.BINARY_FLOAT, 25.34534, "NATIVE_FLOAT", False),
         (oracle.DOUBLE_PRECISION, 25.34534, None, False),
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index b98d0fac6..98b974ea5 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -243,6 +243,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "CAST(bar AS someschema.somename) AS bar",
         )
 
+    def test_cast_double_pg_double(self):
+        """test #5465:
+
+        test sqlalchemy Double/DOUBLE to PostgreSQL DOUBLE PRECISION
+        """
+        d1 = sqltypes.Double
+
+        stmt = select(cast(column("foo"), d1))
+        self.assert_compile(
+            stmt, "SELECT CAST(foo AS DOUBLE PRECISION) AS foo"
+        )
+
     def test_cast_enum_schema_translate(self):
         """test #6739"""
         e1 = Enum("x", "y", "z", name="somename")
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index 0c00c7633..a59dd0ac7 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -12,6 +12,7 @@ from sqlalchemy import cast
 from sqlalchemy import Column
 from sqlalchemy import column
 from sqlalchemy import DateTime
+from sqlalchemy import Double
 from sqlalchemy import Enum
 from sqlalchemy import exc
 from sqlalchemy import Float
@@ -127,14 +128,16 @@ class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults):
             Column("x", postgresql.ARRAY(Float)),
             Column("y", postgresql.ARRAY(REAL)),
             Column("z", postgresql.ARRAY(postgresql.DOUBLE_PRECISION)),
+            Column("w", postgresql.ARRAY(Double)),
             Column("q", postgresql.ARRAY(Numeric)),
         )
         metadata.create_all(connection)
         connection.execute(
-            t1.insert(), dict(x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")])
+            t1.insert(),
+            dict(x=[5], y=[5], z=[6], w=[7], q=[decimal.Decimal("6.4")]),
         )
         row = connection.execute(t1.select()).first()
-        eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")]))
+        eq_(row, ([5], [5], [6], [7], [decimal.Decimal("6.4")]))
 
     def test_arrays_base(self, connection, metadata):
         t1 = Table(
@@ -143,14 +146,16 @@ class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults):
             Column("x", sqltypes.ARRAY(Float)),
             Column("y", sqltypes.ARRAY(REAL)),
             Column("z", sqltypes.ARRAY(postgresql.DOUBLE_PRECISION)),
+            Column("w", sqltypes.ARRAY(Double)),
             Column("q", sqltypes.ARRAY(Numeric)),
         )
         metadata.create_all(connection)
         connection.execute(
-            t1.insert(), dict(x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")])
+            t1.insert(),
+            dict(x=[5], y=[5], z=[6], w=[7], q=[decimal.Decimal("6.4")]),
         )
         row = connection.execute(t1.select()).first()
-        eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")]))
+        eq_(row, ([5], [5], [6], [7], [decimal.Decimal("6.4")]))
 
 
 class EnumTest(fixtures.TestBase, AssertsExecutionResults):
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index 42c83d87b..d2e3d5f40 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -50,7 +50,7 @@ class ReflectionTest(fixtures.TestBase, ComparesTables):
             Column("user_id", sa.INT, primary_key=True),
             Column("user_name", sa.VARCHAR(20), nullable=False),
             Column("test1", sa.CHAR(5), nullable=False),
-            Column("test2", sa.Float(5), nullable=False),
+            Column("test2", sa.Float(), nullable=False),
             Column("test3", sa.Text),
             Column("test4", sa.Numeric(10, 2), nullable=False),
             Column("test5", sa.Date),
@@ -1830,7 +1830,7 @@ def createTables(meta, schema=None):
         Column("user_id", sa.INT, primary_key=True),
         Column("user_name", sa.VARCHAR(20), nullable=False),
         Column("test1", sa.CHAR(5), nullable=False),
-        Column("test2", sa.Float(5), nullable=False),
+        Column("test2", sa.Float(), nullable=False),
         Column("test3", sa.Text),
         Column("test4", sa.Numeric(10, 2), nullable=False),
         Column("test5", sa.Date),
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index f441c3b46..4e1048cb6 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -23,6 +23,7 @@ from sqlalchemy import DateTime
 from sqlalchemy import DECIMAL
 from sqlalchemy import dialects
 from sqlalchemy import distinct
+from sqlalchemy import Double
 from sqlalchemy import Enum
 from sqlalchemy import exc
 from sqlalchemy import FLOAT
@@ -277,6 +278,7 @@ class AdaptTest(fixtures.TestBase):
         eq_(types.Numeric(asdecimal=False).python_type, float)
         eq_(types.LargeBinary().python_type, bytes)
         eq_(types.Float().python_type, float)
+        eq_(types.Double().python_type, float)
         eq_(types.Interval().python_type, datetime.timedelta)
         eq_(types.Date().python_type, datetime.date)
         eq_(types.DateTime().python_type, datetime.datetime)
@@ -3455,6 +3457,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialects.postgresql.FLOAT(), "FLOAT", allow_dialect_select=True
         )
 
+    def test_default_compile_double(self):
+        self.assert_compile(Double(), "DOUBLE")
+
     def test_default_compile_mysql_integer(self):
         self.assert_compile(
             dialects.mysql.INTEGER(display_width=5),