473,573 Members | 4,482 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

transitive closure of a graph

Transitive closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate. I was surprised to learn
from Graeme Birchall's DB2 cookbook that there is no really
satisfactory solution to this problem. Graeme suggests building a path
and searching for repeated node entry in the path. Is there a more
satisfactory approach that doesn't require string parsing? I understand
that the problem of finding loops is undecideable in general case, but,
cmon, we are taking about very narrowly scoped transitive closure
problem here.

BTW, TC with goofy oracle syntax is just 3 lines

select connect_by_root (tail), tail
from Edges
connect by nocycle prior head = tail

with cycles being automatically taken care of.

Nov 12 '05 #1
9 2727

Mikito Harakiri wrote:
Transitive closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate.


Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.

Nov 12 '05 #2
Mikito Harakiri wrote:
Mikito Harakiri wrote:
Transitive closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate.

Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.

There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

Serge Rielau wrote:
Mikito Harakiri wrote:
Mikito Harakiri wrote:
Transitive closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate.

Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.

There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).


There is July 2003 article by Torsten Steinbach. I guess your article
would address 10g features, cycle handling first of all. Looking
forward to read it.

Interestingly, that recursive "with" was brought up in past flame wars
"A" vs. "B". (As if technical excellence really matters:-). It's
amaising what difference a tiny standard uncomplience makes -- I mean
"union all" instead of "union". Suddenly, transitive closure for graph
with cycles is not expressible anymore.

Nov 12 '05 #4
Mikito Harakiri wrote:
Transitive closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate. I was surprised to learn
from Graeme Birchall's DB2 cookbook that there is no really
satisfactory solution to this problem. Graeme suggests building a path
and searching for repeated node entry in the path. Is there a more
satisfactory approach that doesn't require string parsing? I understand
that the problem of finding loops is undecideable in general case, but,
cmon, we are taking about very narrowly scoped transitive closure
problem here.

BTW, TC with goofy oracle syntax is just 3 lines

select connect_by_root (tail), tail
from Edges
connect by nocycle prior head = tail

with cycles being automatically taken care of.


I wonder if the non-termination problem is a result of the sql syntax
rather than the basic relationships.

I don't know sql well enough to express an alternative solution but it
seems to me that the basic 'parts explosion' problem, if we ignore part
counts and part levels, doesn't require recursion.

Ie., if the starting table can be viewed as a set of pairs of nodes then
each row represents an arc between adjacent nodes. Say this this
table's columns are named A and B, where A and B stand, respectively,
say for 'part' and 'sub-part'. Ignoring issues of optimization we could
take the product of this table and a copy of itself where A and B have
been renamed to C and D. This table might be theoretically big but it
would be finite. It would contain a row for every possible pair of arcs
in the graph. I would think we need only to select pairs of arcs that
are connected, either because the original table says they are or
because the tail of the LHS arc matches the head of the RHS arc. I'd
think this would give all sub-parts of every part because there could be
no possible arc pair that wasn't in the product.

The simple closure of the original table would be the union of the rows
where B = C and the rows where {B,C} match the original {A,B} rows with
the original column names now being represented by the A and D columns.
That whole mess unioned with the original table would give all the
derived pairs.

That's how I worked some small examples out on paper, even ones that had
loops and multiple graphs. I imagine if filtering was applied as the
product was built, then a large cross-product intermediate result could
be avoided. Also, simple (uncompounded) part counts would be possible,
but not levels as far as I can see. Maybe compound counts and levels
could be produced from the simple closure, after the fact.

cheers,
paul c.

Nov 12 '05 #5
Mikito Harakiri wrote:
Serge Rielau wrote:
Mikito Harakiri wrote:
Mikito Harakiri wrote:
Transitiv e closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate.
Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.


There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).

There is July 2003 article by Torsten Steinbach. I guess your article
would address 10g features, cycle handling first of all. Looking
forward to read it.

Interestingly, that recursive "with" was brought up in past flame wars
"A" vs. "B". (As if technical excellence really matters:-). It's
amaising what difference a tiny standard uncomplience makes -- I mean
"union all" instead of "union". Suddenly, transitive closure for graph
with cycles is not expressible anymore.

Didn't know about this article :-(. Here is my TOC:
LEVEL pseudo column
The CONNECT_BY_ROOT expression
The SYS_CONNECT_BY_ PATH() procedure
ORDER SIBLINGS BY expression
The NOCYCLE keyword
CONNECT_BY_ISCY CLE
CONNECT_BY_ISLE AF

Unless I missed something it's a complete and generic mapping up to
Oracle 10gR2.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6
vc

Serge Rielau wrote:
Mikito Harakiri wrote:
Serge Rielau wrote:
Mikito Harakiri wrote:

Mikito Harakiri wrote:
>Transitiv e closure (TC) of a graph is
>
>with TransClosedEdge s (tail, head) as
>( select tail, head from Edges
> union all
> select e.tail, ee.head from Edges e, TransClosedEdge s ee
> where e.head = ee.tail
>)
>select distinct * from TransClosedEdge s
>
>except that this query might never terminate.
Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.
There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).

There is July 2003 article by Torsten Steinbach. I guess your article
would address 10g features, cycle handling first of all. Looking
forward to read it.

Interestingly, that recursive "with" was brought up in past flame wars
"A" vs. "B". (As if technical excellence really matters:-). It's
amaising what difference a tiny standard uncomplience makes -- I mean
"union all" instead of "union". Suddenly, transitive closure for graph
with cycles is not expressible anymore.

Didn't know about this article :-(. Here is my TOC:
LEVEL pseudo column
The CONNECT_BY_ROOT expression
The SYS_CONNECT_BY_ PATH() procedure
ORDER SIBLINGS BY expression
The NOCYCLE keyword
CONNECT_BY_ISCY CLE
CONNECT_BY_ISLE AF

Unless I missed something it's a complete and generic mapping up to
Oracle 10gR2.


How do you implement depth first traversal without a UDF ?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Nov 12 '05 #7
vc wrote:
Serge Rielau wrote:
Mikito Harakiri wrote:
Serge Rielau wrote:
Mikito Harakiri wrote:
>Mikito Harakiri wrote:
>
>
>
>>Transitiv e closure (TC) of a graph is
>>
>>with TransClosedEdge s (tail, head) as
>>( select tail, head from Edges
>>union all
>>select e.tail, ee.head from Edges e, TransClosedEdge s ee
>>where e.head = ee.tail
>>)
>>select distinct * from TransClosedEdge s
>>
>>except that this query might never terminate.
>
>
>Oops, it's actually quite obvious
>
>with TransClosedEdge s (tail, head) as
>( select tail, head from Edges
> where tail <> head --*******
> union all
> select e.tail, ee.head from Edges e, TransClosedEdge s ee
> where e.head = ee.tail
> and e.tail <> ee.head --*******
>)
>select distinct * from TransClosedEdge s
>
>Graeme handles more general case, hence the complication.
>

There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).
There is July 2003 article by Torsten Steinbach. I guess your article
would address 10g features, cycle handling first of all. Looking
forward to read it.

Interestingl y, that recursive "with" was brought up in past flame wars
"A" vs. "B". (As if technical excellence really matters:-). It's
amaising what difference a tiny standard uncomplience makes -- I mean
"union all" instead of "union". Suddenly, transitive closure for graph
with cycles is not expressible anymore.


Didn't know about this article :-(. Here is my TOC:
LEVEL pseudo column
The CONNECT_BY_ROOT expression
The SYS_CONNECT_BY_ PATH() procedure
ORDER SIBLINGS BY expression
The NOCYCLE keyword
CONNECT_BY_IS CYCLE
CONNECT_BY_IS LEAF

Unless I missed something it's a complete and generic mapping up to
Oracle 10gR2.

How do you implement depth first traversal without a UDF ?

Where did I say I excluded UDFs? I like UDF :-)
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

Serge Rielau wrote:
Mikito Harakiri wrote:
Mikito Harakiri wrote:
Transitive closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate.

Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.

There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).


See your article. Unfortunately, understanding code in C is beyond my
capabilities. What is "binary-encoded path"? Suppose we have

Name Sal
------------ ---
KING 100
--> SMITH 50
-----> JONES 20
-----> JAMES 30

What is the binary-encoded path for JAMES node is?

Nov 12 '05 #9
Mikito Harakiri wrote:
Serge Rielau wrote:
Mikito Harakiri wrote:
Mikito Harakiri wrote:
Transitiv e closure (TC) of a graph is

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
)
select distinct * from TransClosedEdge s

except that this query might never terminate.
Oops, it's actually quite obvious

with TransClosedEdge s (tail, head) as
( select tail, head from Edges
where tail <> head --*******
union all
select e.tail, ee.head from Edges e, TransClosedEdge s ee
where e.head = ee.tail
and e.tail <> ee.head --*******
)
select distinct * from TransClosedEdge s

Graeme handles more general case, hence the complication.


There will be a new developerWorks article on mapping CONnECT BY to DB2
tomorrow (if all goes well).

See your article. Unfortunately, understanding code in C is beyond my
capabilities. What is "binary-encoded path"? Suppose we have

Name Sal
------------ ---
KING 100
--> SMITH 50
-----> JONES 20
-----> JAMES 30

What is the binary-encoded path for JAMES node is?

Teh binary encoded path is a concatenation of INTEGERs.
So in "SOURCE" I number the input with ROW_NUMBER() OVER().
Assuming the rows get numbered 1. KING, 2. SMITH, 3. JONES, 4. James.
With 256 Bytes / 4 Bytes Integers => 64 Levels
So James is be:
0x0000000100000 00200000004
King is:
0x000000010

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2890
by: Lilith | last post by:
Is there a python module somewhere (been searching today, no luck) which has efficiently coded various graph-handling routines, such as finding the shortest path through a graph, or the set of all paths through a graph? I'm not a compsci-educated person, so coding my own would be less parsimonious. Thanks for any suggestions! D
27
2089
by: Ted Lilley | last post by:
What I want to do is pre-load functions with arguments by iterating through a list like so: >>>class myclass: .... pass >>>def func(self, arg): .... print arg >>>mylist = >>>for item in mylist: .... setattr(myclass, item, lamdba self: func(self, item))
3
4153
by: Vincenzino | last post by:
Hi, I have some problem in using SQL3 recursive queries on DB2 database system (8.1 and 8.2 UDB). I need to compute the transitive closure of a (possibly) ciclic graph using SQL3 on DB2. I MUST use pure SQL3 and I cannot use cursors, indexes and the like. The graph is represented by a set of arcs with the table "arc" having two attributes...
7
10367
by: David | last post by:
Hi there, I'm having some trouble identifying transitive dependencies in the student table below StudId Name CourseCode CourseDesc Lecturer Grade Office 'S1234', 'Jack', 'C224', 'Database', 'Codd', 'D', 381 'S1234', 'Jack', 'C225', 'Algorithms', 'Djikstra', 'P', 380 'S2345', 'Jill', 'C224', ...
7
1854
by: Csaba Gabor | last post by:
I feel like it's the twilight zone here as several seemingly trivial questions are bugging me. The first of the following three lines is a syntax error, while the last one is the only one that shows the alert. What is the essential reason? function () { alert('hi mom'); }(); function () { alert('hi dad'); }(8); var x=function () {...
9
1616
by: User1014 | last post by:
I'm a javascript noob and have a question concerning some code I've come across that I'm trying to understand. Here is a snippet of the relevant section: (snip) var closure = this; var xhr = new Ajax.Request(this.Url, { method: 'post',
0
1085
by: Gerard Brunick | last post by:
Consider: ### Function closure example def outer(s): .... def inner(): .... print s .... return inner .... 5
11
3012
by: Huayang Xia | last post by:
What will the following piece of code print? (10 or 15) def testClosure(maxIndex) : def closureTest(): return maxIndex maxIndex += 5 return closureTest()
2
2302
by: Man4ish | last post by:
I have created Graph object without vertex and edge property.It is working fine. #include <boost/config.hpp> #include <iostream> #include <vector> #include <string> #include <boost/graph/adjacency_list.hpp> #include <boost/tuple/tuple.hpp> #include <set> using namespace std;
0
7661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7978
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8167
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7730
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6349
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5550
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3688
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2164
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
987
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.