phpBB • Free and Open Source Forum Software

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:

Code: Select all: $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

Code: Select all: $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

Code: Select all: $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

Code: Select all: $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.

This problem also shows up in fulltext_phpbb.php, when searching using topics. I similiar hack would need to be done to the SELECTing for it to work in PostgreSQL.

Oh and also looking through fulltext_phpbb.php, the queries will also fail due to the way GROUP BY is used. In PostgreSQL (and per Ansi SQL), if you're using GROUP BY, any column being SELECTed or being used in sorting (ORDER BY) and is not being used in an aggregate function must be listed in the GROUP BY clause. For example:

Code: Select all: SELECT m.post_id, COUNT(DISTINCT m.word_id) as matches FROM phpbb_posts p, phpbb_search_wordmatch m, phpbb_search_wordlist w WHERE w.word_text IN ('sasd') AND m.word_id = w.word_id AND w.word_common <> 1 AND p.post_id = m.post_id GROUP BY m.post_id ORDER BY p.post_time DESC

Will fail because p.post_time must be in the GROUP BY clause ("GROUP BY m.post_id, p.post_time" is fine because grouping is done left to right and won't group rows with the same post_time but different post_ids due to the order of precedence)