-
Bug
-
Resolution: Fixed
-
None
-
3.0.x
-
None
-
PHP Environment:
Database:
There are some problems with the use of SELECT DISTINCT when searching and viewing results as topics. PostgreSQL requires that if you're using SELECT DISTINCT, any column you are sorting by must be in the SELECT list (in order of when it is being sorted). ie "SELECT DISTINCT foo ... ORDER BY bar" would fail, you'd need to have bar selected like "SELECT DISTINCT bar, foo ... ORDER BY bar" would work.
The problem shows itself in a couple instances in search.php. Line 265:
$sql = 'SELECT DISTINCT t.topic_id
|
FROM ' . POSTS_TABLE . ' p, ' . TOPICS_TABLE . " t
|
WHERE p.post_time > $last_post_time
|
AND t.topic_approved = 1
|
AND p.topic_id = t.topic_id
|
$m_approve_fid_sql
|
" . ((sizeof($ex_fid_ary)) ? ' AND p.forum_id NOT IN (' . implode(',', $ex_fid_ary) . ')' : '') . '
|
ORDER BY t.topic_last_post_time DESC';
|
Line 301
$sql = "SELECT DISTINCT p.topic_id
|
FROM $sort_join" . POSTS_TABLE . ' p, ' . TOPICS_TABLE . " t
|
WHERE t.topic_replies = 0
|
AND p.topic_id = t.topic_id
|
$m_approve_fid_sql
|
" . ((sizeof($ex_fid_ary)) ? ' AND p.forum_id NOT IN (' . implode(',', $ex_fid_ary) . ')' : '') . "
|
$sql_sort";
|
Line 342
$sql = "SELECT DISTINCT p.topic_id
|
FROM $sort_join" . TOPICS_TABLE . ' t, ' . POSTS_TABLE . ' p
|
WHERE p.post_time > ' . $user->data['user_lastvisit'] . "
|
AND t.topic_id = p.topic_id
|
$m_approve_fid_sql
|
" . ((sizeof($ex_fid_ary)) ? ' AND p.forum_id NOT IN (' . implode(',', $ex_fid_ary) . ')' : '') . "
|
$sql_sort";
|
My recommendation would be to simply add the column used to sort as the first in the SELECT list, so for example
$sql = 'SELECT DISTINCT ' . $sort_by_sql[$sort_key] . ', p.topic_id
|
FROM $sort_join" . TOPICS_TABLE . ' t, ' . POSTS_TABLE . ' p
|
WHERE p.post_time > ' . $user->data['user_lastvisit'] . "
|
AND t.topic_id = p.topic_id
|
$m_approve_fid_sql
|
" . ((sizeof($ex_fid_ary)) ? ' AND p.forum_id NOT IN (' . implode(',', $ex_fid_ary) . ')' : '') . "
|
$sql_sort";
|
This additional selected data shouldn't cause an issue further down the stream as everything is referenced as an associative array.