MySQL Forums
Forum List  »  Newbie

Re: Tree behavior - Make this query compatible with mysql with sql_mode=only_full_group_by
Posted by: Peter Brawley
Date: August 21, 2017 02:43PM

The only_full_group_by sql_mode setting protects you from writing aggregating queries that look correct but aren't because they display columns which ...

(i) aren't Grouped By,

(ii) don'r aggregate, and

(iii) aren't functionally dependent on the query Group By expression(s),

... so those Select expressions can return arbitrary results.

MySQL provides a workaround function for avoiding the error message you're receiving: change every such column_arg to ANY(column_arg). The name of the func makes the situation clear: the values are undetermined, arbitrary.

There are times when such arbitrary results are Ok, but unless you can prove that your query result will never be changed by adding n.industry_naf and n.rght-n.lft to the Group By list, this isn't one of those times.

To put it another way, the query as written is valid if and only if adding n.industry_naf and n.rght-n.lft to the Group By list can never change the result. So if you can prove that, then just add those expressions to the Group By list and you're done.

But it's pretty clear that won't work. You need another approach.

Discussion of a problem much like yours at https://stackoverflow.com/questions/1316126/how-can-i-find-all-siblings-to-my-node-and-its-anchestors-in-a-hierarchical-cate suggests this is another tree problem for which the Nested Sets model is less than helpful.

The edge list model (often mislabelled the "adjacent list" model in the SQL world) does provide a solution for your problem (examples in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html should get you started), and MySQL 8's Common Table Expressions make such edge list queries run about ten times faster.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.