PKP Bugzilla – Bug 6007
PostgreSQL: error while searching for reviewers
Last modified: 2012-12-20 10:15:03 PST
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.)
Created attachment 3279 [details] patch
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 > );
Created attachment 3280 [details] use THIS patch (ignore the previous patch, please use this one)
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
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
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
I suspect this fix is obsolete by now, but should vet to make sure.
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.
Fixed reviewer assign issue with psql https://github.com/pkp/ocs/commit/6a83106fb40c5907e42a768b0edfe604f8f4fdcd
Fixed reviewer search https://github.com/pkp/ojs/commit/427eba87b0eda5d4498b07010d39ae3c70820a07
Fixed reviewer search https://github.com/pkp/ojs/commit/88b43e961fd016d04685767b238a9ddb2e377b9e