From 1776597131ef96472b5188cebc72c31a387c90f4 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Thu, 22 Jun 2017 23:51:52 -0400
Subject: [PATCH] Coerce float Python type to Float; ensure Python float coming
 back

Added some extra strictness to the handling of Python "float" values
passed to SQL statements.  A "float" value will be associated with the
:class:`.Float` datatype and not the Decimal-coercing :class:`.Numeric`
datatype as was the case before, eliminating a confusing warning
emitted on SQLite as well as unecessary coercion to Decimal.

Change-Id: I1bb1810ff1d198c0d929ccba5656e55401d74119
Fixes: #4017
---
 doc/build/changelog/changelog_12.rst       | 14 ++++++++++++
 doc/build/changelog/migration_12.rst       | 25 ++++++++++++++++++++++
 lib/sqlalchemy/sql/sqltypes.py             |  2 +-
 lib/sqlalchemy/testing/suite/test_types.py | 18 ++++++++++++++++
 test/sql/test_types.py                     | 17 +++++++++++++++
 5 files changed, 75 insertions(+), 1 deletion(-)

diff --git a/doc/build/changelog/changelog_12.rst b/doc/build/changelog/changelog_12.rst
index a963b96f07..5dc83da2d5 100644
--- a/doc/build/changelog/changelog_12.rst
+++ b/doc/build/changelog/changelog_12.rst
@@ -13,6 +13,20 @@
 .. changelog::
     :version: 1.2.0b1
 
+    .. change:: 4017
+        :tags: bug, sql
+        :tickets: 4017
+
+        Added some extra strictness to the handling of Python "float" values
+        passed to SQL statements.  A "float" value will be associated with the
+        :class:`.Float` datatype and not the Decimal-coercing :class:`.Numeric`
+        datatype as was the case before, eliminating a confusing warning
+        emitted on SQLite as well as unecessary coercion to Decimal.
+
+        .. seealso::
+
+            :ref:`change_floats_12`
+
     .. change:: 3058
         :tags: feature, orm
         :tickets: 3058
diff --git a/doc/build/changelog/migration_12.rst b/doc/build/changelog/migration_12.rst
index f0857c5314..add12a50c3 100644
--- a/doc/build/changelog/migration_12.rst
+++ b/doc/build/changelog/migration_12.rst
@@ -764,6 +764,31 @@ Where the value of the parameter "x_1" is ``'total/%score'``.
 
 :ticket:`2694`
 
+.. _change_floats_12:
+
+Stronger typing added to "float" datatypes
+------------------------------------------
+
+A series of changes allow for use of the :class:`.Float` datatype to more
+strongly link itself to Python floating point values, instead of the more
+generic :class:`.Numeric`.  The changes are mostly related to ensuring
+that Python floating point values are not erroneously coerced to
+``Decimal()``, and are coerced to ``float`` if needed, on the result side,
+if the application is working with plain floats.
+
+* A plain Python "float" value passed to a SQL expression will now be
+  pulled into a literal parameter with the type :class:`.Float`; previously,
+  the type was :class:`.Numeric`, with the default "asdecimal=True" flag, which
+  meant the result type would coerce to ``Decimal()``.  In particular,
+  this would emit a confusing warning on SQLite::
+
+        float_value = connection.scalar(
+            select([literal(4.56)])   # the "BindParameter" will now be
+                                      # Float, not Numeric(asdecimal=True)
+        )
+
+:ticket:`4017`
+
 Key Behavioral Changes - ORM
 ============================
 
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 7a3c50549e..06b5e5c19c 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -2604,7 +2604,7 @@ MATCHTYPE = MatchType()
 
 _type_map = {
     int: Integer(),
-    float: Numeric(),
+    float: Float(),
     bool: BOOLEANTYPE,
     decimal.Decimal: Numeric(),
     dt.date: Date(),
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index ee757e1caf..de32e77a4c 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -431,6 +431,24 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase):
             filter_=lambda n: n is not None and round(n, 5) or None
         )
 
+    @testing.fails_on("mysql", "until we do #4020")
+    def test_float_coerce_round_trip(self):
+        expr = 15.7563
+
+        val = testing.db.scalar(
+            select([literal(expr)])
+        )
+        eq_(val, expr)
+
+    # TODO: this one still breaks on MySQL
+    # def test_decimal_coerce_round_trip(self):
+    #    expr = decimal.Decimal("15.7563")
+    #
+    #    val = testing.db.scalar(
+    #        select([literal(expr)])
+    #    )
+    #    eq_(val, expr)
+
     @testing.requires.precision_numerics_general
     def test_precision_decimal(self):
         numbers = set([
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index f46ef21cd8..9107adaca0 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -2025,6 +2025,23 @@ class ExpressionTest(
         expr = column('foo', CHAR) == "asdf"
         eq_(expr.right.type.__class__, CHAR)
 
+    def test_actual_literal_adapters(self):
+        for data, expected in [
+            (5, Integer),
+            (2.65, Float),
+            (True, Boolean),
+            (decimal.Decimal("2.65"), Numeric),
+            (datetime.date(2015, 7, 20), Date),
+            (datetime.time(10, 15, 20), Time),
+            (datetime.datetime(2015, 7, 20, 10, 15, 20), DateTime),
+            (datetime.timedelta(seconds=5), Interval),
+            (None, types.NullType)
+        ]:
+            is_(
+                literal(data).type.__class__,
+                expected
+            )
+
     def test_typedec_operator_adapt(self):
         expr = test_table.c.bvalue + "hi"
 
-- 
2.39.5