agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Michael S. Kelly <michaelk@axian.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: andy_turk@hotmail.com
Cc: pgsql-sql@postgresql.org
Subject: RE: Recursive SQL
Date: Thu, 20 Apr 2000 16:26:49 -0700
Message-ID: <NEBBKOJMAKEJJCCOJPPPKEOECAAA.michaelk@axian.com> (raw)
In-Reply-To: <504.956202428@sss.pgh.pa.us>
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 like
UPDATE 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
view thread (16+ messages) latest in thread
Message-ID: <NEBBKOJMAKEJJCCOJPPPKEOECAAA.michaelk@axian.com>
Permalink: ../NEBBKOJMAKEJJCCOJPPPKEOECAAA.michaelk@axian.com/
Also on: postgresql.org/message-id/NEBBKOJMAKEJJCCOJPPPKEOECAAA.michaelk@axian.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michaelk@axian.com, tgl@sss.pgh.pa.us, andy_turk@hotmail.com
Subject: RE: Recursive SQL
In-Reply-To: <NEBBKOJMAKEJJCCOJPPPKEOECAAA.michaelk@axian.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox