3 ms·

'id' doesn't appear in the group by list and it isn't being used in an aggregate function. I'm actually curious: what id would a MySQL user expect to be displa

• rimantas 15y ago
  > I'm actually curious: what id would a MySQL user expect to be displayed > for this query? MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group: > Do not use this feature if the columns you omit from the GROUP BY part > are not constant in the group. The server is free to return any value > from the group, so the results are indeterminate unless all values are > the same. Their example in documentation: SELECT order.custid, customer.name, MAX(payments) FROM order,customer WHERE order.custid = customer.custid GROUP BY order.custid;
  • jeffdavis 15y ago
    "MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group" That's what errors are for, not documentation. It is pretty easy to forget something in the group by, and documentation won't help with that. The dangerous thing is that the result returned from such a nonsense query looks valid in many cases, while being wrong in subtle ways. PostgreSQL detects when the query is valid, and executes it if so. So, if you do a GROUP BY customer_id (a key column), you can also see customer_name without adding it to the GROUP BY list. But if you group by customer_zipcode (not a key), and try to select the customer_name, it will throw an error.