Recursive query?

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

Recursive query?

Petite Abeille-2
Hello,

Couldn’t help but notice a brand new branch in SQLite’s repository, the one labeled "Start a new experimental branch for support of Oracle-style CONNECT BY syntax.”.

http://www.sqlite.org/src/info/4365ddd62d

Two reactions:

(1) Recursive queries! Yes! Hurray! :D

(2) CONNECT BY syntax?!? What the…!!! To paraphrase Ridley Scott: “On the interweb, no one can hear you scream.”

If you are considering a syntax for recursion... please, please, pretty please, use Common Table Expression. Even Oracle does use it now.

"Common table expressions are supported by DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), HyperSQL and H2 (experimental).”.

http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

Let have SQLite added to that list :))





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

Re: Recursive query?

Richard Hipp-3
That branch is a "sponsored" change.  We are in communication with the
sponsor asking if they will consider WITH RECURSIVE syntax.  Their original
request specified Oracle10g-compatible CONNECT-BY syntax.  It depends on
what the sponsor really wants.  (We await their reply.)

The code will only make it into trunk if the sponsor selects WITH RECURSIVE.


On Wed, Jan 8, 2014 at 1:33 PM, Petite Abeille <[hidden email]>wrote:

> Hello,
>
> Couldn’t help but notice a brand new branch in SQLite’s repository, the
> one labeled "Start a new experimental branch for support of Oracle-style
> CONNECT BY syntax.”.
>
> http://www.sqlite.org/src/info/4365ddd62d
>
> Two reactions:
>
> (1) Recursive queries! Yes! Hurray! :D
>
> (2) CONNECT BY syntax?!? What the…!!! To paraphrase Ridley Scott: “On the
> interweb, no one can hear you scream.”
>
> If you are considering a syntax for recursion... please, please, pretty
> please, use Common Table Expression. Even Oracle does use it now.
>
> "Common table expressions are supported by DB2, Firebird, Microsoft SQL
> Server, Oracle (with recursion since 11g release 2), PostgreSQL (since
> 8.4), HyperSQL and H2 (experimental).”.
>
> http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
>
> Let have SQLite added to that list :))
>
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: Recursive query?

Petite Abeille-2
Aha… I see… well… if that mysterious sponsor is familiar with ‘connect by’ they should have no issue with a recursive ‘with’ clause syntax, which is what Oracle provides now.

See "Recursive Subquery Factoring” in Oracle’s very own documentation:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55268
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55227


On Jan 8, 2014, at 8:03 PM, Richard Hipp <[hidden email]> wrote:

> That branch is a "sponsored" change.  We are in communication with the
> sponsor asking if they will consider WITH RECURSIVE syntax.  Their original
> request specified Oracle10g-compatible CONNECT-BY syntax.  It depends on
> what the sponsor really wants.  (We await their reply.)
>
> The code will only make it into trunk if the sponsor selects WITH RECURSIVE.
>
>
> On Wed, Jan 8, 2014 at 1:33 PM, Petite Abeille <[hidden email]>wrote:
>
>> Hello,
>>
>> Couldn’t help but notice a brand new branch in SQLite’s repository, the
>> one labeled "Start a new experimental branch for support of Oracle-style
>> CONNECT BY syntax.”.
>>
>> http://www.sqlite.org/src/info/4365ddd62d
>>
>> Two reactions:
>>
>> (1) Recursive queries! Yes! Hurray! :D
>>
>> (2) CONNECT BY syntax?!? What the…!!! To paraphrase Ridley Scott: “On the
>> interweb, no one can hear you scream.”
>>
>> If you are considering a syntax for recursion... please, please, pretty
>> please, use Common Table Expression. Even Oracle does use it now.
>>
>> "Common table expressions are supported by DB2, Firebird, Microsoft SQL
>> Server, Oracle (with recursion since 11g release 2), PostgreSQL (since
>> 8.4), HyperSQL and H2 (experimental).”.
>>
>> http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
>>
>> Let have SQLite added to that list :))
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: Recursive query?

Richard Hipp-3
On Wed, Jan 8, 2014 at 2:28 PM, Petite Abeille <[hidden email]>wrote:

> Aha… I see… well… if that mysterious sponsor is familiar with ‘connect by’
> they should have no issue with a recursive ‘with’ clause syntax, which is
> what Oracle provides now.
>

The sponsor might have a big pile of preexisting Oracle10g CONNECT BY code
that they need to run.  I feel sure that if they just want "recursive
queries" for use in code that has not yet been written then we can convince
them to go with SQL:1999 WITH RECURSIVE.  But if the enhancement is needed
to support legacy code, they might instead insist on CONNECT BY syntax.  I
still don't know what the situation is.  Hopefully we'll here back soon....




>
> See "Recursive Subquery Factoring” in Oracle’s very own documentation:
>
>
> http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55268
>
> http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55227
>
>
> On Jan 8, 2014, at 8:03 PM, Richard Hipp <[hidden email]> wrote:
>
> > That branch is a "sponsored" change.  We are in communication with the
> > sponsor asking if they will consider WITH RECURSIVE syntax.  Their
> original
> > request specified Oracle10g-compatible CONNECT-BY syntax.  It depends on
> > what the sponsor really wants.  (We await their reply.)
> >
> > The code will only make it into trunk if the sponsor selects WITH
> RECURSIVE.
> >
> >
> > On Wed, Jan 8, 2014 at 1:33 PM, Petite Abeille <[hidden email]
> >wrote:
> >
> >> Hello,
> >>
> >> Couldn’t help but notice a brand new branch in SQLite’s repository, the
> >> one labeled "Start a new experimental branch for support of Oracle-style
> >> CONNECT BY syntax.”.
> >>
> >> http://www.sqlite.org/src/info/4365ddd62d
> >>
> >> Two reactions:
> >>
> >> (1) Recursive queries! Yes! Hurray! :D
> >>
> >> (2) CONNECT BY syntax?!? What the…!!! To paraphrase Ridley Scott: “On
> the
> >> interweb, no one can hear you scream.”
> >>
> >> If you are considering a syntax for recursion... please, please, pretty
> >> please, use Common Table Expression. Even Oracle does use it now.
> >>
> >> "Common table expressions are supported by DB2, Firebird, Microsoft SQL
> >> Server, Oracle (with recursion since 11g release 2), PostgreSQL (since
> >> 8.4), HyperSQL and H2 (experimental).”.
> >>
> >> http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
> >>
> >> Let have SQLite added to that list :))
> >>
> >>
> >>
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > [hidden email]
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: Recursive query?

Richard Hipp-3
On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp <[hidden email]> wrote:

>
>
> The sponsor might have a big pile of preexisting Oracle10g CONNECT BY code
> that they need to run.  I feel sure that if they just want "recursive
> queries" for use in code that has not yet been written then we can convince
> them to go with SQL:1999 WITH RECURSIVE.  But if the enhancement is needed
> to support legacy code, they might instead insist on CONNECT BY syntax.  I
> still don't know what the situation is.  Hopefully we'll here back soon....
>
>
FYI:  The sponsor is now indicating that they want to go with WITH
RECURSIVE.  So the CONNECT BY branch has been closed and we are starting to
work on a WITH RECURSIVE implementation.

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

Re: Recursive query?

big stone
In reply to this post by Petite Abeille-2
This is really great news !

Will it be developped also for SQLite 4 ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Recursive query?

Darren Duncan
In reply to this post by Richard Hipp-3
On 1/10/2014, 7:34 AM, Richard Hipp wrote:

> On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp <[hidden email]> wrote:
>> The sponsor might have a big pile of preexisting Oracle10g CONNECT BY code
>> that they need to run.  I feel sure that if they just want "recursive
>> queries" for use in code that has not yet been written then we can convince
>> them to go with SQL:1999 WITH RECURSIVE.  But if the enhancement is needed
>> to support legacy code, they might instead insist on CONNECT BY syntax.  I
>> still don't know what the situation is.  Hopefully we'll here back soon....
>>
> FYI:  The sponsor is now indicating that they want to go with WITH
> RECURSIVE.  So the CONNECT BY branch has been closed and we are starting to
> work on a WITH RECURSIVE implementation.

That's great news!  This will be a serious step up in feature set for SQLite,
both in ease of use and power, I look forward to it. -- Darren Duncan

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

Re: Recursive query?

Scott Robison-2
> That's great news!  This will be a serious step up in feature set for
SQLite, both in ease of use and power, I look forward to it. -- Darren
Duncan

It will be available in the SQLite Gold edition for $10,000. {jk}
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Recursive query?

decalek
In reply to this post by Richard Hipp-3
On 10.01.2014 17:34, Richard Hipp wrote:
> On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp <[hidden email]> wrote:
>>
> FYI:  The sponsor is now indicating that they want to go with WITH
> RECURSIVE.  So the CONNECT BY branch has been closed and we are starting to
> work on a WITH RECURSIVE implementation.

Thank you, SQLite team! Big thanks to the sponsor too!

Kind Regards,
Alek



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

Re: Recursive query?

Petite Abeille-2
In reply to this post by Richard Hipp-3

On Jan 10, 2014, at 4:34 PM, Richard Hipp <[hidden email]> wrote:

> FYI:  The sponsor is now indicating that they want to go with WITH
> RECURSIVE.  So the CONNECT BY branch has been closed and we are starting to
> work on a WITH RECURSIVE implementation.

Much excellent. And much thanks to such rational sponsor :)

So, considering that ‘with recursive’ is a superset of the plain vanilla ‘with’ clause, does it mean we can expect to see the regular ‘with’ clause in SQLite sometime in the near future as well?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Recursive query?

Darren Duncan
On 1/11/2014, 7:33 AM, Petite Abeille wrote:
> On Jan 10, 2014, at 4:34 PM, Richard Hipp <[hidden email]> wrote:
>
>> FYI:  The sponsor is now indicating that they want to go with WITH
>> RECURSIVE.  So the CONNECT BY branch has been closed and we are starting to
>> work on a WITH RECURSIVE implementation.
>
> Much excellent. And much thanks to such rational sponsor :)
>
> So, considering that ‘with recursive’ is a superset of the plain vanilla ‘with’ clause, does it mean we can expect to see the regular ‘with’ clause in SQLite sometime in the near future as well?

I would expect so; you can't have WITH RECURSIVE without WITH. -- Darren Duncan

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

Re: Recursive query?

Petite Abeille-2

On Jan 12, 2014, at 11:02 AM, Darren Duncan <[hidden email]> wrote:

> I would expect so; you can't have WITH RECURSIVE without WITH.

It’s taking shape:

http://www.sqlite.org/src/timeline?r=common-table-expr&nd

Oh, so, exciting! :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users