Opened 5 years ago

Last modified 4 years ago

#31094 closed Bug

subquery uses ungrouped column "table.column" from outer query — at Version 4

Reported by: Johannes Maron Owned by: nobody
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords:
Cc: Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Johannes Maron)

Why upgrading to Django 3.0 I get the following issue:

subquery uses ungrouped column "t4.position" from outer query
LINE 1: ..."."activity_type" AND ST_Contains(U2."locations", T4."positi...

It works fine in Django 2.2.8.

Stack:
Python 3.8
PostgeSQL 11
macOS / Ubuntu latest

Full query in 3.0 (pretty)

SELECT DISTINCT "camps_offer"."id",
                "camps_offer"."title",
                "camps_offer"."slug",
                "camps_offer"."is_active",
                "camps_offer"."modified",
                "camps_offer"."created",
                "camps_offer"."provider_id",
                "camps_offer"."activity_type",
                "camps_offer"."description",
                "camps_offer"."highlights",
                "camps_offer"."important_information",
                "camps_offer"."min_age",
                "camps_offer"."max_age",
                "camps_offer"."food",
                "camps_offer"."video",
                "camps_offer"."accommodation",
                "camps_offer"."accommodation_type",
                "camps_offer"."room_type",
                "camps_offer"."room_size_min",
                "camps_offer"."room_size_max",
                "camps_offer"."external_url",
                "camps_offer"."application_form",
                "camps_offer"."caseload",
                "camps_offer"."field_trips",
                MIN(T4."retail_price") AS "min_retail_price",
                (SELECT U0."id"
                 FROM "camps_servicepackage" U0
                          INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                 WHERE (U0."company_id" = 1 AND U0."option" = "camps_offer"."activity_type" AND
                        ST_Contains(U2."locations", T4."position"))
                 LIMIT 1)              AS "in_package",
                "camps_provider"."id",
                "camps_provider"."title",
                "camps_provider"."slug",
                "camps_provider"."is_active",
                "camps_provider"."modified",
                "camps_provider"."created",
                "camps_provider"."logo",
                "camps_provider"."description",
                "camps_provider"."video",
                "camps_provider"."external_url",
                "camps_provider"."terms",
                "camps_provider"."cancellation_policy",
                "camps_provider"."privacy_policy",
                "camps_provider"."application_form"
FROM "camps_offer"
         LEFT OUTER JOIN "camps_bookingoption" ON ("camps_offer"."id" = "camps_bookingoption"."offer_id")
         INNER JOIN "camps_provider" ON ("camps_offer"."provider_id" = "camps_provider"."id")
         INNER JOIN "camps_bookingoption" T4 ON ("camps_offer"."id" = T4."offer_id")
WHERE ("camps_offer"."is_active" = True AND "camps_provider"."is_active" = True AND
       T4."end" >= STATEMENT_TIMESTAMP() AND T4."is_active" = True AND (SELECT U0."id"
                                                                        FROM "camps_servicepackage" U0
                                                                                 INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                                                                        WHERE (U0."company_id" = 1 AND
                                                                               U0."option" = "camps_offer"."activity_type" AND
                                                                               ST_Contains(U2."locations", T4."position"))
                                                                        LIMIT 1) IS NOT NULL)
GROUP BY "camps_offer"."id", "camps_provider"."id"
ORDER BY "camps_offer"."created" ASC

Full query in 2.2 (pretty)

SELECT DISTINCT "camps_offer"."id",
                "camps_offer"."title",
                "camps_offer"."slug",
                "camps_offer"."is_active",
                "camps_offer"."modified",
                "camps_offer"."created",
                "camps_offer"."provider_id",
                "camps_offer"."activity_type",
                "camps_offer"."description",
                "camps_offer"."highlights",
                "camps_offer"."important_information",
                "camps_offer"."min_age",
                "camps_offer"."max_age",
                "camps_offer"."food",
                "camps_offer"."video",
                "camps_offer"."accommodation",
                "camps_offer"."accommodation_type",
                "camps_offer"."room_type",
                "camps_offer"."room_size_min",
                "camps_offer"."room_size_max",
                "camps_offer"."external_url",
                "camps_offer"."application_form",
                "camps_offer"."caseload",
                "camps_offer"."field_trips",
                MIN(T4."retail_price") AS "min_retail_price",
                (SELECT U0."id"
                 FROM "camps_servicepackage" U0
                          INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                 WHERE (U0."company_id" = 1 AND U0."option" = ("camps_offer"."activity_type") AND
                        ST_Contains(U2."locations", (T4."position")))
                 LIMIT 1)              AS "in_package",
                "camps_provider"."id",
                "camps_provider"."title",
                "camps_provider"."slug",
                "camps_provider"."is_active",
                "camps_provider"."modified",
                "camps_provider"."created",
                "camps_provider"."logo",
                "camps_provider"."description",
                "camps_provider"."video",
                "camps_provider"."external_url",
                "camps_provider"."terms",
                "camps_provider"."cancellation_policy",
                "camps_provider"."privacy_policy",
                "camps_provider"."application_form"
FROM "camps_offer"
         LEFT OUTER JOIN "camps_bookingoption" ON ("camps_offer"."id" = "camps_bookingoption"."offer_id")
         INNER JOIN "camps_provider" ON ("camps_offer"."provider_id" = "camps_provider"."id")
         INNER JOIN "camps_bookingoption" T4 ON ("camps_offer"."id" = T4."offer_id")
WHERE ("camps_offer"."is_active" = True AND "camps_provider"."is_active" = True AND
       T4."end" >= (STATEMENT_TIMESTAMP()) AND T4."is_active" = True AND (SELECT U0."id"
                                                                          FROM "camps_servicepackage" U0
                                                                                   INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                                                                          WHERE (U0."company_id" = 1 AND
                                                                                 U0."option" = ("camps_offer"."activity_type") AND
                                                                                 ST_Contains(U2."locations", (T4."position")))
                                                                          LIMIT 1) IS NOT NULL)
GROUP BY "camps_offer"."id",
         (SELECT U0."id"
          FROM "camps_servicepackage" U0
                   INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
          WHERE (U0."company_id" = 1 AND U0."option" = ("camps_offer"."activity_type") AND
                 ST_Contains(U2."locations", (T4."position")))
          LIMIT 1), "camps_provider"."id"
ORDER BY "camps_offer"."created" ASC

Full stack trace

Environment:


Request Method: GET
Request URL: http://localhost:8000/api/offer-map/

Django Version: 3.0
Python Version: 3.8.0
Installed Applications:
['django.contrib.auth',
 'django.contrib.sites',
 'django.contrib.contenttypes',
 '...',
 '....',
 '....',
 'django.contrib.admin',
 'django.contrib.sessions',
 'django.contrib.messages',
 'whitenoise.runserver_nostatic',
 'django.contrib.staticfiles',
 'django.contrib.postgres',
 'django.contrib.gis',
 'django.forms',
 'django_dramatiq',
 'mailauth',
 'mailauth.contrib.admin',
 's3file',
 'stdimage',
 'adminsortable2',
 'rest_framework',
 'rest_framework_gis',
 'django_filters',
 'phonenumber_field',
 '...']
Installed Middleware:
['log_request_id.middleware.RequestIDMiddleware',
 'voiio.logging.middleware.UserAgentLogMiddleware',
 'django.middleware.gzip.GZipMiddleware',
 'django.middleware.security.SecurityMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 's3file.middleware.S3FileMiddleware',
 'accounts.middleware.CompanyMiddleware']



Traceback (most recent call last):
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)

The above exception (subquery uses ungrouped column "t4.position" from outer query
LINE 1: ..."."activity_type" AND ST_Contains(U2."locations", T4."positi...
                                                             ^
) was the direct cause of the following exception:
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/viewsets.py", line 114, in view
    return self.dispatch(request, *args, **kwargs)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/Users/johannes/GitHub/voiio/voiio-platform/camps/viewsets.py", line 344, in list
    page = self.paginate_queryset(queryset)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/generics.py", line 171, in paginate_queryset
    return self.paginator.paginate_queryset(queryset, self.request, view=self)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/rest_framework/pagination.py", line 627, in paginate_queryset
    results = list(queryset[offset:offset + self.page_size + 1])
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1137, in execute_sql
    cursor.execute(sql, params)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/johannes/GitHub/voiio/voiio-platform/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)

Exception Type: ProgrammingError at /api/offer-map/
Exception Value: subquery uses ungrouped column "t4.position" from outer query
LINE 1: ..."."activity_type" AND ST_Contains(U2."locations", T4."positi...                                                         ^

Change History (4)

comment:1 by Johannes Maron, 5 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 5 years ago

Resolution: → needsinfo
Status: new → closed

Can you provide a queryset? We'll not be able to reproduce this issue without it. Can you also confirm that it works with Django 2.2?

comment:3 by Johannes Maron, 5 years ago

Description: modified (diff)
Resolution: needsinfo
Status: closed → new

comment:4 by Johannes Maron, 5 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top