6 ms·

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes,

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: it returns the manifest and versionId of the entry that has the maximum checkinTime.)"

mwexler 3y ago
It's not really what one would expect in SQL, but SQLite often defies expectation. In this case, handy, but non-standard.
mrighele 3y ago
> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from version order by checkinTime desc limit 1 or something like that. This should work in SQlite and Postgresql at the minimum. I think to remember that in Oracle you have to use "where rownum=1" so indeed you have to use a nested query. I don't know about other databases.
- globular-toast 3y ago
  The interesting thing is if you want more than one record, like you want the latest version number for each document ID. In SQLite you could do: `SELECT documentId, versionId, max(checkInTime) FROM version GROUP BY documentId`. In Postgres you can do `SELECT DISTINCT ON (documentId) documentId, versionId, checkInTime FROM version ORDER BY versionId, checkInTime DESC`. See: https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...
- paulddraper 3y ago
  MySQL allows the query, but the non aggregate fields are selected randomly
  - wongarsu 3y ago
    Following MySQL's longstanding tradition of just doing whatever instead of showing an error message, no matter how unreasonable the result.
    - paulddraper 3y ago
      MySQL + PHP, name a more iconic match
      - deleted 3y ago
        [deleted]
  - sgarland 3y ago
    It only allows that if you’ve set it to do so. The default SQL_MODE variable includes ONLY_FULL_GROUP_BY. However, in their brilliance, AWS RDS defaults to only NO_ENGINE_SUBSTITUTION for SQL_MODE, thus merrily allowing partial aggregates with non-deterministic results. Wheee! https://github.com/awsdocs/amazon-rds-user-guide/issues/160 https://github.com/awsdocs/amazon-rds-user-guide/issues/160
    - paulddraper 3y ago
      Prior to 5.7, MySQL always accepted non-aggregated fields. Version 5.7 introduced ONLY_FULL_GROUP_BY, but since that change broke lots of code that depended on this historical behavior, many people disabled it.
  - asddubs 3y ago
    randomly, but after filtering by the criteria in the WHERE part of the query. This can actually be useful sometimes if all non-aggregate fields contain the same value (though I wouldn't actually rely on it, since whether this is allowed depends on how the database is configured, and it makes it easy to introduce errors by changing the query)
- tannhaeuser 3y ago
  I agree, that doesn't make sense to me either. What about select versionId, max(checkinTime), min(checkinTime)? Can as well query SqlGPT. And above all, it's not what the SQL standard says when that's the entire point of using a standard in the first place.
  - OskarS 3y ago
    Well, it doesn't error out! In this example, it seems like it picks the result from whatever matches the last column, but not sure if this is determinstic: sqlite> create table x(c1, c2); sqlite> insert into x values ("a", 1); sqlite> insert into x values ("b", 2); sqlite> insert into x values ("c", 3); sqlite> select c1, max(c2) from x; c|3 sqlite> select c1, max(c2), min(c2) from x; a|3|1 sqlite> select c1, min(c2), max(c2) from x; c|1|3 (note: since SQLite is dynamically typed, no need to specify column types for simple examples like this).
- deathanatos 3y ago
  > or something like that That query isn't guaranteed to produce a well-defined result in most SQL engines. (For pretty much the same reason the original doesn't/can't/shouldn't…) In the simple case of two rows with the same `checkinTime`, many engines permit the results to be ordered arbitrarily.
globular-toast 3y ago
I think this is a convenient side-effect of the implementation which was later turned into official behaviour. A bit like Python dictionary key ordering. In Postgres you can do similar things with a DISTINCT ON query. I always found this one of the hardest simple things to do in SQL.
- ealexhudson 3y ago
  I think if you have a set that you want the latest value from, in all engines you can do something explicit like: > SELECT manifest, versionId, checkinTime FROM version ORDER BY checkinTime DESC LIMIT 1 The problem with putting aggregation functions in the select output is that you're being unclear about what is aggregating; that pattern begins to break down once you have e.g. multiple documents in the same schema. Or if versionId somehow wasn't linear (e.g. branches of changes).
cryptonector 3y ago
That's basically short-hand for SELECT manifest, versionId, max(checkinTime) FROM version GROUP BY manifest, versionId ORDER BY 3 DESC LIMIT 1; or WITH m AS (SELECT max(checkinTime) AS checkinTime FROM version) SELECT v.manifest, v.versionId, v.checkinTime FROM version v JOIN m m USING (checkinTime) LIMIT 1; It's a bit of a footgun though because there is some randomness here if multiple rows have the same max checkinTime, so I try not to use this SQLite3-ism. You want to also do something to deterministically pick a "best" row, but for that you need to do something like the above.
kevincox 3y ago
The docs don't make it clear that this works as stated. The first docs I found don't say that they come from the matching row: From https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows https://www.sqlite.org/lang_select.html#generation_of_the_se... > Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Does `max` somehow only affect the selected rows? Or is this relying on a side affect of the query planner sorting the table to optimize max? However then I found https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query https://www.sqlite.org/lang_select.html#bare_columns_in_an_a... > If there is exactly one min() or max() aggregate in the query, then all bare columns in the result set take values from an input row which also contains the minimum or maximum. In all of the nearby examples contain an explicit "GROUP BY" clause but I don't think that this section says that one is required for this behaviour. So I guess this is the behaviour that is being described. However I found this rule as well: > If the same minimum or maximum value occurs on two or more rows, then bare values might be selected from any of those rows. [...] The choice might be different for different bare columns within the same query. Which is in conflict with the earlier rule which says that the row is consistent. Or is this consistent row rule only provided for the implicit grouping. AKA is a and b guaranteed to be from the same row for the first query but not the second? That would be very surprising, maybe the docs just promise too little? SELECT a, b, MAX(c) FROM t SELECT 1 as group, a, b, MAX(c) FROM t GROUP BY 1 There are also more not-well sepcified results if multiple of MIN or MAX are used or if these functions or customized. So overall it is probably best to avoid this in "production" use. But can be convenient for some quick exportation if you are careful.
deathanatos 3y ago
That's um… quite the aside. How can it possibly claim that to be well-defined, given that `manifest` and `versionId` are not functionally dependent¹ on `max(checkinTime)`? ¹e.g., there could be two rows with the same checkinTime, whose value happens to then be the max such.

mwexler 3y ago

It's not really what one would expect in SQL, but SQLite often defies expectation. In this case, handy, but non-standard.

mrighele 3y ago

> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from version order by checkinTime desc limit 1 or something like that. This should work in SQlite and Postgresql at the minimum. I think to remember that in Oracle you have to use "where rownum=1" so indeed you have to use a nested query. I don't know about other databases.

globular-toast 3y ago
The interesting thing is if you want more than one record, like you want the latest version number for each document ID. In SQLite you could do: `SELECT documentId, versionId, max(checkInTime) FROM version GROUP BY documentId`. In Postgres you can do `SELECT DISTINCT ON (documentId) documentId, versionId, checkInTime FROM version ORDER BY versionId, checkInTime DESC`. See: https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...
paulddraper 3y ago
MySQL allows the query, but the non aggregate fields are selected randomly
- wongarsu 3y ago
  Following MySQL's longstanding tradition of just doing whatever instead of showing an error message, no matter how unreasonable the result.
  - paulddraper 3y ago
    MySQL + PHP, name a more iconic match
    - deleted 3y ago
      [deleted]
- sgarland 3y ago
  It only allows that if you’ve set it to do so. The default SQL_MODE variable includes ONLY_FULL_GROUP_BY. However, in their brilliance, AWS RDS defaults to only NO_ENGINE_SUBSTITUTION for SQL_MODE, thus merrily allowing partial aggregates with non-deterministic results. Wheee! https://github.com/awsdocs/amazon-rds-user-guide/issues/160 https://github.com/awsdocs/amazon-rds-user-guide/issues/160
  - paulddraper 3y ago
    Prior to 5.7, MySQL always accepted non-aggregated fields. Version 5.7 introduced ONLY_FULL_GROUP_BY, but since that change broke lots of code that depended on this historical behavior, many people disabled it.
- asddubs 3y ago
  randomly, but after filtering by the criteria in the WHERE part of the query. This can actually be useful sometimes if all non-aggregate fields contain the same value (though I wouldn't actually rely on it, since whether this is allowed depends on how the database is configured, and it makes it easy to introduce errors by changing the query)
tannhaeuser 3y ago
I agree, that doesn't make sense to me either. What about select versionId, max(checkinTime), min(checkinTime)? Can as well query SqlGPT. And above all, it's not what the SQL standard says when that's the entire point of using a standard in the first place.
- OskarS 3y ago
  Well, it doesn't error out! In this example, it seems like it picks the result from whatever matches the last column, but not sure if this is determinstic: sqlite> create table x(c1, c2); sqlite> insert into x values ("a", 1); sqlite> insert into x values ("b", 2); sqlite> insert into x values ("c", 3); sqlite> select c1, max(c2) from x; c|3 sqlite> select c1, max(c2), min(c2) from x; a|3|1 sqlite> select c1, min(c2), max(c2) from x; c|1|3 (note: since SQLite is dynamically typed, no need to specify column types for simple examples like this).
deathanatos 3y ago
> or something like that That query isn't guaranteed to produce a well-defined result in most SQL engines. (For pretty much the same reason the original doesn't/can't/shouldn't…) In the simple case of two rows with the same `checkinTime`, many engines permit the results to be ordered arbitrarily.

globular-toast 3y ago

I think this is a convenient side-effect of the implementation which was later turned into official behaviour. A bit like Python dictionary key ordering. In Postgres you can do similar things with a DISTINCT ON query. I always found this one of the hardest simple things to do in SQL.

ealexhudson 3y ago
I think if you have a set that you want the latest value from, in all engines you can do something explicit like: > SELECT manifest, versionId, checkinTime FROM version ORDER BY checkinTime DESC LIMIT 1 The problem with putting aggregation functions in the select output is that you're being unclear about what is aggregating; that pattern begins to break down once you have e.g. multiple documents in the same schema. Or if versionId somehow wasn't linear (e.g. branches of changes).

cryptonector 3y ago

That's basically short-hand for SELECT manifest, versionId, max(checkinTime) FROM version GROUP BY manifest, versionId ORDER BY 3 DESC LIMIT 1; or WITH m AS (SELECT max(checkinTime) AS checkinTime FROM version) SELECT v.manifest, v.versionId, v.checkinTime FROM version v JOIN m m USING (checkinTime) LIMIT 1; It's a bit of a footgun though because there is some randomness here if multiple rows have the same max checkinTime, so I try not to use this SQLite3-ism. You want to also do something to deterministically pick a "best" row, but for that you need to do something like the above.

kevincox 3y ago

The docs don't make it clear that this works as stated. The first docs I found don't say that they come from the matching row: From https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows https://www.sqlite.org/lang_select.html#generation_of_the_se... > Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Does `max` somehow only affect the selected rows? Or is this relying on a side affect of the query planner sorting the table to optimize max? However then I found https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query https://www.sqlite.org/lang_select.html#bare_columns_in_an_a... > If there is exactly one min() or max() aggregate in the query, then all bare columns in the result set take values from an input row which also contains the minimum or maximum. In all of the nearby examples contain an explicit "GROUP BY" clause but I don't think that this section says that one is required for this behaviour. So I guess this is the behaviour that is being described. However I found this rule as well: > If the same minimum or maximum value occurs on two or more rows, then bare values might be selected from any of those rows. [...] The choice might be different for different bare columns within the same query. Which is in conflict with the earlier rule which says that the row is consistent. Or is this consistent row rule only provided for the implicit grouping. AKA is a and b guaranteed to be from the same row for the first query but not the second? That would be very surprising, maybe the docs just promise too little? SELECT a, b, MAX(c) FROM t SELECT 1 as group, a, b, MAX(c) FROM t GROUP BY 1 There are also more not-well sepcified results if multiple of MIN or MAX are used or if these functions or customized. So overall it is probably best to avoid this in "production" use. But can be convenient for some quick exportation if you are careful.

deathanatos 3y ago

That's um… quite the aside. How can it possibly claim that to be well-defined, given that `manifest` and `versionId` are not functionally dependent¹ on `max(checkinTime)`? ¹e.g., there could be two rows with the same checkinTime, whose value happens to then be the max such.