`InvalidRequestError` when running a report with a distinct query

[grantfitzsimmons]

Steps to Recreate

Log into NHMD Test as petrifiedroadkill and run the Distinct Report query in the NHMD Entomology database.

https://nhmdtest030823-edge.test.specifysystems.org/specify/query/404/

Run it on any record set or open-ended query, see error.

See that the query it is based on is marked "Distinct".

This error does not happen without it.

https://nhmdtest030823-edge.test.specifysystems.org/specify/resources/app-resource/737/

Error:

InvalidRequestError at /report_runner/run/

No entities to join from; please use select_from() to establish the left entity/selectable of this join

Environment:

Request Method: POST
Request URL: http://nhmdtest030823-edge.test.specifysystems.org/report_runner/run/

Django Version: 3.2.15
Python Version: 3.8.0
Installed Applications:
('django.contrib.sessions',
 'django.contrib.staticfiles',
 'django.contrib.contenttypes',
 'django.contrib.auth',
 'specifyweb.specify',
 'specifyweb.permissions',
 'specifyweb.accounts',
 'specifyweb.stored_queries',
 'specifyweb.businessrules',
 'specifyweb.express_search',
 'specifyweb.context',
 'specifyweb.attachment_gw',
 'specifyweb.frontend',
 'specifyweb.barvis',
 'specifyweb.report_runner',
 'specifyweb.interactions',
 'specifyweb.workbench',
 'specifyweb.notifications',
 'specifyweb.export',
 'specifyweb.raven_placeholder')
Installed Middleware:
['django.middleware.gzip.GZipMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'specifyweb.context.middleware.ContextMiddleware',
 'specifyweb.permissions.middleware.PermissionsMiddleware',
 'specifyweb.middleware.general.GeneralMiddleware']

Traceback (most recent call last):
  File "/opt/specify7/ve/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/opt/specify7/ve/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/opt/specify7/ve/lib/python3.8/site-packages/django/views/decorators/http.py", line 40, in inner
    return func(request, *args, **kwargs)
  File "/opt/specify7/specifyweb/specify/views.py", line 29, in wrapped
    return view(request, *args, **kwargs)
  File "/opt/specify7/specifyweb/report_runner/views.py", line 52, in run
    report_data = run_query(request.specify_collection, request.specify_user, request.POST['query'])
  File "/opt/specify7/specifyweb/report_runner/views.py", line 202, in run_query
    query_result = run_ephemeral_query(collection, user, spquery)
  File "/opt/specify7/specifyweb/stored_queries/execution.py", line 378, in run_ephemeral_query
    return execute(session, collection, user, tableid, distinct, count_only,
  File "/opt/specify7/specifyweb/stored_queries/execution.py", line 513, in execute
    query, order_by_exprs = build_query(session, collection, user, tableid, field_specs, recordsetid=recordsetid, formatauditobjs=formatauditobjs, distinct=distinct)
  File "/opt/specify7/specifyweb/stored_queries/execution.py", line 580, in build_query
    query = query.join(models.RecordSetItem, models.RecordSetItem.recordId == id_field) \
  File "/opt/specify7/specifyweb/stored_queries/query_construct.py", line 116, in proxy
    return self._replace(query=method(*args, **kwargs))
  File "/opt/specify7/ve/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2133, in join
    return self._join(props,
  File "<string>", line 2, in _join
    <source code not available>
  File "/opt/specify7/ve/lib/python3.8/site-packages/sqlalchemy/orm/base.py", line 201, in generate
    fn(self, *args[1:], **kw)
  File "/opt/specify7/ve/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2284, in _join
    self._join_left_to_right(
  File "/opt/specify7/ve/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2307, in _join_left_to_right
    raise sa_exc.InvalidRequestError(

Exception Type: InvalidRequestError at /report_runner/run/
Exception Value: No entities to join from; please use select_from() to establish the left entity/selectable of this join

---

Reported By: Fedor at NHMD

[maxpatiiuk]

Very similar to #2058 Same comment applies - https://github.com/specify/specify7/issues/2058#issuecomment-1474585253 Likely a 2 minute change

[grantfitzsimmons]

2 minute change

[emenslin]

Can recreate in edge (7.9.6)