agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: andy_turk@hotmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Recursive SQL
Date: Wed, 19 Apr 2000 23:47:08 -0400
Message-ID: <504.956202428@sss.pgh.pa.us> (raw)
In-Reply-To: <20000419162746.84052.qmail@hotmail.com>
References: <20000419162746.84052.qmail@hotmail.com>

"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: <504.956202428@sss.pgh.pa.us>
Permalink:  ../504.956202428@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/504.956202428@sss.pgh.pa.us

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: tgl@sss.pgh.pa.us, andy_turk@hotmail.com
  Subject: Re: Recursive SQL
  In-Reply-To: <504.956202428@sss.pgh.pa.us>

* 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