4 ms·

Why isn't that valid SQL?

by rudiger 15y ago

ericflo 15y ago
'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 displayed for this query?
- 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.
mey 15y ago
Ponder: Which id is returned when you have more then one last name that is the same?
justin_vanw 15y ago
Well, lets say your rows are: id|score|last_name 1|11|smith 2|22|jones 3|33|smith 4|44|jones Query we are pretending is valid is: SELECT id, last_name FROM table GROUP BY last_name; What rows are returned? I expect to see something like: ?|?|smith ?|?|jones However, what ? is isn't clear. Could we get a row like 1|33|smith ? In SQL, all selected columns must be part of the group by or inside an aggregate.

Why isn't that valid SQL?

by rudiger 15y ago

Why isn't that valid SQL?

'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 displayed for this query?

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.

mey 15y ago

Ponder: Which id is returned when you have more then one last name that is the same?

justin_vanw 15y ago

Well, lets say your rows are: id|score|last_name 1|11|smith 2|22|jones 3|33|smith 4|44|jones Query we are pretending is valid is: SELECT id, last_name FROM table GROUP BY last_name; What rows are returned? I expect to see something like: ?|?|smith ?|?|jones However, what ? is isn't clear. Could we get a row like 1|33|smith ? In SQL, all selected columns must be part of the group by or inside an aggregate.