Re: Add Missing From?

On Wed, 11 Aug 2004, Harald Fuchs wrote:

> In article <200408101156(dot)19796(dot)josh(at)agliodbs(dot)com>,
> Josh Berkus <josh(at)agliodbs(dot)com> writes:

> > A) In what order are the rows deleted, from which table first?
>
> In exactly the same order as for single-table DELETEs -
> implementation-defined.

I think you probably meant in an unspecified order.
Implementation-defined really doesn't mean anything when you're trying to
define what it means for a particular implementation. ;)

Unless it's unspecified, changing the order later may be hard because
people will be using it, and changes will almost certainly break things
for some of those people. IMHO, this means we should choose a behavior
even if it's explicitly unspecified, rather than just letting it go.

> > B) If no join criteria are supplied, is it OK to delete all rows from one of
> > the tables?
>
> Yes - people creating Cartesian products deserve punishment :-)

What we do here should be related to what we would want to happen on
a view with a join if we were to make automatically updatable views.

> > C) If one of the tables had FKs or triggers that affect one of the other
> > tables, when do these get evaluated/fired?
>
> Implementation-defined.

See A.

> > Overall, I consider it a very, very bad idea.
>
> My main concern was not multi-table DELETEs per se, but a way to do
> deletions based on results of arbitrary queries. Multi-table DELETEs
> would just be a logical extension to that.
>
> I thought about something like that:
>
> DELETE [tbl [,tbl]...]
> FROM fromexp
>
> "fromexp" could be anything which is legal after a "SELECT ... FROM",
> including outer joins and LIMIT clauses.

Including subselects in FROM? That's a big barrel of fish. How does this
interact with subselects with union or distinct or group by.