Skip to content

Unsupported query: greatest(): expected avg() to be of type float, found type decimal #74

@timgraham

Description

@timgraham

It looks like the GREATEST function can't compare mixed number types:

SELECT ... GREATEST(AVG("aggregation_book"."rating"), AVG("aggregation_book"."price")) AS "price_or_median"
FROM "aggregation_publisher"
LEFT OUTER JOIN "aggregation_book" ON ("aggregation_publisher"."id" = "aggregation_book"."publisher_id")
GROUP BY "aggregation_publisher"."id"
HAVING GREATEST(AVG("aggregation_book"."rating"), AVG("aggregation_book"."price")) >= ("aggregation_publisher"."num_awards")
ORDER BY "aggregation_publisher"."num_awards" ASC;

rating is FloatField
price is DecimalField

Django test failure:

======================================================================
ERROR: test_expression_on_aggregation (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidParameterValue: greatest(): expected avg(price) to be of type float, found type decimal


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/aggregation/tests.py", line 1100, in test_expression_on_aggregation
    qs, [1, 3, 7, 9], lambda v: v.num_awards)
  File "/home/tim/code/django/django/test/testcases.py", line 1047, in assertQuerysetEqual
    items = map(transform, qs)
  File "/home/tim/code/django/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  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 99, in execute
    return super().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: greatest(): expected avg(price) to be of type float, found type decimal

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions