Re: Capturing the changes in columns in a table

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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users