Re: Is relational theory irrelevant?

From: Serge Rielau <srielau_at_ca.eye-bee-m.com>
Date: Wed, 19 Nov 2003 18:41:18 -0500
Message-ID: <bpgv01$i6f$1_at_hanover.torolab.ibm.com>

Mikito Harakiri wrote:

> "Serge Rielau" <srielau_at_ca.eye-bee-m.com> wrote in message
> news:bpgef8$gp6$1_at_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.

>
>
> Correction:
>
> SELECT * FROM T FOR UPDATE;
> DELETE FROM T;
> COMMIT;

You still have the double scan over the rows. First you fetch them all. Then you go back to delete them.

>>>>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(*).

>
>
> INSERT INTO T as
> SELECT * FROM S FOR UPDATE;
> SELECT count(*) FROM S;
> DELETE FROM S;
> COMMIT;
There is currently no feature that allows you to collect update locks in   a subquery, but lets presume there is and modify the problem slightly:

INSERT INTO T (SELECT * FROM S FOR UPDATE WHERE c1 > 10); SELECT COUNT(*) FROM S FOR UPDATE WHERE c1 > 10; DELETE FROM S WHERE c1 > 10;
COMMIT; Unless you run the strongest isolation level you still have no guarantee that you don't get extra rows in the COUNT(*) and delete even more rows from S.

The problem here is two fold:
1. The relational model does not deal with concurrency (ACID) 2. The relational model is not concerned with speed of execution. Neither of which are acceptable limitations for customers. If SQL (or any language dealing with dat afor that matter) wants to stay relevant it has to deal with these problems.

>>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?

>
>
> No trigger is necessary:
>
> SELECT COALESCE(MAX(c1), 0) + 1 into :x FROM T;
> insert into T(c1, c2) values (:x, 6);
>
>

>>>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?

>
>
> May I ask you what
>
> SELECT * FROM OLD TABLE(DELETE FROM T)
> union
> SELECT * FROM T

This one is indeed interesting. It can be solved though. It may be prudent that read/write conflicts are solved by the syntactic order.
In our case we chose to define ordering for CTE (WITH clause) only. I.e. you write:
WITH V1 AS (SELECT * FROM OLD TABLE(DELETE FROM T)),       V2 AS (SELECT * FROM T)
SELECT * FROM V1
UNION
SELECT * FROM V2; It is up to the standards commitee to extend this to tables in the FROM clause or other set-operations such as UNION or INTERSECT. I am convinced though that syntactic ordering will solve any conflicts in a straight forward way.
However once SQL gets beyond a certain level of complexity the problems become pathological since customesr prefer to use WITH to keep their thinking straight :-)

>
> SELECT * FROM OLD TABLE(DELETE FROM (
> SELECT * FROM NEW TABLE(DELETE FROM T)
> )

NEW TABLE makes no sense for DELETE.
It doesn't exist for DELETE triggers either which I belive to be very natural.
But let's refine the example:
SELECT * FROM OLD TABLE(DELETE FROM (
   SELECT * FROM NEW TABLE(INSERT INTO T VALUES 5) Is this legal? No, because the target of the delete is not deletable. This is nothing new.
DELETE FROM (SELECT * FROM T1, T2);
Also is not deletable. So some targets are updatable, others are not. Note, btw, that I let the "query as a target" go in principle. Both DB2 as well as Oracle support this syntax today. It follows naturally from the notion of updatable views, which are an encapulation requirement. It is interesting to note that one modifies the "underlying" target rather than the derived table. A trade-off made eons ago to allow views to play their role in encapsulation.
>
> Ah, you don't allow features in such combinations? Since combining
> orthogonal features is never a problem, then what exactly do you mean "we
> increased orthodonality with new syntax"?
I hope I answered that with my comments above. BTW, I pick up some sarcastic undertone in your posts. Should I take that personal or is this just your way ... All I try to do is help develop and ship the best DBMS' I can. Walking a fine line between customer demand, legacy (SQL is about my age) and relational model.
Discussions such as these help me along this path and I learn a lot.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Thu Nov 20 2003 - 00:41:18 CET