DB error on paper review

[Dragone]

Hi to all,
I have a problem with the review of the submission.
When a Director clicks on Complete button (Add the submission to the list of accepted Presentations) on the Paper Review page I have the following error
DB Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
The url is
http://conferences.aepic.it/index.php/l ... nsAccepted
I use OCS 2.1.0.1 and Postgres 8.1.11

Thanks in advance for your help
Daniela

This is the stack trace:
(postgres7): SELECT * FROM sessions WHERE session_id = ? [ (0=>'dddd21fc426fcfd7878a74e37e1241cc') ] (postgres7): SELECT * FROM users WHERE user_id = ? [ (0=>'1') ] (postgres7): SELECT * FROM user_settings WHERE user_id = ? [ (0=>'1') ]
Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /var/conferences/lib/adodb/adodb.inc.php:377) in /var/conferences/classes/session/SessionManager.inc.php on line 57
(postgres7): UPDATE sessions SET user_id = ?, ip_address = ?, user_agent = ?, created = ?, last_used = ?, remember = ?, data = ? WHERE session_id = ? [ (0=>'1') (1=>'131.175.80.115') (2=>'Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9) Gecko/20080...') (3=>'1216296273') (4=>'1216299926') (5=>'0') (6=>'userId|s:1:"1";username|s:17:"admin-conferences";currentLocale|s...') (7=>'dddd21fc426fcfd7878a74e37e1241cc') ] (postgres7): SELECT * FROM conferences WHERE path = ? [ (0=>'lis') ] (postgres7): SELECT * FROM site (postgres7): SELECT * FROM site_settings (postgres7): SELECT COUNT(*) FROM plugin_settings WHERE plugin_name = ? AND setting_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'AcronPlugin') (1=>'crontab') (2=>'0') (3=>'0') ] (postgres7): UPDATE plugin_settings SET setting_value = ?, setting_type = ? WHERE plugin_name = ? AND setting_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'a:2:{i:0;a:3:{s:9:"className";s:20:"tasks.ReviewReminder";s:9:"f...') (1=>'object') (2=>'AcronPlugin') (3=>'crontab') (4=>'0') (5=>'0') ] (postgres7): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'AcronPlugin') (1=>'0') (2=>'0') ] (postgres7): SELECT * FROM sched_confs WHERE path = ? and conference_id = ? [ (0=>'pp') (1=>'1') ] (postgres7): SELECT COUNT(*) FROM roles WHERE conference_id = ? AND sched_conf_id = ? AND user_id = ? AND role_id = ? [ (0=>'1') (1=>'3') (2=>'1') (3=>'64') ] (postgres7): SELECT * FROM versions WHERE current = 1 (postgres7): SELECT COUNT(*) FROM sched_confs WHERE conference_id = ? AND end_date < now() [ (0=>'1') ] (postgres7): SELECT COUNT(*) FROM sched_confs WHERE conference_id = ? AND start_date < now() AND end_date > now() [ (0=>'1') ] (postgres7): SELECT * FROM sched_confs WHERE sched_conf_id = ? [ (0=>'3') ] (postgres7): SELECT * FROM tracks WHERE sched_conf_id = ? ORDER BY seq [ (0=>'3') ] (postgres7): SELECT * FROM track_settings WHERE track_id = ? [ (0=>'4') ] (postgres7): select version() (postgres7): SELECT COUNT(*) FROM (SELECT DISTINCT p.*, COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title, COALESCE(tal.setting_value, tapl.setting_value) AS track_abbrev FROM papers p INNER JOIN paper_presenters pa ON (pa.paper_id = p.paper_id) LEFT JOIN published_papers pp ON (pp.paper_id = p.paper_id) LEFT JOIN tracks t ON (t.track_id = p.track_id) LEFT JOIN edit_assignments ea ON (ea.paper_id = p.paper_id) LEFT JOIN users ed ON (ea.director_id = ed.user_id) LEFT JOIN review_assignments ra ON (ra.paper_id = p.paper_id) LEFT JOIN users re ON (re.user_id = ra.reviewer_id AND cancelled = 0) LEFT JOIN track_settings ttpl ON (t.track_id = ttpl.track_id AND ttpl.setting_name = ? AND ttpl.locale = ?) LEFT JOIN track_settings ttl ON (t.track_id = ttl.track_id AND ttl.setting_name = ? AND ttl.locale = ?) LEFT JOIN track_settings tapl ON (t.track_id = tapl.track_id AND tapl.setting_name = ? AND tapl.locale = ?) LEFT JOIN track_settings tal ON (t.track_id = tal.track_id AND tal.setting_name = ? AND tal.locale = ?) LEFT JOIN paper_settings ptl ON (p.paper_id = ptl.paper_id AND ptl.setting_name = ?) WHERE p.sched_conf_id = ? AND (p.status = 3) ) _ADODB_ALIAS_ [ (0=>'title') (1=>'it_IT') (2=>'title') (3=>'en_US') (4=>'abbrev') (5=>'it_IT') (6=>'abbrev') (7=>'en_US') (8=>'title') (9=>'3') ] (postgres7): SELECT DISTINCT p.*, COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title, COALESCE(tal.setting_value, tapl.setting_value) AS track_abbrev FROM papers p INNER JOIN paper_presenters pa ON (pa.paper_id = p.paper_id) LEFT JOIN published_papers pp ON (pp.paper_id = p.paper_id) LEFT JOIN tracks t ON (t.track_id = p.track_id) LEFT JOIN edit_assignments ea ON (ea.paper_id = p.paper_id) LEFT JOIN users ed ON (ea.director_id = ed.user_id) LEFT JOIN review_assignments ra ON (ra.paper_id = p.paper_id) LEFT JOIN users re ON (re.user_id = ra.reviewer_id AND cancelled = 0) LEFT JOIN track_settings ttpl ON (t.track_id = ttpl.track_id AND ttpl.setting_name = ? AND ttpl.locale = ?) LEFT JOIN track_settings ttl ON (t.track_id = ttl.track_id AND ttl.setting_name = ? AND ttl.locale = ?) LEFT JOIN track_settings tapl ON (t.track_id = tapl.track_id AND tapl.setting_name = ? AND tapl.locale = ?) LEFT JOIN track_settings tal ON (t.track_id = tal.track_id AND tal.setting_name = ? AND tal.locale = ?) LEFT JOIN paper_settings ptl ON (p.paper_id = ptl.paper_id AND ptl.setting_name = ?) WHERE p.sched_conf_id = ? AND (p.status = 3) ORDER BY t.seq ASC, pp.seq ASC LIMIT 25 OFFSET 0 [ (0=>'title') (1=>'it_IT') (2=>'title') (3=>'en_US') (4=>'abbrev') (5=>'it_IT') (6=>'abbrev') (7=>'en_US') (8=>'title') (9=>'3') ]
Warning: pg_query_params() [function.pg-query-params]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /var/conferences/lib/adodb/drivers/adodb-postgres7.inc.php on line 113
-1: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

ADOConnection._Execute(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., Array[10]) % line 860, file: adodb.inc.php
ADOConnection.Execute(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., Array[10]) % line 44, file: adodb-postgres7.inc.php
ADODB_postgres7.SelectLimit(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., 25, 0, Array[10], 0) % line 437, file: adodb-lib.inc.php
_adodb_pageexecute_all_rows(Object:ADODB_postgres7, SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., 25, 1, Array[10], 0) % line 2548, file: adodb.inc.php
ADOConnection.PageExecute(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., 25, 1, Array[10]) % line 144, file: DAO.inc.php

DB Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
(postgres7): UPDATE sessions SET user_id = ?, ip_address = ?, user_agent = ?, created = ?, last_used = ?, remember = ?, data = ? WHERE session_id = ? [ (0=>'1') (1=>'131.175.80.115') (2=>'Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9) Gecko/20080...') (3=>'1216296273') (4=>'1216299926') (5=>'0') (6=>'userId|s:1:"1";username|s:17:"admin-conferences";currentLocale|s...') (7=>'dddd21fc426fcfd7878a74e37e1241cc') ]

[asmecher]

Hi Daniela,

Can you turn on the show_stacktrace option in config.inc.php and post the result here?

Regards,
Alec Smecher
Public Knowledge Project Team

[Dragone]

Hi Alec,

this is the result:

viewtopic.php?f=3&t=3355

(postgres7): SELECT * FROM sessions WHERE session_id = ? [ (0=>'c02f529a13c1aeb3ba40e39fb388c8d6') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM users WHERE user_id = ? [ (0=>'1') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM user_settings WHERE user_id = ? [ (0=>'1') ]
________________________________________

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /var/conferences/lib/adodb/adodb.inc.php:377) in /var/conferences/classes/session/SessionManager.inc.php on line 57
________________________________________
(postgres7): UPDATE sessions SET user_id = ?, ip_address = ?, user_agent = ?, created = ?, last_used = ?, remember = ?, data = ? WHERE session_id = ? [ (0=>'1') (1=>'131.175.80.173') (2=>'Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.0.1) Gecko/2...') (3=>'1217491063') (4=>'1217494702') (5=>'0') (6=>'userId|s:1:"1";username|s:17:"admin-conferences";') (7=>'c02f529a13c1aeb3ba40e39fb388c8d6') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM conferences WHERE path = ? [ (0=>'lis') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM site
________________________________________
________________________________________
(postgres7): SELECT * FROM site_settings
________________________________________
________________________________________
(postgres7): SELECT COUNT(*) FROM plugin_settings WHERE plugin_name = ? AND setting_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'AcronPlugin') (1=>'crontab') (2=>'0') (3=>'0') ]
________________________________________
________________________________________
(postgres7): UPDATE plugin_settings SET setting_value = ?, setting_type = ? WHERE plugin_name = ? AND setting_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'a:2:{i:0;a:3:{s:9:"className";s:20:"tasks.ReviewReminder";s:9:"f...') (1=>'object') (2=>'AcronPlugin') (3=>'crontab') (4=>'0') (5=>'0') ]
________________________________________
________________________________________
(postgres7): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'AcronPlugin') (1=>'0') (2=>'0') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM sched_confs WHERE path = ? and conference_id = ? [ (0=>'2008') (1=>'1') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM conferences WHERE conference_id = ? [ (0=>'1') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM versions WHERE current = 1
________________________________________
________________________________________
(postgres7): SELECT COUNT(*) FROM sched_confs WHERE conference_id = ? AND end_date < now() [ (0=>'1') ]
________________________________________
________________________________________
(postgres7): SELECT COUNT(*) FROM sched_confs WHERE conference_id = ? AND start_date < now() AND end_date > now() [ (0=>'1') ]
________________________________________
________________________________________
(postgres7): SELECT * FROM sched_confs WHERE sched_conf_id = ? [ (0=>'4') ]
________________________________________

Warning: Cannot modify header information - headers already sent by (output started at /var/conferences/lib/adodb/adodb.inc.php:377) in /var/conferences/classes/template/TemplateManager.inc.php on line 292

Warning: Cannot modify header information - headers already sent by (output started at /var/conferences/lib/adodb/adodb.inc.php:377) in /var/conferences/classes/template/TemplateManager.inc.php on line 295

thanks again
Daniela

[asmecher]

Hi Daniela,

Are you sure the stack trace option is enabled? The output above looks like the debug option is enabled, but not the stack trace.

Regards,
Alec Smecher
Public Knowledge Project Team

[Dragone]

Sorry Alec,

this is what you asked me:

(postgres7): SELECT * FROM sessions WHERE session_id = ? [ (0=>'69a90707a7a1ed8137356d8acb2f1da3') ] (postgres7): SELECT * FROM users WHERE user_id = ? [ (0=>'2') ] (postgres7): SELECT * FROM user_settings WHERE user_id = ? [ (0=>'2') ]
Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /var/conferences/lib/adodb/adodb.inc.php:377) in /var/conferences/classes/session/SessionManager.inc.php on line 57
(postgres7): UPDATE sessions SET user_id = ?, ip_address = ?, user_agent = ?, created = ?, last_used = ?, remember = ?, data = ? WHERE session_id = ? [ (0=>'2') (1=>'131.175.80.173') (2=>'Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.0.1) Gecko/2...') (3=>'1217574804') (4=>'1217575212') (5=>'0') (6=>'userId|i:2;username|s:11:"marchitelli";signedInAs|s:1:"1";') (7=>'69a90707a7a1ed8137356d8acb2f1da3') ] (postgres7): SELECT * FROM conferences WHERE path = ? [ (0=>'lis') ] (postgres7): SELECT * FROM site (postgres7): SELECT * FROM site_settings (postgres7): SELECT COUNT(*) FROM plugin_settings WHERE plugin_name = ? AND setting_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'AcronPlugin') (1=>'crontab') (2=>'0') (3=>'0') ] (postgres7): UPDATE plugin_settings SET setting_value = ?, setting_type = ? WHERE plugin_name = ? AND setting_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'a:2:{i:0;a:3:{s:9:"className";s:20:"tasks.ReviewReminder";s:9:"f...') (1=>'object') (2=>'AcronPlugin') (3=>'crontab') (4=>'0') (5=>'0') ] (postgres7): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = ? AND conference_id = ? AND sched_conf_id = ? [ (0=>'AcronPlugin') (1=>'0') (2=>'0') ] (postgres7): SELECT * FROM sched_confs WHERE path = ? and conference_id = ? [ (0=>'2009') (1=>'1') ] (postgres7): SELECT COUNT(*) FROM roles WHERE conference_id = ? AND sched_conf_id = ? AND user_id = ? AND role_id = ? [ (0=>'1') (1=>'4') (2=>'2') (3=>'64') ] (postgres7): SELECT * FROM versions WHERE current = 1 (postgres7): SELECT COUNT(*) FROM sched_confs WHERE conference_id = ? AND end_date < now() [ (0=>'1') ] (postgres7): SELECT COUNT(*) FROM sched_confs WHERE conference_id = ? AND start_date < now() AND end_date > now() [ (0=>'1') ] (postgres7): SELECT * FROM sched_confs WHERE sched_conf_id = ? [ (0=>'4') ] (postgres7): SELECT * FROM tracks WHERE sched_conf_id = ? ORDER BY seq [ (0=>'4') ] (postgres7): SELECT * FROM track_settings WHERE track_id = ? [ (0=>'5') ] (postgres7): SELECT * FROM track_settings WHERE track_id = ? [ (0=>'6') ] (postgres7): select version() (postgres7): SELECT COUNT(*) FROM (SELECT DISTINCT p.*, COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title, COALESCE(tal.setting_value, tapl.setting_value) AS track_abbrev FROM papers p INNER JOIN paper_presenters pa ON (pa.paper_id = p.paper_id) LEFT JOIN published_papers pp ON (pp.paper_id = p.paper_id) LEFT JOIN tracks t ON (t.track_id = p.track_id) LEFT JOIN edit_assignments ea ON (ea.paper_id = p.paper_id) LEFT JOIN users ed ON (ea.director_id = ed.user_id) LEFT JOIN review_assignments ra ON (ra.paper_id = p.paper_id) LEFT JOIN users re ON (re.user_id = ra.reviewer_id AND cancelled = 0) LEFT JOIN track_settings ttpl ON (t.track_id = ttpl.track_id AND ttpl.setting_name = ? AND ttpl.locale = ?) LEFT JOIN track_settings ttl ON (t.track_id = ttl.track_id AND ttl.setting_name = ? AND ttl.locale = ?) LEFT JOIN track_settings tapl ON (t.track_id = tapl.track_id AND tapl.setting_name = ? AND tapl.locale = ?) LEFT JOIN track_settings tal ON (t.track_id = tal.track_id AND tal.setting_name = ? AND tal.locale = ?) LEFT JOIN paper_settings ptl ON (p.paper_id = ptl.paper_id AND ptl.setting_name = ?) WHERE p.sched_conf_id = ? AND (p.status = 3) ) _ADODB_ALIAS_ [ (0=>'title') (1=>'it_IT') (2=>'title') (3=>'it_IT') (4=>'abbrev') (5=>'it_IT') (6=>'abbrev') (7=>'it_IT') (8=>'title') (9=>'4') ] (postgres7): SELECT DISTINCT p.*, COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title, COALESCE(tal.setting_value, tapl.setting_value) AS track_abbrev FROM papers p INNER JOIN paper_presenters pa ON (pa.paper_id = p.paper_id) LEFT JOIN published_papers pp ON (pp.paper_id = p.paper_id) LEFT JOIN tracks t ON (t.track_id = p.track_id) LEFT JOIN edit_assignments ea ON (ea.paper_id = p.paper_id) LEFT JOIN users ed ON (ea.director_id = ed.user_id) LEFT JOIN review_assignments ra ON (ra.paper_id = p.paper_id) LEFT JOIN users re ON (re.user_id = ra.reviewer_id AND cancelled = 0) LEFT JOIN track_settings ttpl ON (t.track_id = ttpl.track_id AND ttpl.setting_name = ? AND ttpl.locale = ?) LEFT JOIN track_settings ttl ON (t.track_id = ttl.track_id AND ttl.setting_name = ? AND ttl.locale = ?) LEFT JOIN track_settings tapl ON (t.track_id = tapl.track_id AND tapl.setting_name = ? AND tapl.locale = ?) LEFT JOIN track_settings tal ON (t.track_id = tal.track_id AND tal.setting_name = ? AND tal.locale = ?) LEFT JOIN paper_settings ptl ON (p.paper_id = ptl.paper_id AND ptl.setting_name = ?) WHERE p.sched_conf_id = ? AND (p.status = 3) ORDER BY t.seq ASC, pp.seq ASC LIMIT 25 OFFSET 0 [ (0=>'title') (1=>'it_IT') (2=>'title') (3=>'it_IT') (4=>'abbrev') (5=>'it_IT') (6=>'abbrev') (7=>'it_IT') (8=>'title') (9=>'4') ]
Warning: pg_query_params() [function.pg-query-params]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /var/conferences/lib/adodb/drivers/adodb-postgres7.inc.php on line 113
-1: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

ADOConnection._Execute(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., Array[10]) % line 860, file: adodb.inc.php
ADOConnection.Execute(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., Array[10]) % line 44, file: adodb-postgres7.inc.php
ADODB_postgres7.SelectLimit(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., 25, 0, Array[10], 0) % line 437, file: adodb-lib.inc.php
_adodb_pageexecute_all_rows(Object:ADODB_postgres7, SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., 25, 1, Array[10], 0) % line 2548, file: adodb.inc.php
ADOConnection.PageExecute(SELECT DISTINCT
p.*,
COALESCE(ttl.setting_value, ttpl.setting_value) AS track_title,
COALESCE(tal.setting_value, tap..., 25, 1, Array[10]) % line 144, file: DAO.inc.php

DB Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Stack Trace:
File: /var/conferences/classes/submission/director/DirectorSubmissionDAO.inc.php line 260
Function: DAO->retrieveRange("SELECT DISTINCT p.*, COALESCE(ttl.setting_value, ttpl.se...", Array(10), Object(DBResultRange))

File: /var/conferences/classes/submission/director/DirectorSubmissionDAO.inc.php line 398
Function: DirectorSubmissionDAO->getUnfilteredDirectorSubmissions("4", Null, Null, Null, Null, Null, Null, Null, "p.status = 3", 2, Object(DBResultRange))

File: /var/conferences/pages/director/DirectorHandler.inc.php line 95
Function: DirectorSubmissionDAO->getDirectorSubmissionsAccepted("4", Null, Null, Null, Null, Null, Null, Null, Object(DBResultRange))

File: (unknown) line (unknown)
Function: DirectorHandler::submissions(Array(1))

File: /var/conferences/index.php line 56
Function: call_user_func(Array(2), Array(1))

File: /var/conferences/index.php line 67
Function: handleRequest()

(postgres7): UPDATE sessions SET user_id = ?, ip_address = ?, user_agent = ?, created = ?, last_used = ?, remember = ?, data = ? WHERE session_id = ? [ (0=>'2') (1=>'131.175.80.173') (2=>'Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.0.1) Gecko/2...') (3=>'1217574804') (4=>'1217575212') (5=>'0') (6=>'userId|i:2;username|s:11:"marchitelli";signedInAs|s:1:"1";') (7=>'69a90707a7a1ed8137356d8acb2f1da3') ]

thank you!

[asmecher]

Hi Daniela,

Can you try the patch at http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=3729 and see if it fixes the problem?

Regards,
Alec Smecher
Public Knowledge Project Team

[Dragone]

Hi,
now it works!
Thanks
Daniela