consider truediv as truediv; support floordiv operator

Implemented full support for "truediv" and "floordiv" using the
"/" and "//" operators.  A "truediv" operation between two expressions
using :class:`_types.Integer` now considers the result to be
:class:`_types.Numeric`, and the dialect-level compilation will cast
the right operand to a numeric type on a dialect-specific basis to ensure
truediv is achieved.  For floordiv, conversion is also added for those
databases that don't already do floordiv by default (MySQL, Oracle) and
the ``FLOOR()`` function is rendered in this case, as well as for
cases where the right operand is not an integer (needed for PostgreSQL,
others).

The change resolves issues both with inconsistent behavior of the
division operator on different backends and also fixes an issue where
integer division on Oracle would fail to be able to fetch a result due
to inappropriate outputtypehandlers.

Fixes: #4926
Change-Id: Id54cc018c1fb7a49dd3ce1216d68d40f43fe2659

4 files changed, 85 insertions, 23 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 55c2b07c4..c0fa57484 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2244,7 +2244,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 (value_tbl.c.val2 - value_tbl.c.val1) / value_tbl.c.val1,
             ),
             "SELECT values.id, (values.val2 - values.val1) "
-            "/ values.val1 AS anon_1 FROM values",
+            "/ CAST(values.val1 AS NUMERIC) AS anon_1 FROM values",
         )
 
         self.assert_compile(
@@ -2252,7 +2252,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 (value_tbl.c.val2 - value_tbl.c.val1) / value_tbl.c.val1 > 2.0,
             ),
             "SELECT values.id FROM values WHERE "
-            "(values.val2 - values.val1) / values.val1 > :param_1",
+            "(values.val2 - values.val1) / "
+            "CAST(values.val1 AS NUMERIC) > :param_1",
         )
 
         self.assert_compile(
@@ -2263,8 +2264,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 > 2.0,
             ),
             "SELECT values.id FROM values WHERE "
-            "(values.val1 / (values.val2 - values.val1)) "
-            "/ values.val1 > :param_1",
+            "(values.val1 / CAST((values.val2 - values.val1) AS NUMERIC)) "
+            "/ CAST(values.val1 AS NUMERIC) > :param_1",
         )
 
     def test_percent_chars(self):
diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py
index 9b0add713..64479b969 100644
--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -55,7 +55,7 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
             .where(
                 regional_sales.c.total_sales
                 > select(
-                    func.sum(regional_sales.c.total_sales) / 10
+                    func.sum(regional_sales.c.total_sales) // 10
                 ).scalar_subquery()
             )
             .cte("top_regions")
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 6e943d236..9e47f217f 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1621,66 +1621,87 @@ class OperatorAssociativityTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_associativity_7(self):
         f = column("f")
         # because - less precedent than /
-        self.assert_compile(f / (f - f), "f / (f - f)")
+        self.assert_compile(f / (f - f), "f / CAST((f - f) AS NUMERIC)")
 
     def test_associativity_8(self):
         f = column("f")
-        self.assert_compile(f / (f - f).label("foo"), "f / (f - f)")
+        self.assert_compile(
+            f / (f - f).label("foo"), "f / CAST((f - f) AS NUMERIC)"
+        )
 
     def test_associativity_9(self):
         f = column("f")
-        self.assert_compile(f / f - f, "f / f - f")
+        self.assert_compile(f / f - f, "f / CAST(f AS NUMERIC) - f")
 
     def test_associativity_10(self):
         f = column("f")
-        self.assert_compile((f / f) - f, "f / f - f")
+        self.assert_compile((f / f) - f, "f / CAST(f AS NUMERIC) - f")
 
     def test_associativity_11(self):
         f = column("f")
-        self.assert_compile((f / f).label("foo") - f, "f / f - f")
+        self.assert_compile(
+            (f / f).label("foo") - f, "f / CAST(f AS NUMERIC) - f"
+        )
 
     def test_associativity_12(self):
         f = column("f")
         # because / more precedent than -
-        self.assert_compile(f - (f / f), "f - f / f")
+        self.assert_compile(f - (f / f), "f - f / CAST(f AS NUMERIC)")
 
     def test_associativity_13(self):
         f = column("f")
-        self.assert_compile(f - (f / f).label("foo"), "f - f / f")
+        self.assert_compile(
+            f - (f / f).label("foo"), "f - f / CAST(f AS NUMERIC)"
+        )
 
     def test_associativity_14(self):
         f = column("f")
-        self.assert_compile(f - f / f, "f - f / f")
+        self.assert_compile(f - f / f, "f - f / CAST(f AS NUMERIC)")
 
     def test_associativity_15(self):
         f = column("f")
-        self.assert_compile((f - f) / f, "(f - f) / f")
+        self.assert_compile((f - f) / f, "(f - f) / CAST(f AS NUMERIC)")
 
     def test_associativity_16(self):
         f = column("f")
-        self.assert_compile(((f - f) / f) - f, "(f - f) / f - f")
+        self.assert_compile(
+            ((f - f) / f) - f, "(f - f) / CAST(f AS NUMERIC) - f"
+        )
 
     def test_associativity_17(self):
         f = column("f")
         # - lower precedence than /
-        self.assert_compile((f - f) / (f - f), "(f - f) / (f - f)")
+        self.assert_compile(
+            (f - f) / (f - f), "(f - f) / CAST((f - f) AS NUMERIC)"
+        )
 
     def test_associativity_18(self):
         f = column("f")
         # / higher precedence than -
-        self.assert_compile((f / f) - (f / f), "f / f - f / f")
+        self.assert_compile(
+            (f / f) - (f / f),
+            "f / CAST(f AS NUMERIC) - f / CAST(f AS NUMERIC)",
+        )
 
     def test_associativity_19(self):
         f = column("f")
-        self.assert_compile((f / f) - (f - f), "f / f - (f - f)")
+        self.assert_compile(
+            (f / f) - (f - f), "f / CAST(f AS NUMERIC) - (f - f)"
+        )
 
     def test_associativity_20(self):
         f = column("f")
-        self.assert_compile((f / f) / (f - f), "(f / f) / (f - f)")
+        self.assert_compile(
+            (f / f) / (f - f),
+            "(f / CAST(f AS NUMERIC)) / CAST((f - f) AS NUMERIC)",
+        )
 
     def test_associativity_21(self):
         f = column("f")
-        self.assert_compile(f / (f / (f - f)), "f / (f / (f - f))")
+        self.assert_compile(
+            f / (f / (f - f)),
+            "f / CAST((f / CAST((f - f) AS NUMERIC)) AS NUMERIC)",
+        )
 
     def test_associativity_22(self):
         f = column("f")
@@ -2195,7 +2216,6 @@ class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         ("add", operator.add, "+"),
         ("mul", operator.mul, "*"),
         ("sub", operator.sub, "-"),
-        ("div", operator.truediv, "/"),
         ("mod", operator.mod, "%"),
         id_="iaa",
     )
@@ -2216,6 +2236,17 @@ class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         ):
             self.assert_compile(py_op(lhs, rhs), res % sql_op)
 
+    def test_truediv_op_integer(self):
+        self.assert_compile(
+            5 / literal(5), ":param_1 / CAST(:param_2 AS NUMERIC)"
+        )
+
+    def test_floordiv_op_integer(self):
+        self.assert_compile(5 // literal(5), ":param_1 / :param_2")
+
+    def test_floordiv_op_numeric(self):
+        self.assert_compile(5.10 // literal(5.5), "FLOOR(:param_1 / :param_2)")
+
     @testing.combinations(
         ("format", "mytable.myid %% %s"),
         ("qmark", "mytable.myid % ?"),
@@ -2231,6 +2262,36 @@ class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             dialect=default.DefaultDialect(paramstyle=paramstyle),
         )
 
+    @testing.combinations(
+        (operator.add,),
+        (operator.mul,),
+        (operator.sub,),
+        (operator.floordiv),
+    )
+    def test_integer_integer_coercion_to_integer(self, op):
+        expr = op(column("bar", Integer()), column("foo", Integer()))
+        assert isinstance(expr.type, Integer)
+
+    @testing.combinations(
+        (operator.add,),
+        (operator.mul,),
+        (operator.sub,),
+        (operator.truediv,),
+    )
+    def test_integer_numeric_coercion_to_numeric(self, op):
+        expr = op(column("bar", Integer()), column("foo", Numeric(10, 2)))
+        assert isinstance(expr.type, Numeric)
+        expr = op(column("foo", Numeric(10, 2)), column("bar", Integer()))
+        assert isinstance(expr.type, Numeric)
+
+    def test_integer_truediv(self):
+        expr = column("bar", Integer()) / column("foo", Integer)
+        assert isinstance(expr.type, Numeric)
+
+    def test_integer_floordiv(self):
+        expr = column("bar", Integer()) // column("foo", Integer)
+        assert isinstance(expr.type, Integer)
+
 
 class ComparisonOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = "default"
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index eca4623c4..c3a2d8d3c 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -660,8 +660,8 @@ class SelectableTest(
         assert isinstance(subq2.c.foo.type, MyType)
 
     def test_type_coerce_selfgroup(self):
-        no_group = column("a") / type_coerce(column("x"), Integer)
-        group = column("b") / type_coerce(column("y") * column("w"), Integer)
+        no_group = column("a") // type_coerce(column("x"), Integer)
+        group = column("b") // type_coerce(column("y") * column("w"), Integer)
 
         self.assert_compile(no_group, "a / x")
         self.assert_compile(group, "b / (y * w)")