[solved] ORDER BY ASC LIMIT 0,25 Bug?

[ponsfrilus]

In a updated 2.3.1 install, searching for user with manager role (conf/manager/people/all ) returns the following queries:

...
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'CustomLocalePlugin' AND conference_id = '1' AND sched_conf_id = '0'
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'TranslatorPlugin' AND conference_id = '0' AND sched_conf_id = '0'
(mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'AcronPlugin' AND conference_id = '0' AND sched_conf_id = '0'
(mysql): SELECT COUNT(*) FROM roles WHERE conference_id = 0 AND sched_conf_id = 0 AND user_id = 1 AND role_id = 1
(mysql): SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests'), roles AS r WHERE u.user_id = r.user_id AND r.conference_id = 1 AND LOWER(u.first_name) LIKE LOWER('%James%')
(mysql): SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests'), roles AS r WHERE u.user_id = r.user_id AND r.conference_id = 1 AND LOWER(u.first_name) LIKE LOWER('%James%') ORDER BY ASC LIMIT 0,25
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC LIMIT 0,25' at line 1
ADOConnection._Execute(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests'), roles...) % line 845, file: adodb.inc.php
ADOConnection.Execute(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., Array[3]) % line 487, file: adodb-mysql.inc.php
ADODB_mysql.SelectLimit(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., 25, 0, Array[3], 0) % line 437, file: adodb-lib.inc.php
_adodb_pageexecute_all_rows(Object:ADODB_mysql, SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., 25, 1, Array[3], 0) % line 2548, file: adodb.inc.php
ADOConnection.PageExecute(SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHER..., 25, 1, Array[3]) % line 166, file: DAO.inc.php
DB Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC LIMIT 0,25' at line 1

The error is that the Order by is not defined ORDER BY ASC LIMIT 0,25 .

In ocs/classes/security/RoleDAO.inc.php ligne 270, change

Code: Select all

$searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');

to

Code: Select all

$searchSql .= ($this->getSortMapping($sortBy) ?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');

This is a patch:

Code: Select all

--- RoleDAO.inc.php
+++ RoleDAO.inc.php
@@ -267,7 +267,7 @@
             break;
       }
 
-      $searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
+      $searchSql .= ($this->getSortMapping($sortBy) ?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
 
       $result =& $this->retrieveRange(
 


[ponsfrilus]

See this report: http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=5369

[asmecher]

Hi ponsfrilus,

Thanks for reporting; I've posted a link to a patch at the bottom of that Bugzilla entry.

Regards,
Alec Smecher
Public Knowledge Project Team