On Thursday, 29 November, 2018 18:24, J. King <jk...@jkingweb.ca> 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
