RE: Recursive SQL

I have not looked closely at Graeme Birchall's DB2 SQL Cookbook, but Joe
Celko has a good section in "SQL for Smarties" on representing trees in
relational databases and traversing those trees using standard SQL.  He also
discusses some of the extensions various vendors have added to make
traversing trees (w/o temporary tables) simpler.

-=michael=-

*****************************************************
*  Michael S. Kelly
*  4800 SW Griffith Dr., Ste. 202
*  Beaverton, OR  97005 USA
*  voice: (503)644-6106 x122  fax: (503)643-8425
*  <michaelk@axian.com>
*  http://www.axian.com/
*****************************************************
*    Axian:  Software Consulting and Training
*****************************************************


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 19, 2000 8:47 PM
To: andy_turk@hotmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Recursive SQL


"Andy Turk" <andy_turk@hotmail.com> writes:
> I was reading Graeme Birchall's SQL Cookbook at
> http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
> and came across an *amazing* technique called recursive SQL.

Interesting, but I think Birchall has confused some very peculiar
(and incorrect) implementation-specific behavior of DB2 with SQL.
This is not SQL.

Leaving aside a minor quibble about whether the WITH syntax he shows
is valid (it's surely not SQL92, although it might be SQL3 if SQL3 ever
becomes a standard), the really fundamental problem is that you cannot
have a SELECT query that inspects its own output.  He claims that in
SELECT foo UNION SELECT bar, the "bar" select will somehow see the
output of the "foo" select --- and not only that, but will be
recursively invoked to see its *own* outputs.  I do not believe that
any such interpretation can be extracted from the SQL standard.
If SQL worked that way, then simple commands likeUPDATE foo SET x = 42 WHERE y = 44
would be infinite loops, because they'd see the new tuples produced
by their own action and try to update those, leading to more new
tuples, etc etc.

He's built a large intellectual edifice on a DB2 bug.
        regards, tom lane