Roles->descendants() not working

[orinoco]

With mysql >= 5.7, this does not work anymore - it returns an empty array (and hides the SQL error). The issue is with the GROUP BY clause, whose behaviour was changed with this update.

http://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by

The easiest solution is to revert back to previous mysql behaviour, which can be accomplished by setting sql-mode to TRADITIONAL. This can be done at runtime globally, per session or at startup in a config file.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Easiest for me was to update my.cnf to contain sql_mode=TRADITIONAL under [mysqld], which removes the problematic ONLY_FULL_GROUP_BY mode.

Of course a better solution would be to update the SQL query.

[abesapien]

Nice find. I recommend submitting a patch as a merge request.

[abiusx]

If I remember correctly, there was a SQL that you send to the database instead of changing my.cnf to make the session behavior traditional. I don’t remember the details, if someone does please post. -A

…

On Apr 27, 2017, at 8:57 AM, shawn faison ***@***.***> wrote: Nice find. I recommend submitting a patch as a merge request. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#89 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABVjW0yGzNLUr8xv3lDLQqByoRDcUxQzks5r0LrcgaJpZM4MO84A>.

[cw1427]

reproduced this issue in mysql 5.7.24. Do we have plan to update the SQL query on descendantsConditional(...) function?

[abiusx]

We can, but I have no recollection of it at the moment. Can you take a look at the query and see if you can do anything about it?

…

On Dec 3, 2018, at 10:19 PM, Shawn Chen ***@***.***> wrote: reproduced this issue in mysql 5.7.24. Do we have plan to update the SQL query on descendantsConditional(...) function? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#89 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABVjW4HZ6kBWLV26VLMCd0cBY-zrQrY9ks5u1em_gaJpZM4MO84A>.

[cw1427]

The query should be something like this:
select node.*,(count(parent.id)-1 - (sub_tree.innerDepth)) as Depth from perm_roles as node, perm_roles as parent, perm_roles as sub_parent, (select node.id, (count(parent.id) -1) as innerDepth from perm_roles as node, perm_roles as parent where node.lft between parent.Lft and parent.rght and node.id =1 group by node.id order by node.lft) as sub_tree where node.lft between parent.lft and parent.rght and node.lft between sub_parent.lft and sub_parent.rght and sub_parent.id = sub_tree.id group by node.id having Depth >0

And below is the error.
ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sub_tree.innerDepth' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Yeah, I'm trying to take a deep look at it.

[cw1427]

Add a aggregrate function group_concat in the select list for fix:
#114