Bug 6007 - PostgreSQL: error while searching for reviewers
PostgreSQL: error while searching for reviewers
Status: RESOLVED FIXED
Product: OJS
Classification: Unclassified
Component: General
2.4.2
PC Mac OS X 10.6
: P5 normal
Assigned To: PKP Support
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2010-09-29 16:37 PDT by James MacGregor
Modified: 2012-12-20 10:15 PST (History)
3 users (show)

See Also:
Version Reported In:
Also Affects:


Attachments
patch (1.05 KB, patch)
2010-10-01 09:01 PDT, Fernão
Details | Diff
use THIS patch (1.05 KB, patch)
2010-10-01 09:07 PDT, Fernão
Details | Diff
searching for reviewers patch (1.29 KB, patch)
2011-07-04 02:58 PDT, Giuseppe Digilio
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description James MacGregor 2010-09-29 16:37:03 PDT
Courtesy of http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=6636&p=25555#p25555, which includes a partial fix. 

The user is hitting the following error while searching for users: 

DB Error: ERROR: SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 19:  ... BY u.user_id, u.last_name, ar.review_id ORDER BY a.article_...

(I haven't replicated this -- I don't have a Postrgre install.)
Comment 1 Fernão 2010-10-01 09:01:51 PDT
Created attachment 3279 [details]
patch
Comment 2 Fernão 2010-10-01 09:05:35 PDT
Comment on attachment 3279 [details]
patch

>--- classes/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php	2010-08-10 14:44:52.000000000 -0300
>+++ classes/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php	2010-10-01 12:58:04.000000000 -0300
>@@ -835,9 +835,12 @@
> 				break;
> 		}
> 
>+		$extraFieldsPsql = ($this->_dataSource->databaseType == 'postgres7') ? ' a.article_id, ' : '';
> 		$result =& $this->retrieveRange(
> 			'SELECT DISTINCT
>+			' . ($extraFieldsPsql ? 'ON (u.user_id, u.last_name)' : '') . '
> 				u.user_id,
>+                                ' . $extraFieldsPsql . '
> 				u.last_name,
> 				ar.review_id,
> 				AVG(a.quality) AS average_quality,
>@@ -854,7 +857,7 @@
> 				LEFT JOIN roles r ON (r.user_id = u.user_id)
> 			WHERE	u.user_id = r.user_id AND
> 				r.journal_id = ? AND
>-				r.role_id = ? ' . $searchSql . 'GROUP BY u.user_id, u.last_name, ar.review_id' .
>+				r.role_id = ? ' . $searchSql . 'GROUP BY u.user_id, u.last_name, ' . $extraFieldsPsql . ' ar.review_id' . 
> 			($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
> 			$paramArray, $rangeInfo
> 		);
Comment 3 Fernão 2010-10-01 09:07:10 PDT
Created attachment 3280 [details]
use THIS patch

(ignore the previous patch, please use this one)
Comment 4 Andrea Marchitelli 2011-03-19 03:04:37 PDT
I'm trying using this patch but I got an error.
Once I applyed the patch, when I go on SELECT REVIEWER link I got
DB Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Comment 5 Fernão 2011-03-27 18:27:00 PDT
in fact, that patch was broken; I've made a new one that worked, try it instead:

--- classes/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php	2011-03-27 22:15:22.000000000 -0300
+++ SectionEditorSubmissionDAO.inc.php	2011-03-27 22:16:24.000000000 -0300
@@ -777,9 +777,12 @@
 				break;
 		}
 
+		$extraFieldsPsql = ($this->_dataSource->databaseType == 'postgres7') ? ' a.article_id, ' : '';
 		$result =& $this->retrieveRange(
 			'SELECT DISTINCT
+			' . ($extraFieldsPsql ? 'ON (u.user_id, u.last_name)' : '') . '
 				u.user_id,
+                                ' . $extraFieldsPsql . '
 				u.last_name,
 				ar.review_id,
 				AVG(ra.quality) AS average_quality,
@@ -799,8 +802,8 @@
 				LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id)
 			WHERE u.user_id = r.user_id AND
 				r.journal_id = ? AND
-				r.role_id = ? ' . $searchSql . 'GROUP BY u.user_id, u.last_name, ar.review_id' .
-			($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
+				r.role_id = ? ' . $searchSql . 'GROUP BY u.user_id, u.last_name, a.article_id, ar.review_id ORDER BY u.user_id ' . $this->getDirectionMapping($sortDirection) . '
+, u.last_name ' . $this->getDirectionMapping($sortDirection),
 			$paramArray, $rangeInfo
 		);
 




(In reply to comment #4)
> I'm trying using this patch but I got an error.
> Once I applyed the patch, when I go on SELECT REVIEWER link I got
> DB Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
> expressions
Comment 6 Giuseppe Digilio 2011-07-04 02:58:16 PDT
Created attachment 3584 [details]
searching for reviewers patch

I've tried the previous patch but it didn't work, so I've posted this one, it works
Comment 7 Alec Smecher 2012-09-21 16:02:37 PDT
I suspect this fix is obsolete by now, but should vet to make sure.
Comment 8 Alec Smecher 2012-12-20 10:09:00 PST
Testing this out for myself, previous to any patch I was getting the following error (PostgreSQL 8.3.11 and 9.0.0 alike):

DB Error: ERROR: column "ar.declined" must appear in the GROUP BY clause or be used in an aggregate function

Not sure if this is the same error, but I'm able to use the reviewer search tool once the following patch is applied.
Comment 9 Alec Smecher 2012-12-20 10:15:02 PST
Fixed reviewer assign issue with psql
https://github.com/pkp/ocs/commit/6a83106fb40c5907e42a768b0edfe604f8f4fdcd
Comment 10 Alec Smecher 2012-12-20 10:15:02 PST
Fixed reviewer search
https://github.com/pkp/ojs/commit/427eba87b0eda5d4498b07010d39ae3c70820a07
Comment 11 Alec Smecher 2012-12-20 10:15:03 PST
Fixed reviewer search
https://github.com/pkp/ojs/commit/88b43e961fd016d04685767b238a9ddb2e377b9e