4 ms·
Surprised they let you pick something without defining the aggregate as it seems really dangerous. Not entirely sure how far it extends but I’ve noticed that P
by aidos 3y ago
Surprised they let you pick something without defining the aggregate as it seems really dangerous.
Not entirely sure how far it extends but I’ve noticed that Postgres will let you select any rows from the table that way if you’ve included the primary key in the group by. Back when I used sql server / MySQL you needed to specify them all in the group by.
- dspillett 3y agoSQL Server forces determinism here: anything projected when there is a GROUP BY must be one of the grouping properties, an aggregate, or a constant. mySql does allow other columns to be projected, which has always felt wrong to me as I like my results from the DB to be dertemanistic. I wasn't aware sqlite allowed it too, though I've not directly used sqlite much. It is something that causes issues migrating to other DBs (or when trying to be cross-DB compatible). Often it is used accidentally and by chance gives good results, where is is used deliberately IME it is where the same value will always return due to properties of the that data being queried (in which case the "fix" for other DBs is to use an aggregate like MIN or MAX). Sometimes accidentally use leads to subtle bugs which, as you say, can be rather dangerous. Note though that these days this can only be used dangerously if the ONLY_FULL_GROUP_BY option is on, and it is off by default. Allowing it in safe circumstances actual makes modern mySql more standards compliant in this respect, see https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.ht... for more detail.
- GolDDranks 3y agoSometimes I'd like to pick a non-aggregated, non-grouped column that I know will be the same for the whole group. (I often write SQL in GCP's BigQuery that forbids indeterminate results in group bys) I'd like to have an aggregate function something like assert_same(), that picks the value, and errors when some of the values in the group are unexpectedly different.
- ComodoHacker 3y agoYou can do that with standard aggregates by comparing min() and max().
- masklinn 3y agoIn postgres you can array_agg(distinct) and error on the application side if there is more than one value.
- nerdponx 3y agoOr sometimes you can do something like FIRST() OVER (ORDER BY NULL).
- GolDDranks 3y agoI don't think that errors if the group contains different values...?
- nerdponx 3y agoSnowflake has ANY_VALUE() for this: https://docs.snowflake.com/en/sql-reference/functions/any_value https://docs.snowflake.com/en/sql-reference/functions/any_va...