Detecting erroneous multi-row subqueries

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Detecting erroneous multi-row subqueries

J. King-3
Good people of sqlite-users,
I've been porting an application originally designed for SQLite to
PostgreSQL, and the latter failed on a test case where the former does
not. While I've encountered many more such failures during the process
than I'd like, in this case I think PostgreSQL's behaviour is more
desirable. Here's a minimal query:

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

--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting erroneous multi-row subqueries

Richard Hipp-3
On 11/29/18, J. King <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting erroneous multi-row subqueries

J. King-3

On 2018-11-29 20:56:13, "Richard Hipp" <[hidden email]> wrote:

>On 11/29/18, J. King <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting erroneous multi-row subqueries

Keith Medcalf
In reply to this post by J. King-3

On Thursday, 29 November, 2018 18:24, J. King <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting erroneous multi-row subqueries

J. King-3
On 2018-11-29 21:17:59, "Keith Medcalf" <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting erroneous multi-row subqueries

Dominique Devienne
In reply to this post by J. King-3
On Fri, Nov 30, 2018 at 3:00 AM J. King <[hidden email]> wrote:

> On 2018-11-29 20:56:13, "Richard Hipp" <[hidden email]> wrote:
> >On 11/29/18, J. King <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users