MySQL 5.0.12 - list is not in GROUP BY clause and contains nonaggregated column?

Question

Why do I get this error below on mysqlnd 5.0.12-dev:

1055 - Expression #29 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_name.p2.url' which is not

functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Query:

SELECT p.* , 
    p2.article_id AS parent_id  , 
    p2.url AS parent_url  , 
    p3.article_id AS parent_parent_id  , 
    p3.url AS parent_parent_url  , 
    p3.title AS parent_parent_title   

FROM article AS p  
LEFT JOIN article AS p2  
ON p2.article_id = p.parent_id  
AND p.article_id <> p2.article_id  

LEFT JOIN article AS p3  
ON p3.article_id = p2.parent_id  
AND p2.article_id <> p3.article_id  

WHERE p.url = 'contact'  
AND p.type = 'page'  
AND p.hide = '0'  
GROUP BY p.article_id  
ORDER BY p.backdated_on DESC 

The query works fine on mysqlnd 5.0.11-dev.

Any ideas what is going on?

Answer 1

As it seems your group by clause contains only p.article_id whereas your selection has

p.* , 
    p2.article_id AS parent_id  , 
    p2.url AS parent_url  , 
    p3.article_id AS parent_parent_id  , 
    p3.url AS parent_parent_url  , 
    p3.title AS parent_parent_title

Either include all of the select query columns in group by or wrap them in a aggregate function such as sum(),count(),max() etc. What do you actually want from this query?

Answer 2

The error is pretty clear:

functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

By default, MySQL allows the structure of the query as you have it. When your server was updated, someone (wisely, IMHO) set the SQL mode so the engine would not support this functionality.

It is unclear what you want to do. But I'm guessing that the GROUP BY is not even necessary:

SELECT p.* , 
       p2.article_id AS parent_id  , 
       p2.url AS parent_url  , 
       p3.article_id AS parent_parent_id  , 
       p3.url AS parent_parent_url  , 
       p3.title AS parent_parent_title   
FROM article p LEFT JOIN
     article p2  
     ON p2.article_id = p.parent_id AND
     p.article_id <> p2.article_id LEFT JOIN
     article p3  
     ON p3.article_id = p2.parent_id AND
     p2.article_id <> p3.article_id  
WHERE p.url = 'contact' AND p.type = 'page' AND p.hide = '0'  
ORDER BY p.backdated_on DESC ;

If, somehow, you are getting duplicates, then you might want SELECT DISTINCT.

If that still doesn't solve your problem, ask another question (because this one already has multiple answers that address the syntax issue in the question). Provide sample data and desired results, as well as the query that you get to work.