Databases
StackOverflow2013
Postsdbo
POI got stuck on cycles to get the path of a sql directed graph
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POI got stuck on cycles to get the path of a sql directed graph
    primarykey
    data
    text
    <p>I've got a table where I keep couples of numbers, to indicate arcs of a directed Graph, where every node is identified by an integer value:</p> <pre><code>CREATE TABLE graph ( n2 INTEGER NOT NULL, n1 INTEGER NOT NULL, PRIMARY KEY (id_area_possesso, id_area_utente) CONSTRAINT CK CHECK (n1 &lt;&gt; n2) ) </code></pre> <p>Where n1 points n2, and so on; so, for instance, when I will insert</p> <pre><code>INSERT INTO graph VALUES (3,4) INSERT INTO graph VALUES (9,3) INSERT INTO graph VALUES (12,9) </code></pre> <p>I will obtain this graph: 4->3->9->12.</p> <p>I use this query to get the list of the arcs (the path) starting from a node: </p> <pre><code>WITH tmp (n2,n1) AS ( SELECT G.n2 , G.n1 FROM Graph AS G WHERE n1=3 UNION ALL SELECT n2 , n1 FROM Graph AS G JOIN tmp ON (G.n1=tmp.N2) ) SELECT * FROM tmp GO </code></pre> <p>As result of this query I would obtain the arcs: </p> <ul> <li>(9,3) </li> <li>(12,9)</li> </ul> <p>This query works fine, but when there are cycles on the graph:</p> <pre><code>INSERT INTO graph VALUES (0,1) INSERT INTO graph VALUES (2,0) INSERT INTO graph VALUES (1,2) </code></pre> <p>it goes on an infinite loop, and I get the error message:</p> <blockquote> <p>The statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p> </blockquote> <p>I can't use or create other tables in my project, so I will have to do everything on temporary ones. How can I fix my query in order to get the right path, avoiding to get stuck in cycles?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDharma Dude
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POI got stuck on cycles to get the path of a sql directed graph
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload