MySQL Bad Idea #384
In MySQL, you can legally execute the following statement:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;
The statement was taken from here:
So what does this statement even mean? What will be returned in the c.name projection? MAX(c.name)? ANY(c.name)? FIRST(c.name)? NULL? 42? According to the documentation, ANY(c.name) would best describe what’s going on. This peculiar syntax is probably quite clever for those few that really know when this is useful. When they know exactly, that o.custid and c.name have a 1:1 correlation, and they can speed things up a little by avoiding writing things like MAX(c.name), or by adding c.name to the GROUP BY clause (“yes, saved yet another 8 characters”).
But the bulk of newbie MySQL users will be confused by this.
- First, they will be confused because they don’t get the c.name they’d expect.
- Secondly, they will eventually switch over to another database that gets these things right, and be frustrated all over again, over the funny syntax errors, such as ORA-00979 not a GROUP BY expression
- MySQL users: stop using this non-feature. It will only cause pain and suffering, even if you know how/why it works. SQL’s GROUP BY was not meant to work that way.
- MySQL: Deprecate this non-feature.
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)