Re: [sqlite] Recursive query

[R Smith]

I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.

Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back  to the
original problem and failed miserably :-(


Yes, that is the problem with simplifying problems - one is liable to 
simplify-out some essential properties of the problem.



The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.

So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.

Table X
idA_id
=
21  1
23  3


Table A
A_id
==
1
3
...

Table Aelements
object_idindex  X_idB_id
=
10  NULL  5
11  23   NULL
12  NULL  7
30  NULL  2
31  NULL  3
...

Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:

WITH RECURSIVE AE(object_id, X_id, B_id) AS (
 SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
 UNION ALL
 SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;

But I think I'm failing to express the recursion in the above.
It doesn't work.

Basically in above, the resulting flattened Aelements list should be  1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.


Right, this schema was made by a masochist. :)

I see David R. already had a go at a solution which seems perfect and it 
already probably works for you, I'm only going to elaborate a bit more 
for education purposes since you seem interested in learning about CTE 
and recursive queries.


Firstly, the table A is analogous to an Index and completely superfluous 
to our problem, so it can be ignored from the start. Aelements already 
contains the object id's we need, so we do not need table A.

I'm going to refer to Aelements simply as "E" from now on.

So if we start with Table X, the problem states (if my understanding is 
correct) that we need to check for the X.A_id of the given X.id, if 
there are any E.object_id's which has a reiterating reference back to 
table X in the E.X_id column and expand those corresponding X.A_id 
relational entries until we have exhaustively added every implicated 
A_id that is a 1st, 2nd, ... or n-th tier relation to the original X.id.
This list we will then match to the B values later (which is easy and 
not part of this immediate problem, so I remove the B references 
completely from the CTE).


In the first (top) part of any CTE table is the root or origin query - 
which for our case is simply the original referenced X.id, so if we make 
a CTE with only the root part, it would be:

WITH AE(X_id, A_id) AS (
    SELECT X.id, X.A_id FROM X WHERE X.id = ?1
)
SELECT * FROM AE;

Note that we start from X_id and we look to expand for it the A_id 
values, and I know later we will need to refer back to the X_id values, 
so that is why I chose those field names in that order in the CTE.  The 
result of this query should be the single line from the X table that 
gives the correct root id. (Nice thing about CTEs is that you can at any 
point select from them, like we do here, to see what the results are so 
far).


Now we are ready to add the recursive part - We need for every X_id 
added to that CTE table, to also add any related A_id values which it 
points to AND which has a corresponding X_id in the E table that refers 
back to another X.id in the X table (i.e where the E.X_id column is not 
null).
The recursive query part is simply the query that follows the root via a 
UNION *and* that refers back to the containing CTE. So to make AE a 
recursive CTE, we will add our id-expanding query after the UNION and 
refer (JOIN) back to the same AE cte  *as if it always contained the 
full list of X_id references we need* (this is the bit that feels a 
little like magic).


WITH AE(X_id, A_id) AS (
    SELECT X.id, X.A_id, NULL FROM X WHERE X.id = ?1
    UNION ALL
    SELECT E.X_id, X.A_id  FROM Aelements AS E JOIN X ON X.id = E.X_id 
WHERE E.object_id = AE.A_id AND E.X_id IS NOT NULL

)
SELECT * FROM AE;

As we scan the Aelements table for the object_id(s) that matches the 
A_id which our root query produced

[ ...FROM Aelements AS E ... WHERE E.object_id = AE.A_id... ],
any entries found with a valid X_id
[ ... WHERE ... E.X_id IS NOT NULL ]
is then matched back to the X 

Re: [sqlite] Recursive query

[David Raymond]

with recursive foo (X_id) as (
  values (21)
  union
  select Aelements.X_id
  from X inner join Aelements
on X.A_id = Aelements.object_id
  where X_id is not null
)
select Aelements.*
from X inner join Aelements
  on X.A_id = Aelements.object_id
where X.id in foo
  and Aelements.B_id is not null;

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MM
Sent: Friday, June 15, 2018 2:03 PM
To: SQLite mailing list; ryansmit...@gmail.com
Subject: Re: [sqlite] Recursive query

>
> On 2018/06/15 3:19 PM, MM wrote:
> I have table A of rows with column "object_id", and 2 NULL columns A_id
> and B_id.
> Each row of A (among rows with the same object_id) either references
> table A itself or table B, ie columns A_id and B_id are mutually
> exclusive.
> e.g.
> Table A
> object_id A_id B_id
> ...
> 1 NULL  5
> 1 3 NULL
> 1 NULL  7
> 3 NULL  2
> 3 NULL  3
> ...
> Table B
> object_id data
> ...
> 2  15
> 3  16
> 5  17
> 7  18
> ...
> My objective is to collect all unique "data" in all the B rows that
> belong to a given A object_id (like '1') and all its "descendants".
> For e.g.:
> given  object "1", the result of the query would be the list 17, 18.
> 15, 16 in no particular order
> given  object "3", the result of the query would be the list 15 16
> Can this be done with a recursive cet query? If so, how?
> Yes it can be done.
> First you need, for a given object_id in A, all the references from A that
> will eventually point to B (i.e. a non-null B_id). That is, you need all
> the A_id items that point back into A to resolve to the entire list of
> object_id rows in A that refers to B_id (and not A_id).
> Thereafter it's a simple thing of taking the A list with B links, linking
> the B values, and getting the Unique entries.
> Step 1: Expand all the object_id items in A which point to another
> object_id in A: This can be done recursively:
> (Assume the given object ID is set in: ?1)
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT AE.o_id FROM AE;
> This gives the fully expanded list of A_id's that are referenced by either
> the given id (?1) or a linking id in A (A_id).
> Now let's simply join all the A table items that is in the list described
> by AE, then join to that the B items where those links exist, and then get
> the distinct items from those:
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT DISTINCT B.data
>   FROM AE
>   JOIN A ON A.object_id = AE.o_id
>   JOIN B ON B.object_id = A.B_id
> ;
> I don't have a testbed handy, but this should work - if not, please post
> again (perhaps with a little more example data) so we can fix it.
> Cheers!
> Ryan


 Hi Ryan,

I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.

Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back  to the
original problem and failed miserably :-(
The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.

So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.

Table X
idA_id
=
21  1
23  3


Table A
A_id
==
1
3
...

Table Aelements
object_idindex  X_idB_id
=
10  NULL  5
11  23   NULL
12  NULL  7
30  NULL  2
31  NULL  3
...

Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:

WITH RECURSIVE AE(object_id, X_id, B_id) AS (
SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
UNION ALL
SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;

But I thin

Re: [sqlite] Recursive query

[MM]

>
> On 2018/06/15 3:19 PM, MM wrote:
> I have table A of rows with column "object_id", and 2 NULL columns A_id
> and B_id.
> Each row of A (among rows with the same object_id) either references
> table A itself or table B, ie columns A_id and B_id are mutually
> exclusive.
> e.g.
> Table A
> object_id A_id B_id
> ...
> 1 NULL  5
> 1 3 NULL
> 1 NULL  7
> 3 NULL  2
> 3 NULL  3
> ...
> Table B
> object_id data
> ...
> 2  15
> 3  16
> 5  17
> 7  18
> ...
> My objective is to collect all unique "data" in all the B rows that
> belong to a given A object_id (like '1') and all its "descendants".
> For e.g.:
> given  object "1", the result of the query would be the list 17, 18.
> 15, 16 in no particular order
> given  object "3", the result of the query would be the list 15 16
> Can this be done with a recursive cet query? If so, how?
> Yes it can be done.
> First you need, for a given object_id in A, all the references from A that
> will eventually point to B (i.e. a non-null B_id). That is, you need all
> the A_id items that point back into A to resolve to the entire list of
> object_id rows in A that refers to B_id (and not A_id).
> Thereafter it's a simple thing of taking the A list with B links, linking
> the B values, and getting the Unique entries.
> Step 1: Expand all the object_id items in A which point to another
> object_id in A: This can be done recursively:
> (Assume the given object ID is set in: ?1)
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT AE.o_id FROM AE;
> This gives the fully expanded list of A_id's that are referenced by either
> the given id (?1) or a linking id in A (A_id).
> Now let's simply join all the A table items that is in the list described
> by AE, then join to that the B items where those links exist, and then get
> the distinct items from those:
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT DISTINCT B.data
>   FROM AE
>   JOIN A ON A.object_id = AE.o_id
>   JOIN B ON B.object_id = A.B_id
> ;
> I don't have a testbed handy, but this should work - if not, please post
> again (perhaps with a little more example data) so we can fix it.
> Cheers!
> Ryan


 Hi Ryan,

I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.

Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back  to the
original problem and failed miserably :-(
The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.

So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.

Table X
idA_id
=
21  1
23  3


Table A
A_id
==
1
3
...

Table Aelements
object_idindex  X_idB_id
=
10  NULL  5
11  23   NULL
12  NULL  7
30  NULL  2
31  NULL  3
...

Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:

WITH RECURSIVE AE(object_id, X_id, B_id) AS (
SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
UNION ALL
SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;

But I think I'm failing to express the recursion in the above.
It doesn't work.

Basically in above, the resulting flattened Aelements list should be  1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.

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

Re: [sqlite] Recursive query

[R Smith]

On 2018/06/15 3:19 PM, MM wrote:

I have table A of rows with column "object_id", and 2 NULL columns
A_id and B_id.
Each row of A (among rows with the same object_id) either references
table A itself or table B, ie columns A_id and B_id are mutually
exclusive.

e.g.

Table A
object_id A_id B_id
...
1 NULL  5
1 3 NULL
1 NULL  7
3 NULL  2
3 NULL  3
...

Table B
object_id data
...
2  15
3  16
5  17
7  18
...

My objective is to collect all unique "data" in all the B rows that
belong to a given A object_id (like '1') and all its "descendants".

For e.g.:
given  object "1", the result of the query would be the list 17, 18.
15, 16 in no particular order
given  object "3", the result of the query would be the list 15 16

Can this be done with a recursive cet query? If so, how?


Yes it can be done.

First you need, for a given object_id in A, all the references from A 
that will eventually point to B (i.e. a non-null B_id). That is, you 
need all the A_id items that point back into A to resolve to the entire 
list of object_id rows in A that refers to B_id (and not A_id).


Thereafter it's a simple thing of taking the A list with B links, 
linking the B values, and getting the Unique entries.


Step 1: Expand all the object_id items in A which point to another 
object_id in A: This can be done recursively:

(Assume the given object ID is set in: ?1)

WITH AE(o_id,A_id) AS (
    SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
    UNION ALL
    SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
)
SELECT AE.o_id FROM AE;

This gives the fully expanded list of A_id's that are referenced by 
either the given id (?1) or a linking id in A (A_id).


Now let's simply join all the A table items that is in the list 
described by AE, then join to that the B items where those links exist, 
and then get the distinct items from those:


WITH AE(o_id,A_id) AS (
    SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
    UNION ALL
    SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
)
SELECT DISTINCT B.data
  FROM AE
  JOIN A ON A.object_id = AE.o_id
  JOIN B ON B.object_id = A.B_id
;

I don't have a testbed handy, but this should work - if not, please post 
again (perhaps with a little more example data) so we can fix it.


Cheers!
Ryan



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

Re: [sqlite] Recursive query

[David Raymond]

Something like this? Your seed number will go into the values statement at the 
start.

with recursive foo (A_id) as (
  values (1)

  union

  select A.A_id
  from
  foo inner join A
on foo.A_id = A.object_id
  where A.A_id is not null
)
select distinct B.data
from
foo inner join A
  on foo.A_id = A.object_id
inner join B
  on A.B_id = B.object_id;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MM
Sent: Friday, June 15, 2018 9:19 AM
To: SQLite mailing list
Subject: [sqlite] Recursive query

I have table A of rows with column "object_id", and 2 NULL columns
A_id and B_id.
Each row of A (among rows with the same object_id) either references
table A itself or table B, ie columns A_id and B_id are mutually
exclusive.

e.g.

Table A
object_id A_id B_id
...
1 NULL  5
1 3 NULL
1 NULL  7
3 NULL  2
3 NULL  3
...

Table B
object_id data
...
2  15
3  16
5  17
7  18
...

My objective is to collect all unique "data" in all the B rows that
belong to a given A object_id (like '1') and all its "descendants".

For e.g.:
given  object "1", the result of the query would be the list 17, 18.
15, 16 in no particular order
given  object "3", the result of the query would be the list 15 16

Can this be done with a recursive cet query? If so, how?

Rds,
___
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] Recursive query?

[Petite Abeille]

On Jan 12, 2014, at 11:02 AM, Darren Duncan  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

Oh, so, exciting! :)

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

Re: [sqlite] Recursive query?

[Darren Duncan]

On 1/11/2014, 7:33 AM, Petite Abeille wrote:

On Jan 10, 2014, at 4:34 PM, Richard Hipp  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Petite Abeille]

On Jan 10, 2014, at 4:34 PM, Richard Hipp  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Alek Paunov]

On 10.01.2014 17:34, Richard Hipp wrote:

On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Scott Robison]

> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Darren Duncan]

On 1/10/2014, 7:34 AM, Richard Hipp wrote:

On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[big stone]

This is really great news !

Will it be developped also for SQLite 4 ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Richard Hipp]

On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Richard Hipp]

On Wed, Jan 8, 2014 at 2:28 PM, Petite Abeille 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  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  >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
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>



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

Re: [sqlite] Recursive query?

[Petite Abeille]

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  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 
> 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
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Recursive query?

[Richard Hipp]

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 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Recursive Query

[MikeW]

Alexander Yap <[EMAIL PROTECTED]> writes:

> 
> Hi All,
> I just want to know if WITH RECURSIVE query is supported by SQLite.
> 
> Thanks in advance.
> 
> Regards,
> 
> Alex

http://www.sqlite.org/lang_select.html

MikeW

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