Re: Capturing the changes in columns in a table

A note and a question on subqueries. On reading:

select
...
(select b from t where a == new.a and idate < new.idate order by idate desc) as oldv,
...

My brain started yelling that that needed a "limit 1" on the subquery so that it would only return 1 row.

I looked in the SQLite docs though and it seems like I'm wrong.
https://www.sqlite.org/lang_expr.html
"The value of a subquery expression is the first row of the result from the enclosed SELECT statement."

Then to make sure my brain was remembering correctly I checked Postgres and found this.
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
"It is an error to use a query that returns more than one row or more than one column as a scalar subquery."


So my questions then are:

How is that handled by other databases?

Is that something worthy of being on the quirks page (https://www.sqlite.org/quirks.html), or is it just in the realm of non-quirky normal-level differences?


_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users