Re: insert different from union?

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:JLoRb.29146$zj7.25503_at_newsread1.news.pas.earthlink.net...
> Bob Badour wrote:
[snip]
> > A insert B is equivalent to a series of preconditions of the form:
> > PRE( ( A{candidate key} intersect B{candidate key} ){} = DUM )
> > for each candidate key of A followed by an assignment of the form:
> > A := A union B
> >
> > Thus, insert is different from union and has the added benefit of
treating
> > certain likely errors as errors. I suggest that both insert and
assignment
> > are useful operations, and I would prefer a dbms that provides both.
>
[snip]

> That is, if the behaviour of insert was more like the unconstrained
> union with assignment than the constrained union with assignment that
> Bob cites, then most people would be better off most of the time.
>
> After the operation is complete, it might be helpful to know the
> difference between the cardinality of the result and the sum of the
> cardinalities of the operands (how many duplicates there were).

Returning values from update statements? Some might complain that would be tantamount to allowing update to have side-effects, or that one should not "nest variable operations within value operations". Personally, I think that selecting from assignments is perfectly valid, and is a good way (i.e. a logically correct way) to return such things as the cardinality difference above.

> And as Marshall pointed out, if the key columns (for N >= 1 candidate
> keys) are identical, then the whole tuple must be identical or an
> error will be generated. So, the only time you don't get the same
> behaviour between the constrained and unconstrained versions is when
> you would otherwise be inserting duplicate data. The union operation
> harmlessly eliminates the duplicates, so the result is perfectly OK.
>
> Of course, SQL permits duplicates in general - that may be why insert
> needs to be different there. But in a TRDBMS, is that extra
> qualification (Bob's pre-condition) really helpful? I tend to think not.

I tend to agree. In fact, I think it would be a useful psychological point to not include I/U/D shorthands by default in a TRDBMS. I don't believe the I/U/D shorthands help set-wise, relational thinking. Especially if they include a hidden assignment in their expansion. I/U/D have too much baggage from their use in SQL systems. A TRDBMS should not just be a "refactored" SQL. It arguably needs to make a point of not keeping many visible traces of SQL in it at all.

Regards

Paul Vernon

Business Intelligence, IBM Global Services Received on Tue Jan 27 2004 - 13:48:29 CET