-
Bug
-
Resolution: Fixed
-
None
-
3.0.x
-
None
-
PHP Environment:
Database:
$query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query);
|
This regexp in includes/db/sqlite.php at around line 98 handles the fact that SQLite does not support parentheses in FROM expressions. However it does not match and fix complex FROM expressions such as (from includes/ucp/ucp_main.php at around 343):
SELECT t.* , tp.topic_posted FROM (phpbb_topics t, phpbb_topics_watch tw LEFT JOIN phpbb_topics_posted tp ON (tp.topic_id = t.topic_id AND tp.user_id = 2) ) WHERE tw.user_id = 2 AND t.topic_id = tw.topic_id ORDER BY t.topic_last_post_time DESC
|
This results in an error when trying to view subscriptions in the UCP.
I'm not sure whether the parentheses around the entire FROM expression are necessary for any DBMS, but parentheses directly around "phpbb_topics t, phpbb_topics_watch tw" are necessary for MySQL 5 as per http://www.phpbb.com/bugs/viewreport.php?b=1164&prj=0" target="_blank and http://www.phpbb.com/bugs/viewreport.php?b=1144&prj=0.
The following works with both MySQL 5 and SQLite.
Replace, in includes/ucp/ucp_main.php at around line 337:
$sql = "SELECT t.* $sql_f_select $sql_t_select
|
FROM (" . TOPICS_TABLE . ' t, ' . TOPICS_WATCH_TABLE . " tw
|
$sql_join )
|
WHERE tw.user_id = " . $user->data['user_id'] . '
|
AND t.topic_id = tw.topic_id
|
ORDER BY t.topic_last_post_time DESC';
|
$result = $db->sql_query_limit($sql, $config['topics_per_page'], $start);
|
With:
$sql = "SELECT t.* $sql_f_select $sql_t_select
|
FROM (" . TOPICS_TABLE . ' t, ' . TOPICS_WATCH_TABLE . " tw)
|
$sql_join
|
WHERE tw.user_id = " . $user->data['user_id'] . '
|
AND t.topic_id = tw.topic_id
|
ORDER BY t.topic_last_post_time DESC';
|