Actually, looking at few queries right now searching for a potential example, I spotted a potentially problematic one, the one in loadBoard.
The query is cached, and the query itself is altered by hooks via integrate_load_board_query before being cached, but the hook is run only to refresh the cache.
So what does it happen?
If an addon joins a table based on... some condition (e.g. belonging to a particular group, then you may get inconsistent results.
Let's say we use the hook to add something like this:
if ($user_info['is_admin'])
{
$select_columns[] = 'a.new_column';
$select_tables[] = 'LEFT JOIN {db_prefix}my_table AS a ON (a.id_board = b.id_board);
}
if the first user visiting the page is not an admin, the query will be cached as "non-admin" and the new table will not be joined, when the admin will visit the page, the content of the query will be retrieved from the cache and the new_column field will not be there for the admin.
Conflict example is rather easy:
$select_columns[] = 'a.new_column';
$select_tables[] = 'LEFT JOIN {db_prefix}my_table AS a ON (a.id_board = b.id_board);
$select_columns[] = 'a.some_column';
$select_tables[] = 'LEFT JOIN {db_prefix}some_table AS a ON (a.id_board = b.id_board);
Of course these are not something I found in real example, but they are not far from reality.