4 ms·

The group by behavior is useful. I wish Postgres did that.

by christophilus 4y ago

unterdenlinden 4y ago
You can do that with window functions in Postgres: SELECT MAX(salary) OVER (), first_name, last_name FROM employee;
- systems 4y ago
  exactly, SQL being a declarative language, you should always prefer explicit over implicit SQL bugs are very hard to detect, when the query return a result that looks right, and because the language is declarative its easy to do those mistakes
deathanatos 4y ago
Use of that group by behavior is a bug, nearly every time I've seen it. (Though usually I'm seeing it in MySQL.) Columns that are not part of the group, an aggregate of the group, or a functional dependency of the group aren't stable — there are multiple rows that could supply the value, and they needn't have the same value.
- kwoff 4y ago
  Yeah, I've seen that bug in a monitoring graph that was showing data for say every 5 minutes but the (MySQL) database table had data per minute; so you'd see a spike in the graph, but refresh a minute later and voila no spike anymore.
dspillett 4y ago
Most DBs don't (and won't ever) support that because the results become undefined. You could get different values out for the same query in different versions of the DB (due to query planner changes) or even within the same version and the same database over time (as the relative sizes of your tables change so the planner choses a different route, or index options are changed, so the planner is forced to go with a different route or has the option of a better one). Though in a world where “eventual consistency” is accepted, may be “eeny, meeny, miny, moe” will be more generally considered OK at some point :) At least SQLite tries to be consistent (taking the value from the row where the aggregate found its value) where that is possible (which it often isn't) which mySQL (which also allows non-grouped non-aggregated columns in the projection list) does not.

The group by behavior is useful. I wish Postgres did that.

unterdenlinden 4y ago

You can do that with window functions in Postgres: SELECT MAX(salary) OVER (), first_name, last_name FROM employee;

systems 4y ago
exactly, SQL being a declarative language, you should always prefer explicit over implicit SQL bugs are very hard to detect, when the query return a result that looks right, and because the language is declarative its easy to do those mistakes

deathanatos 4y ago

Use of that group by behavior is a bug, nearly every time I've seen it. (Though usually I'm seeing it in MySQL.) Columns that are not part of the group, an aggregate of the group, or a functional dependency of the group aren't stable — there are multiple rows that could supply the value, and they needn't have the same value.

kwoff 4y ago
Yeah, I've seen that bug in a monitoring graph that was showing data for say every 5 minutes but the (MySQL) database table had data per minute; so you'd see a spike in the graph, but refresh a minute later and voila no spike anymore.

dspillett 4y ago

Most DBs don't (and won't ever) support that because the results become undefined. You could get different values out for the same query in different versions of the DB (due to query planner changes) or even within the same version and the same database over time (as the relative sizes of your tables change so the planner choses a different route, or index options are changed, so the planner is forced to go with a different route or has the option of a better one). Though in a world where “eventual consistency” is accepted, may be “eeny, meeny, miny, moe” will be more generally considered OK at some point :) At least SQLite tries to be consistent (taking the value from the row where the aggregate found its value) where that is possible (which it often isn't) which mySQL (which also allows non-grouped non-aggregated columns in the projection list) does not.