Error when sorting results by column using a PostgreSQL database

Created on 6 April 2020, over 4 years ago
Updated 16 January 2023, almost 2 years ago

Problem/Motivation

The solution proposed in #254957: Allow sorting on any column in the Results table page caused errors on my D7 website making use of webform (7.x-4.22).
When viewing the results table and sorting them by a column value, an SQL error is triggered :

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 330

This is possibly due to my using a PostgreSQL 11 database, but looks like an error creating the SQL query to me.

Proposed resolution

This was caused by the 'ORDER BY ws.data' statement in the SQL query. The ws.data field was not added to SELECT, but the query states that results should be ORDERed BY this field, causing an error.
This is added by the following lines in includes/webform.submissions.inc (added by the great patch Liam Morland proposed) :
$pager_query->orderBy('wsd.data', $sort['sort']);
Simply adding wsd.data to the select as done by the attached patch solves the problem.

Please provide feedback.

🐛 Bug report
Status

Postponed: needs info

Version

4.0

Component

Code

Created by

Live updates comments and jobs are added and updated live.
  • PostgreSQL

    Particularly affects sites running on the PostgreSQL database.

Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

No activities found.

Production build 0.71.5 2024