[Eikinskjaldi]

I am guessing the query that comes out of your hook looks absolutely nothing like the query you ran in phpmyadmin.

for one thing, if you alias a table, you have to always refer to that table by the alias

,user.username AS to_username";
"LEFT JOIN " . TABLE_PREFIX . "user AS u ON(u.userid = post.to_user)";

By rights this query should complain "unknown column user.username in field list"

I am guessing, however, that user is being called again somewhere else in the query so you have a user table and a user as u table and thereby pulling duplicate rows.

It's just a guess though, what is really needed is the actual query your hook generates.