Re: [sqlite] Detecting erroneous multi-row subqueries
On Fri, Nov 30, 2018 at 3:00 AM J. King wrote: > On 2018-11-29 20:56:13, "Richard Hipp" wrote: > >On 11/29/18, J. King wrote: > >> Is it possible to make SQLite fail like PostgreSQL does? > > > >That is possible in theory, but how many of the millions of existing > >applications would that break? > > > > I was wondering more if it's -currently- possible via some switch I'm > not aware of. As an enhancement obviously I wouldn't expect it as a > default (not before SQLite 4, anyway), but as a debugging feature like > reverse_unordered_selects, it might help to uncover millions of bugs. ;) > +1. There are many SQLite gotchas for backward compatibility reasons. Regularly those come back up on this list, because someone's been bitten by it. Gotcha is explained, BC is invoked for not fixing it, pragma is suggested on an opt-in basis to have a "stricter and safer" SQLite, then nothing happens. Some gotchas require file-format changes, so are mostly off-limit completely. Although myself I'd welcome a new and non-BC format, getting rid of all legacy. DRH has expressed several times he's not willing to go there, that's just life :) But when the gotcha is pure runtime, it's less easy to accept the status quo, I have to be honest. Especially since Richard often does not take the time to explain his rational for not doing them. It's probably obvious to him, from cost of implementation, or maintenance/testing (to maintain 100% line/branch coverage), or even from disagreeing with the proposed features for such or such reasons. But it's not always obvious to me for sure, and I confess to difficulty accepting the silence. I realise it's a bit unfair to blame Richard when he gives away SQLite, such a wonderful library, and that he's super busy, creating Fossil, and forums, and SMTP servers, and what-not we are not even aware of, for his commercial clients. Yet I still wish there was a bit more transparency and discussions around SQLite, with the "real" stakeholders of SQLite, i.e. DRH and his small dev team. We do discuss things at length here, but the only authoritative voices are Richard and Dan, no offence to all the other great contributors on this list, and Richard and Dan are often making themselves scarce in these threads. I just wish they got involved more, and shared more insights and rationals. Xmas is around the corner, so it's that time of the year I guess :). I'll shut up now. Sorry for the noise. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting erroneous multi-row subqueries
On 2018-11-29 21:17:59, "Keith Medcalf" wrote: while I cannot comment on the "reverse_unordered_select" pragma, you are getting the result you are because you are using union rather than union all. The actual query didn't use a union and wasn't affected by the pragma either (presumably for some other reason I'd have to puzzle out), so I didn't realize my error. Thanks. Probably not since this would break backwards compatibility. It is however documented: https://sqlite.org/lang_expr.html#subq I've probably read that several times over the years and didn't recall it when it was actually important. Once again, thanks. :) -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting erroneous multi-row subqueries
On Thursday, 29 November, 2018 18:24, J. King wrote: >select (select 'foo' union select 'bar') || 'bar'; >SQLite 3.25.3 returns 'barbar' (regardless of the value of the >reverse_unordered_select pragma) while PostgreSQL 11 refuses to >process the query unless the subquery is reduced to a single row. while I cannot comment on the "reverse_unordered_select" pragma, you are getting the result you are because you are using union rather than union all. select ... UNION select ... returns only distinct rows. Distinctness is generated by passing the results of the two queries into a sorter/temp table such that duplicates get discarded, and then returning the contents of the sorter/temp table. Because 'bar' sorts before 'foo' your select returns 'bar' for the first row and 'foo' for the second row. It will do this irrespective of the order of your two selects being unioned. If you used "union all" as in select 'foo' union all select 'bar' the result returned will be 'foo' since the resulting rows will not be sorted so that only distinct rows are returned and all rows will be returned in the order in which they are generated. Also, when you request a scalar value from a sub-select some databases will return the first row/value retrieved, some will require that the sub-select generate only a single value (and throw an error if that is not the case). SQLite falls into the first category (since you asked for a scalar result you must have meant LIMIT 1, so SQLite helpfully adds that if you forgot), PostgreSQL of the version you are using obviously falls into the latter. Some databases will change what they do from one version to the next or even from query to query depending on how they "feel" at the time. I don't off-hand recall what the standard says (if it says anything at all), nor the behaviour of any particular database. >In my application the actual query was erroneous and would potentially >return data belonging to a user other than the one making the request, which >concerns me quite a bit. Is it possible to make SQLite fail like >PostgreSQL does? Probably not since this would break backwards compatibility. It is however documented: https://sqlite.org/lang_expr.html#subq --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting erroneous multi-row subqueries
On 2018-11-29 20:56:13, "Richard Hipp" wrote: On 11/29/18, J. King wrote: Is it possible to make SQLite fail like PostgreSQL does? That is possible in theory, but how many of the millions of existing applications would that break? I was wondering more if it's -currently- possible via some switch I'm not aware of. As an enhancement obviously I wouldn't expect it as a default (not before SQLite 4, anyway), but as a debugging feature like reverse_unordered_selects, it might help to uncover millions of bugs. ;) -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting erroneous multi-row subqueries
On 11/29/18, J. King wrote: > Is it possible to make SQLite fail like > PostgreSQL does? That is possible in theory, but how many of the millions of existing applications would that break? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
