Re: [sqlite] [EXTERNAL] Window functions

2018-04-26 Thread Wout Mertens 
I implemented cursors by simply sending an encoded set of row values that
indicate the absolute sorting position of your current query. Given that
set of values, you can continue the query from that position, even if
values were added or removed before the position.

See
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg109379.html

Note that if you want small cursor values, you could also insert the cursor
values in a server-side table and send the id only.

On Thu, Apr 26, 2018 at 11:52 AM Hick Gunter <h...@scigames.at> wrote:

> Of course you can trade memory space for code complexity/size/speed (see
> my reference to "current position" being re-settable). Neither is available
> on embedded systems. Which would make the code another #define to be tested
> (correct results with and without the optional code), and supported from
> then on to eternity.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Shevek
> Gesendet: Donnerstag, 26. April 2018 10:36
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: Re: [sqlite] [EXTERNAL] Window functions
>
> About storing the whole result set: Note that in postgresql-derivatives
> (and Oracle? maybe Teradata?), this is valid:
>
> select lag(a0, a0) over () from a;
>
> whereas many other servers (which I won't name) require the second
> argument of lag() to be constant. If it is constant (even in
> postgresql-derivatives), a better window bound than the implicit "unbounded
> preceding" can be computed by the optimizer, and this may be essential for
> an efficient implementation of the most common case, because then the
> window buffer (per-group) can be guaranteed to fit into
> (pre-allocated) RAM.
>
> S.
>
> On 04/26/2018 07:44 AM, Hick Gunter wrote:
> > "Window functions" aka "scrollable cursors" require that the whole
> result set ist stored somewhere. Or at least the current position of all of
> the tables/indices involved in producing it, provided that such positions
> are settable. You then need to keep track of how many records need to be
> produced to satisfy the window position.
> >
> > If you really need scrollable cursors, you are free to implement them,
> > maybe by
> >
> > CREATE TEMP TABLE query_results AS SELECT...
> >
> > Followed by
> >
> > SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end;
> >
> > And cleaning up with
> >
> > DROP TABLE query_results;
> >
> > Note that you will be producing the complete result set before you are
> able to return even only the first row. This defeats any gain from having
> the query return results in the desired order without requiring a sort,
> i.e. choosing the indices that makes the desired order coincide with the
> natural order.
> >
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> > Charles Leifer
> > Gesendet: Mittwoch, 25. April 2018 21:05
> > An: General Discussion of SQLite Database
> > <sqlite-users@mailinglists.sqlite.org>
> > Betreff: [EXTERNAL] [sqlite] Window functions
> >
> > Hi,
> >
> > I'm sure this has been asked before, but are window functions on the
> roadmap? Is it the authors' experience that the implementation would
> significantly complicate sqlite? Just curious. Thanks so much for a
> fantastic library.
> >
> > Charlie
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> >   Gunter Hick | Software Engineer |
> <https://maps.google.com/?q=are+Engineer+%7C=gmail=g>
> Scientific Games International
> > GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> > 0430013 | (O) +43 1 80100 <+43%201%2080100> - 0
> >
> > May be privileged. May be confidential. Please delete if not the
> addressee.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _

Re: [sqlite] [EXTERNAL] Window functions

2018-04-26 Thread Hick Gunter 
Of course you can trade memory space for code complexity/size/speed (see my 
reference to "current position" being re-settable). Neither is available on 
embedded systems. Which would make the code another #define to be tested 
(correct results with and without the optional code), and supported from then 
on to eternity.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Shevek
Gesendet: Donnerstag, 26. April 2018 10:36
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] [EXTERNAL] Window functions

About storing the whole result set: Note that in postgresql-derivatives (and 
Oracle? maybe Teradata?), this is valid:

select lag(a0, a0) over () from a;

whereas many other servers (which I won't name) require the second argument of 
lag() to be constant. If it is constant (even in postgresql-derivatives), a 
better window bound than the implicit "unbounded preceding" can be computed by 
the optimizer, and this may be essential for an efficient implementation of the 
most common case, because then the window buffer (per-group) can be guaranteed 
to fit into
(pre-allocated) RAM.

S.

On 04/26/2018 07:44 AM, Hick Gunter wrote:
> "Window functions" aka "scrollable cursors" require that the whole result set 
> ist stored somewhere. Or at least the current position of all of the 
> tables/indices involved in producing it, provided that such positions are 
> settable. You then need to keep track of how many records need to be produced 
> to satisfy the window position.
>
> If you really need scrollable cursors, you are free to implement them,
> maybe by
>
> CREATE TEMP TABLE query_results AS SELECT...
>
> Followed by
>
> SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end;
>
> And cleaning up with
>
> DROP TABLE query_results;
>
> Note that you will be producing the complete result set before you are able 
> to return even only the first row. This defeats any gain from having the 
> query return results in the desired order without requiring a sort, i.e. 
> choosing the indices that makes the desired order coincide with the natural 
> order.
>
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Charles Leifer
> Gesendet: Mittwoch, 25. April 2018 21:05
> An: General Discussion of SQLite Database
> <sqlite-users@mailinglists.sqlite.org>
> Betreff: [EXTERNAL] [sqlite] Window functions
>
> Hi,
>
> I'm sure this has been asked before, but are window functions on the roadmap? 
> Is it the authors' experience that the implementation would significantly 
> complicate sqlite? Just curious. Thanks so much for a fantastic library.
>
> Charlie
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Window functions

2018-04-26 Thread Shevek 
About storing the whole result set: Note that in postgresql-derivatives 
(and Oracle? maybe Teradata?), this is valid:


select lag(a0, a0) over () from a;

whereas many other servers (which I won't name) require the second 
argument of lag() to be constant. If it is constant (even in 
postgresql-derivatives), a better window bound than the implicit 
"unbounded preceding" can be computed by the optimizer, and this may be 
essential for an efficient implementation of the most common case, 
because then the window buffer (per-group) can be guaranteed to fit into 
(pre-allocated) RAM.


S.

On 04/26/2018 07:44 AM, Hick Gunter wrote:

"Window functions" aka "scrollable cursors" require that the whole result set 
ist stored somewhere. Or at least the current position of all of the tables/indices involved in 
producing it, provided that such positions are settable. You then need to keep track of how many 
records need to be produced to satisfy the window position.

If you really need scrollable cursors, you are free to implement them, maybe by

CREATE TEMP TABLE query_results AS SELECT...

Followed by

SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end;

And cleaning up with

DROP TABLE query_results;

Note that you will be producing the complete result set before you are able to 
return even only the first row. This defeats any gain from having the query 
return results in the desired order without requiring a sort, i.e. choosing the 
indices that makes the desired order coincide with the natural order.



-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Charles Leifer
Gesendet: Mittwoch, 25. April 2018 21:05
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Window functions

Hi,

I'm sure this has been asked before, but are window functions on the roadmap? 
Is it the authors' experience that the implementation would significantly 
complicate sqlite? Just curious. Thanks so much for a fantastic library.

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Window functions

2018-04-26 Thread Hick Gunter 
"Window functions" aka "scrollable cursors" require that the whole result set 
ist stored somewhere. Or at least the current position of all of the 
tables/indices involved in producing it, provided that such positions are 
settable. You then need to keep track of how many records need to be produced 
to satisfy the window position.

If you really need scrollable cursors, you are free to implement them, maybe by

CREATE TEMP TABLE query_results AS SELECT...

Followed by

SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end;

And cleaning up with

DROP TABLE query_results;

Note that you will be producing the complete result set before you are able to 
return even only the first row. This defeats any gain from having the query 
return results in the desired order without requiring a sort, i.e. choosing the 
indices that makes the desired order coincide with the natural order.



-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Charles Leifer
Gesendet: Mittwoch, 25. April 2018 21:05
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Window functions

Hi,

I'm sure this has been asked before, but are window functions on the roadmap? 
Is it the authors' experience that the implementation would significantly 
complicate sqlite? Just curious. Thanks so much for a fantastic library.

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users