From 6d589ffbb5fe04a4ee606819e948974045f62b80 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 8 Dec 2021 08:57:44 -0500 Subject: 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 --- lib/sqlalchemy/sql/compiler.py | 36 +++++++++++++++++++++++++++++++- lib/sqlalchemy/sql/default_comparator.py | 1 + lib/sqlalchemy/sql/operators.py | 29 ++++++++++++++++++++++++- lib/sqlalchemy/sql/sqltypes.py | 5 ++--- 4 files changed, 66 insertions(+), 5 deletions(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 28c1bf069..5f6ee5f41 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -177,7 +177,6 @@ OPERATORS = { operators.mul: " * ", operators.sub: " - ", operators.mod: " % ", - operators.truediv: " / ", operators.neg: "-", operators.lt: " < ", operators.le: " <= ", @@ -1923,6 +1922,41 @@ class SQLCompiler(Compiled): "Unary expression has no operator or modifier" ) + def visit_truediv_binary(self, binary, operator, **kw): + if self.dialect.div_is_floordiv: + return ( + self.process(binary.left, **kw) + + " / " + # TODO: would need a fast cast again here, + # unless we want to use an implicit cast like "+ 0.0" + + self.process( + elements.Cast(binary.right, sqltypes.Numeric()), **kw + ) + ) + else: + return ( + self.process(binary.left, **kw) + + " / " + + self.process(binary.right, **kw) + ) + + def visit_floordiv_binary(self, binary, operator, **kw): + if ( + self.dialect.div_is_floordiv + and binary.right.type._type_affinity is sqltypes.Integer + ): + return ( + self.process(binary.left, **kw) + + " / " + + self.process(binary.right, **kw) + ) + else: + return "FLOOR(%s)" % ( + self.process(binary.left, **kw) + + " / " + + self.process(binary.right, **kw) + ) + def visit_is_true_unary_operator(self, element, operator, **kw): if ( element._is_implicitly_boolean diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 036a96e9f..2bbead673 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -310,6 +310,7 @@ operator_lookup = { "div": (_binary_operate,), "mod": (_binary_operate,), "truediv": (_binary_operate,), + "floordiv": (_binary_operate,), "custom_op": (_custom_op_operate,), "json_path_getitem_op": (_binary_operate,), "json_getitem_op": (_binary_operate,), diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 6d45cd033..74eb73e46 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -14,6 +14,7 @@ from operator import add from operator import and_ from operator import contains from operator import eq +from operator import floordiv from operator import ge from operator import getitem from operator import gt @@ -1220,7 +1221,12 @@ class ColumnOperators(Operators): def __truediv__(self, other): """Implement the ``/`` operator. - In a column context, produces the clause ``a / b``. + In a column context, produces the clause ``a / b``, and + considers the result type to be numeric. + + .. versionchanged:: 2.0 The truediv operator against two integers + is now considered to return a numeric value. Behavior on specific + backends may vary. """ return self.operate(truediv, other) @@ -1233,6 +1239,26 @@ class ColumnOperators(Operators): """ return self.reverse_operate(truediv, other) + def __floordiv__(self, other): + """Implement the ``//`` operator. + + In a column context, produces the clause ``a / b``, + which is the same as "truediv", but considers the result + type to be integer. + + .. versionadded:: 2.0 + + """ + return self.operate(floordiv, other) + + def __rfloordiv__(self, other): + """Implement the ``//`` operator in reverse. + + See :meth:`.ColumnOperators.__floordiv__`. + + """ + return self.reverse_operate(floordiv, other) + _commutative = {eq, ne, add, mul} _comparison = {eq, ne, lt, gt, ge, le} @@ -1588,6 +1614,7 @@ _PRECEDENCE = { json_path_getitem_op: 15, mul: 8, truediv: 8, + floordiv: 8, mod: 8, neg: 8, add: 7, diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index e65fa3c14..f035284f4 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -310,8 +310,6 @@ class Integer(_LookupExpressionAdapter, TypeEngine): @util.memoized_property def _expression_adaptations(self): - # TODO: need a dictionary object that will - # handle operators generically here, this is incomplete return { operators.add: { Date: Date, @@ -323,7 +321,8 @@ class Integer(_LookupExpressionAdapter, TypeEngine): Integer: self.__class__, Numeric: Numeric, }, - operators.truediv: {Integer: self.__class__, Numeric: Numeric}, + operators.truediv: {Integer: Numeric, Numeric: Numeric}, + operators.floordiv: {Integer: self.__class__, Numeric: Numeric}, operators.sub: {Integer: self.__class__, Numeric: Numeric}, } -- cgit v1.2.1