Re: [sqlite] equivalent for JOIN LATERAL

[Dominique Devienne]

On Mon, Feb 9, 2015 at 6:12 AM, James K. Lowden 
wrote:

> > If not, would it be possible too much effort ?
>
> I'm guessing the answer is No because the prerequisites are missing.
>

[DD] And that's the crux of it. SQLite has virtual tables, and as such can
approximate table functions, but in a very weird and tricky way. Table
functions are part of my top-5 wished for extension in SQLite.


>
> Something like LATERAL (or APPLY in SQL Server) arises around
> table-valued functions, which really should be called parameterized
> views.  You think you'd like to be able to say,
>
> SELECT S.*
> FROM T join F(T.t) as S on T.t < S.x
>
> where F is some function that produces a table for a scalar/row
> input.
>
> However, perfectly nothing new is really needed to express the idea:
>
> SELECT S.*
> FROM (select F(t) from T) as S
> WHERE EXISTS (select 1 from T where S.x > T.t)
>

Thanks James. That was useful to me. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[Darren Duncan]

I recall that 
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ shows 
how Pg 9.3's LATERAL join is useful in practice, as it lets you do in 
declarational SQL what you may have needed procedural code for before, in which 
case it is an improvement. -- Darren Duncan


On 2015-02-08 9:12 PM, James K. Lowden wrote:

On Sun, 8 Feb 2015 23:52:43 +0100
Big Stone  wrote:


I fall over this presentation of LATERAL, from postgresql guys.

Does it exist in SQLITE ?


Syntactically, no.  Functionally, in part.


If not, would it be possible too much effort ?


I'm guessing the answer is No because the prerequisites are missing.

Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views.  You think you'd like to be able to say,

SELECT S.*
FROM T join F(T.t) as S on T.t < S.x

where F is some function that produces a table for a scalar/row
input.

However, perfectly nothing new is really needed to express the idea:

SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)

I suspect that new syntax like this is usually added to SQL for the
wrong reasons.

1.  Marketing.  Now with LATERAL added!
2.  User-imagined need, because don't know SQL
3.  Punt on query optimization, invent keyword as hint

In each case, they have added complexity without power.  The "improved"
system is harder to use and to develop.  But, hey, it's progress.

?Perfection is achieved not when there is nothing
left to add, but when there is nothing left to take away?
? Antoine de Saint-Exupery

--jkl


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

Re: [sqlite] equivalent for JOIN LATERAL

[Big Stone]

oups ! Thank you Roger, I had forgot to post the link.

I got it via a tweet of Wes McKinney, one of the DataScience leader in the
Python World.

https://twitter.com/wesmckinn/status/564526251591733248
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[James K. Lowden]

On Sun, 8 Feb 2015 23:52:43 +0100
Big Stone  wrote:

> I fall over this presentation of LATERAL, from postgresql guys.
> 
> Does it exist in SQLITE ?

Syntactically, no.  Functionally, in part.  

> If not, would it be possible too much effort ?

I'm guessing the answer is No because the prerequisites are missing.  

Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views.  You think you'd like to be able to say, 

SELECT S.*
FROM T join F(T.t) as S on T.t < S.x

where F is some function that produces a table for a scalar/row
input.  

However, perfectly nothing new is really needed to express the idea: 

SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)

I suspect that new syntax like this is usually added to SQL for the
wrong reasons.  

1.  Marketing.  Now with LATERAL added!
2.  User-imagined need, because don't know SQL
3.  Punt on query optimization, invent keyword as hint

In each case, they have added complexity without power.  The "improved"
system is harder to use and to develop.  But, hey, it's progress.   

?Perfection is achieved not when there is nothing
left to add, but when there is nothing left to take away? 
? Antoine de Saint-Exupery

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

Re: [sqlite] equivalent for JOIN LATERAL

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/08/2015 03:32 PM, Simon Slavin wrote:
> For those like me who hadn't heard of it, here's a reference:

Here is a presentation referenced "Modern SQL in PostgreSQL", with
title "Still using Windows 3.1?  So why stick to SQL-92?"  Lots of
nice exposition and diagrams.  SQLite is also mentioned.

   http://www.slideshare.net/MarkusWinand/modern-sql

Seen on HackerNews:

   https://news.ycombinator.com/item?id=9018129

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTYB58ACgkQmOOfHg372QSipwCfTJa/M8UPBqQ1UwRmoxgTVSwU
wBoAn0I9HpwyQswDjLjCKjoY6IdTh4lu
=EOku
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[Simon Slavin]

On 8 Feb 2015, at 10:52pm, Big Stone  wrote:

> I fall over this presentation of LATERAL, from postgresql guys.
> (look at pages 1 to 16)

For those like me who hadn't heard of it, here's a reference:



and from the documentation, section 7.2.1.5 of



> Does it exist in SQLITE ?
> 
> If not, would it be possible [without] too much effort ?

I'm going to let other people answer the question.

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

Re: [sqlite] equivalent for JOIN LATERAL

[Big Stone]

If not, would it be possible "without" too much effort ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] equivalent for JOIN LATERAL

[Big Stone]

Hello,

I fall over this presentation of LATERAL, from postgresql guys.
(look at pages 1 to 16)

Does it exist in SQLITE ?

If not, would it be possible too much effort ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users