Re: [sqlite] correlated subquery in LIMIT/OFFSET?
On Thu, 11 Apr 2013 17:14:43 +0200 Clemens Ladisch wrote: > >> It should come (or be derived) from the current row in the outer > >> query. > > > > Sorry, but inner queries are performed first. > > Sorry, but *correlated* inner queries are performed once for each > record in the outer query. *Logically* performed, you mean. It's helpful to think of correlated subqueries as being performed once per row. It's also helpful to remember there is no "before" or "after" while a query is executing. The DBMS is free to execute your query in whatever fashion it chooses. Your query is incorrectly construed, however, select (select 42 limit 1 offset (select t.x)) from t; assumes IIUC an order to t that isn't there, and a count that can't, in general, be known. The only way to compute a median is to sort and count the input first. That can be done by joining the table to itself to get an ordinal, and by testing that product against itself in an EXISTS clause. The other way is to write a UDF, which isn't too hard if you assume all value can be represented as double-precision floating point. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] correlated subquery in LIMIT/OFFSET?
Simon Slavin wrote:
> On 11 Apr 2013, at 2:38pm, Clemens Ladisch wrote:
>>> sqlite> create table t(x);
>>> sqlite> select (select 42 limit 1 offset (select t.x)) from t;
>>> Error: no such column: t.x
>
>> It should come (or be derived) from the current row in the outer query.
>
> Sorry, but inner queries are performed first.
Sorry, but *correlated* inner queries are performed once for each record
in the outer query.
To take your ballsy example: with the following data:
create table balls(
color varchar(10),
size integer
);
insert into balls values('blue', 1);
insert into balls values('blue', 2);
insert into balls values('red', 3);
insert into balls values('red', 44);
insert into balls values('red', 555);
this query works just fine:
select color,
(select avg(size) from balls where color = b.color)
from (select distinct color from balls) b;
(This could be done much easier with GROUP BY; I'm just demonstrating
how correlated subqueries work.)
> Your original post said "for computing the median of a group".
With PostgreSQL, this works ():
select color,
(select size
from balls
where color = b.color
order by size
limit 1
offset (select cast(count(*) / 2 as integer)
from balls
where color = b.color)
) as median
from (select distinct color from balls) b;
> The fastest way to do that without creating a SQLite extension ...
Thanks for the workaround, but I'm asking if there is any good reason
why SQLite does not support this. As far as I can tell, (scalar)
correlated subqueries work in every other context.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] correlated subquery in LIMIT/OFFSET?
On 11 Apr 2013, at 2:38pm, Clemens Ladisch wrote: >> sqlite> create table t(x); >> sqlite> select (select 42 limit 1 offset (select t.x)) from t; >> Error: no such column: t.x > It should come (or be derived) from the current row in the outer query. Sorry, but inner queries are performed first. The outer query is analysed for parameters, but rows have not been fetched at the time inner queries are executed. Your original post said "for computing the median of a group". The fastest way to do that without creating a SQLite extension for it is to first count the number of items in the group then do a SELECT. Unforunately it's going to be faster to do this in your programming language than it is to try to make one convoluted SQL statement to do it all. The counting SELECT would be something like SELECT count(*) FROM myTable WHERE theGroup="redballs" Unfortunately this may give an odd or even result, and the OFFSET parameter must evaluate to an integer, so you cant just halve it, you have to do a little maths or a little 'if'. The median entry would then be the expected SELECT ballSize FROM myTable WHERE theGroup="redballs" ORDER BY ballSize OFFSET [value stored earlier] LIMIT 1 A neater solution would be to define your own aggregate function which finds the median of all the values fed to it. Then you could just do SELECT myMedian(ballSize) FROM myTable WHERE theGroup="redballs" Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] correlated subquery in LIMIT/OFFSET?
I think you need a more complete example with the output you expect to get better help. Although I do understand what you're getting at. Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: Thursday, April 11, 2013 8:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] correlated subquery in LIMIT/OFFSET? Michael Black wrote: > > However, it appears that SQLite does not allow correlated subqueries > > in the LIMIT/OFFSET clauses of a scalar subquery: > > > > sqlite> create table t(x); > > sqlite> select (select 42 limit 1 offset (select t.x)) from t; > > Error: no such column: t.x > > Instead of "select t.x" don't you really want "select x from t" ?? No, I want it to be a *correlated* subquery. > I assume you just want the offset to come from a single-row table? It should come (or be derived) from the current row in the outer query. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] correlated subquery in LIMIT/OFFSET?
Michael Black wrote: > > However, it appears that SQLite does not allow correlated subqueries > > in the LIMIT/OFFSET clauses of a scalar subquery: > > > > sqlite> create table t(x); > > sqlite> select (select 42 limit 1 offset (select t.x)) from t; > > Error: no such column: t.x > > Instead of "select t.x" don't you really want "select x from t" ?? No, I want it to be a *correlated* subquery. > I assume you just want the offset to come from a single-row table? It should come (or be derived) from the current row in the outer query. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] correlated subquery in LIMIT/OFFSET?
Instead of "select t.x" don't you really want "select x from t" ?? That seems to work at least syntactically. I assume you just want the offset to come from a single-row table? Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: Thursday, April 11, 2013 8:15 AM To: sqlite-users@sqlite.org Subject: [sqlite] correlated subquery in LIMIT/OFFSET? Hi, for computing the median of a group, it would be useful to be able to use a correlated subquery as the expression in a OFFSET clause. However, it appears that SQLite does not allow correlated subqueries in the LIMIT/OFFSET clauses of a scalar subquery: sqlite> create table t(x); sqlite> select (select 42 limit 1 offset (select t.x)) from t; Error: no such column: t.x Is this behaviour by design? The documentation says that the LIMIT clause of a scalar subquery is ignored and gets replaced with 1, but this shouldn't disallow the syntax above, and should not apply to the OFFSET value in any case. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] correlated subquery in LIMIT/OFFSET?
Hi, for computing the median of a group, it would be useful to be able to use a correlated subquery as the expression in a OFFSET clause. However, it appears that SQLite does not allow correlated subqueries in the LIMIT/OFFSET clauses of a scalar subquery: sqlite> create table t(x); sqlite> select (select 42 limit 1 offset (select t.x)) from t; Error: no such column: t.x Is this behaviour by design? The documentation says that the LIMIT clause of a scalar subquery is ignored and gets replaced with 1, but this shouldn't disallow the syntax above, and should not apply to the OFFSET value in any case. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
