Unsupported query: unsupported binary operator: <int> / <int> (desired <int>)

[timgraham]

Dividing an integer column by another integer in an UPDATE query crashes:

======================================================================
ERROR: test_lefthand_division (expressions.tests.ExpressionOperatorTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 615, in run
    testMethod()
  File "/home/tim/code/django/tests/expressions/tests.py", line 973, in test_lefthand_division
    Number.objects.filter(pk=self.n.pk).update(integer=F('integer') / 2, float=F('float') / 42.7)
  File "/home/tim/code/django/django/db/models/query.py", line 741, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1429, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1100, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: unsupported binary operator: <int> / <int> (desired <int>)

Is there a plan to add support?

SQL:

UPDATE "expressions_number" SET "the_integer" = ("expressions_number"."the_integer" / 2), ... 
WHERE "expressions_number"."id" = 1;

[timgraham]

I think the problem is that the division operator yields float (or at least something besides int) and then the float can't be assigned to an integer column (similar to #20). @rafiss do you think this merits an issue in https://github.com/cockroachdb/cockroach. Possibly it would be covered by cockroachdb/cockroach#38965?

These queries do work.

> SELECT 4 / 2;
  ?column?
------------
         2

SELECT ..., ("expressions_number"."the_integer" / 2) AS "i" FROM "expressions_number"

[rafiss]

I don't think this could be addressed by automatically typecasting, since I don't think we'd add logic that could make the result lose precision without the user intending it. So I think the real DB issue we'd need to fix is: cockroachdb/cockroach#41448

[mrchrisadams]

If we know we're working with integers would one option be to use this operator instead, as outlined in the issue comment below?

One note: the // operator can be used if integer division is required.

cockroachdb/cockroach#41448 (comment)

[timgraham]

Operators are customized in DatabaseOperations.combine_expression(), but it isn't possible to determine the types (e.g. integer) of the column names or placeholders in sub_expressions.

[timgraham]

These queries work with psycopg3.

[timgraham]

Correction: these queries only work with server-side cursors. With client-side-binding cursors (which Django defaults to), this issue remains the same.