Linked ticket with changeset: r5831
Action performed by DavidMJ (Former Team Member) on Apr 22nd 2006, 18:42
- Code: Select all
$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):
- Code: Select all
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.ph ... 1164&prj=0 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:
- Code: Select all
$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:
- Code: Select all
$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';