CODEWITHSUNDEEP
mysqlgroup-by
Alex
Alex

Reputation: 2335

MySQL order by after grouping works unexpectedly

how is it that a query like this works in MySQL (on 5.6 right now):

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

I am very confused, I am used to SQL Server, where it would complain that the node.lft is not in the list of selected columns, but here in MySQL it works great,

I appreciate your help,

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You are correct to be confused. MySQL allows this behavior -- and not only in the order by, but also in the having and select clauses as well.

Basically, MySQL uses a value from an indeterminate matching row. In general, this is a (mis)feature in the language and leads to unintentional errors -- in most cases.

It is reasonable when the values of node.lft are the same on all rows in a group. In that case, though, using something like MIN(node.lft) incurs very little additional overhead.

Some notes about this behavior:

  • MySQL documentation -- through version 5.6 -- warns specifically about this feature.
  • ANSI supports a very similar feature. Basically, if the columns being referenced are functionally dependent (i.e. uniquely determined by) the columns in the group by, then the columns are allowed with no aggregation functions.
  • MySQL documentation in version 5.7 drops the full explanation of the feature, probably because the database is moving toward the ANSI standard in this area.

Upvotes: 1

Related Questions