Re: Is relational theory irrelevant?

From: Serge Rielau <srielau_at_ca.eye-bee-m.com>
Date: Wed, 19 Nov 2003 13:59:18 -0500
Message-ID: <bpgef8$gp6$1@hanover.torolab.ibm.com>

Mikito Harakiri wrote:

> "Serge Rielau" <srielau_at_ca.eye-bee-m.com> wrote in message
> news:bpfunr$dic$1_at_hanover.torolab.ibm.com...
> 

>>The syntax is as follows:
>>SELECT .. FROM OLD TABLE(DELETE FROM ...)
>>
>>OLD TABLE being the set of deleted rows

> 
> 
> May I ask you how is your single statement it different from the following
> sequence:
> 
> SELECT * FROM T;
> DELETE FROM T;

Depending on your isolation level it will give you different results or deadlocks. Not to speak of the fact that you have to read the data twice which is a performance problem.

>>For UPDATE you have the choice of OLD TABLE and NEW TABLE (being the set
>>of rows as they are updated (after before triggers, etc...).
>>Consequently INSERT supports NEW TABLE only.
>>
>>At this point we limit the usage not to include views.
>>This is not because of a problem in the semantics. It's more a problem
>>of accepting that views can MODIFY SQL DATA by simply selsecting from
>>them. Something the standard (and most users I presume) reserve to
>>procedures.
>>There are some more limits that we imposed since we are ahead of the
>>standard and we need to let it catch up. But those are tactical.

> 
> 
> Indeed, it's interesting to see how much abuse standard can tolerate.

Depends on the eye of the beholder I suppose.

>>So what you can do is to perform a "relational workflow" where
>>intermediate sets are made persistent on disc by side-effect.
>>People like Paul like that kind of stuff for data cleansing :-)
>>Also you need such concepts in OLTP if you use surrogate keys produced
>>by the DBMS (such as sequences).
>>
>>Example:
>>WITH del AS (SELECT * FROM OLD TABLE(DELETE FROM S))
>>SELECT count(*) FROM NEW TABLE(INSERT INTO T SELECT * FROM del);

> 
> 
> And how is it different from:
> 
> INSERT INTO T as
>   SELECT * FROM S;
> SELECT count(*) FROM S;
> DELETE FROM S;

So what happens if after my INSERT another connection deletes/inserts some rows in S?
I will get wrong COUNT(*).
But let me give you another example
(it requires the acceptance of surrogate keys as a fact of life):

CREATE TABLE T(PK, C1 INT, C2 INT);
CREATE TRIGGER BEFORE INSERT ON T REFERENCING NEW ROW AS n FOR EACH ROW SET n.pk = (SELECT COALESCE(MAX(pk), 0) + 1 FROM T);

INSERT INTO T(c1, c2) VALUES (5, 6);
What is your new primary key?
You will never know....
When you get a customer reference number where does it come from?

> I guess "more" orthogonality argument from the flame wars with your > competitor is long forgotten.
To me if I enable existing fucntionality (UPDATE, DELETE, INSERT and NEW TABLE, OLD TABLE) in another existing feature (SELECT FROM) using existing methods (SETS), that is indeed an increase in orthogonality. What is your point?

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Wed Nov 19 2003 - 12:59:18 CST