Bug #289
Feature #36: associate publications with projects
Feature #175: New Publications Page
Autocomplete for Author not working in live site
| Status: | Closed | Start date: | 2011-09-09 | |
|---|---|---|---|---|
| Priority: | Urgent | Due date: | ||
| Assignee: |  Chris Cannam | % Done: | 0% | |
| Category: | - | |||
| Target version: | - |
Description
The author autocomplete search function is not working in the live site.
The symptom is just that no results appear, but in the logs there is a 500 server error for the autocomplete callback. The error says
ActiveRecord::StatementInvalid (PGError: ERROR: column "authorships.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT * FROM "authorships" WHERE (LOWER(name_on_paper) LIKE...
^
: SELECT * FROM "authorships" WHERE (LOWER(name_on_paper) LIKE '%sandler%' OR LOWER(email) LIKE '%sandler%') GROUP BY name_on_paper, institution, email ORDER BY name_on_paper LIMIT 100):
vendor/plugins/redmine_bibliography/app/controllers/publications_controller.rb:211:in `autocomplete_for_author'
This is presumably something where Postgres is stricter than MySQL.
History
#1
Updated by Chris Cannam over 13 years ago
- Description updated (diff)
CJ Date writes:
If a select expression includes a GROUP BY clause, then there are restrictions on the form that the SELECT clause can take. To be specific, each select-item in the SELECT clause (including any that are implied by an asterisk shorthand) must be single-valued per group. Thus, such select-items must not include any reference to any column of table T that is not mentioned in the GROUP BY clause itself -- unless that reference is the argument, or part of the argument, to one of the aggregate functions COUNT, SUM, AVG, MAX, or MIN, whose effect is to reduce some collection of scalar values from a group to a single such value.
#2
Updated by Chris Cannam over 13 years ago
- Assignee changed from Luis Figueira to Chris Cannam
#3
Updated by Chris Cannam over 13 years ago
- Status changed from New to Closed
Fixed in 9fabf0da3b09
#4
Updated by Chris Cannam over 13 years ago
- Status changed from Closed to In Progress
Reopening. Fix is not a fix. With this change, selecting an entry in the search results no longer populates the author data fields if the result came from an Authorship (rather than a User). The error is "Couldn't find Authorship without an ID", because the key for the selected item is simply "Authorship_" with no ID suffix.
The problem is that we're using a GROUP BY on name, institution and email columns because we want unique values in those, but then we require the id column to build our key. This doesn't work because there is no way for the database to determine which row we want the id of -- if there are multiple rows with identical name, institution and email, which row's id should it return from the SELECT? That can't be answered, which is why GROUP BY doesn't permit this.
For our purposes, of course, any of them would do. We don't care.
We can't introduce id into the GROUP BY clause, since that would make it useless (every row has a distinct id so we'd get all of them).
I think we can't do this in the SQL query after all, we have to instead query without grouping and remove duplicates afterwards. I'm going to leave that for another day; for now I'll just remove the grouping.
#5
Updated by Chris Cannam over 13 years ago
Done in f3e35d639aa4 (in live) pending a proper fix.
#6
Updated by Chris Cannam over 13 years ago
- Status changed from In Progress to Closed