On 2020/01/14 7:10 PM, David Raymond wrote:
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?
I cannot speak for ALL databases, but mostly PostGres, MySQL and MSSQL
fails if violated only. (I think MSSQL warns, depending on the version,
when you prepare a non-limited sub-query).
Mostly though, they assume the Query programmer knows what (s)he is
doing and will run without problem - that is, until the sub-query
actually hits more than 1 row, at which point the current operation will
fail with an error (something like: "Scalar Sub-Query may only return 1
row").
SQLite seems to regard that if you ask for a scalar sub-query, you
obviously mean to have just one row... so it returns just 1.
Both approaches have merit.
I always lean more towards "It's better to error out", and if it is a
case of you just forgetting to place the LIMIT or TOP clause in your
sub-query, then it's great that it errors out to help you rectify it
NOW, like the client-server clade.
But,
I'm slightly more a fan of "what happens in dev must happen in
production" - and in this case I'm leaning the sqlite way since, with
the other servers, the disadvantage is that your dev server may well
never produce more than one row, so always seem to be working, and then
it fails once in production with more real data.
I'd rather have A - the Query error out on Prepare when it realizes the
LIMIT/TOP clause is missing - or B - auto-recognizes the specific
sub-query type and only returns one row (a-la-sqlite), but I don't think
the "Accept it until the shape of the data makes it fail" is a great
solution.
/2c
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
