Re: a union is always a join!

From: Brian Selzer <>
Date: Mon, 23 Mar 2009 01:18:55 -0400
Message-ID: <4ZExl.21879$>

"Tegiri Nenashi" <> wrote in message
> On Mar 22, 4:18 am, "Brian Selzer" <> wrote:
> > I didn't make a typo. I think that your characterization of insertions
> > and
> > deletions is incorrect. There are things in the universe of discourse
> > that
> > can appear different at different times. Twenty years ago I was fifty
> > pounds lighter than I am now, but that doesn't mean that wasn't me. Each
> > thing that persists travels a path through time, interacting along the
> > way
> > with various other things, and thus identity for things that persist
> > must
> > take into account the entirety of those paths, not just what appears in
> > arbitrary or even adjacent snapshots of the universe along the way;
> > therefore what serves to identify something can be different at
> > different
> > times, and similarly, what serves to identify something at one time may
> > serve to identify something else at a different time. Whenever an
> > employee
> > forgets his badge, he is issued one of five temporary badges, so more
> > than
> > one of those temporary badges could serve to identify the same employee
> > at
> > different times, and also each of those temporary badges could serve to
> > identify several different employees at different times. Consequently,
> > one
> > cannot logically conclude that whenever identifiers at different times
> > are
> > identical, the things that they identify are also identical, and
> > therefore
> > the fact that a particular tuple appears in successive states does not
> > imply
> > that the things referenced by the tuple at each state are identical.
> > Clearly deletions and insertions are not just differences between
> > states--at
> > least not algebraic differences. Once one admits that there are things
> > in
> > the universe of discourse that can appear different at different times,
> > the
> > semantics of insert, update and delete become clear: insert describes
> > the
> > beginning of the path that something travels through time, updates
> > describe
> > milestones along the path that mark changes in appearance, and delete
> > describes the end of the path. So a transition consisting of a delete
> > and
> > an insert that has no apparent effect on the database makes perfect
> > sense
> > because it describes the end of one thing and the beginning of another.
> Ok, after tuple has been inserted into the table, did it get recorded
> in the database? If it was, then we have two distinct database states,
> and this insertion is visible to an observer (err, database user). If
> this insertion was not committed, but immediately rolled back, then no
> other transaction can possibly notice this change. For all practical
> purposes the insertion and deletion of the "same thing" is NOOP.

I just want to point out that transactions differ from transitions in that they are an implementation mechanism, along with isolation levels and the notions of being committed or being rolled back. If a database user's transaction is executing at a READ UNCOMMITTED isolation level, then yes, the insertion would be visible, but then, that is a known consequence of the READ UNCOMMITTED isolation level. At the SERIALIZABLE isolation level, all other transactions that might possibly affect the result of a query are blocked until the result is produced, so the insertion wouldn't be visible.

But more importantly, delete refers to tuples that are in the database, not tuples that are being inserted, so a transition that consists of a delete and an insert of a particular tuple is not a NOOP, but rather an assertion that describes the end of the interval during which one thing persisted and the beginning of the interval that a different thing persists. Bottom line: you can't delete what isn't already there, nor can you insert what is already there. Received on Mon Mar 23 2009 - 06:18:55 CET

Original text of this message