On 2020/01/14 7:10 PM, David Raymond wrote:
A note and a question on subqueries. On reading:

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

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.
"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.
"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.


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.



sqlite-users mailing list

Reply via email to