GROUP_CONCAT in subquery yields unpredicable results

Question

I'm having an issue with a group_concat statement within a subquery:

select group_concat(name separator " ")title,tmp.p_i
from (
    select name,blog.db_blog_posts_title_words_evanhendler.p_i
    from blog.db_blog_posts_title_words_evanhendler
    join words.words_blog_posts_title
    on blog.db_blog_posts_title_words_evanhendler.s_i=words.words_blog_posts_title.id
    order by blog.db_blog_posts_title_words_evanhendler.id
)tmp
group by tmp.p_i

yields the desired result:

+-------------------+------+
| title             | p_i  |
+-------------------+------+
| This is the title |    1 |
| This is the title |    2 |
| This is the title |    3 |
| This is the title |    4 |
+-------------------+------+

However:

select title from blog.db_blog_posts_title_evanhendler left join (
    select group_concat(name separator " ")title,tmp.p_i
    from (
         select name,blog.db_blog_posts_title_words_evanhendler.p_i
         from blog.db_blog_posts_title_words_evanhendler
        join words.words_blog_posts_title
        on blog.db_blog_posts_title_words_evanhendler.s_i=words.words_blog_posts_title.id
        order by blog.db_blog_posts_title_words_evanhendler.id
    )tmp
    group by tmp.p_i
)tmp 
on blog.db_blog_posts_title_evanhendler.id=tmp.p_i;

Yields:

+-------------------+
| title             |
+-------------------+
| is the title This |
| This is the title |
| This is the title |
| This is the title |
+-------------------+

Answer 1

You need an optional parameter ORDER BY within the group_concat. You got lucky the first few times with the order,but the additional join is causing the engine to put it in a different order. So....

GROUP_CONCAT(name ORDER BY Some_field_or_Fields_which_will_put_name_in_right_Order_For_you 
SEPARATOR " " )

Excerpt from Docs:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. ...

Again if you don't have anything to order by to put the "names" being combined in the right order, then you're out of luck. As order is not guaranteed otherwise!