Harald,

> You're talking about "the deletion target table".  Sorry to mention
> the M word again, but MySQL allows deleting from more than one table
> at the same time.  Should we support that?

Nope.   In fact, I'd argue pretty strongly against any move to do so.

MySQL supports multi-table delete for 2 reasons:
1) MySQL does not support CASCADE Foriegn Keys, and as a result this is the 
only way they can "clean out" all related records.
2) To be blunt: MySQL doesn't care about your data.  Just look at their 
implementation of constraints.

Since we do support CASCADE FKs, there is no reason for us to support this 
syntax; just set your FKs up correctly and it's taken care of.   Were we to 
consider implementing this, the implementors would have to answer the 
following questions:

A) In what order are the rows deleted, from which table first?
B) If no join criteria are supplied, is it OK to delete all rows from one of 
the tables?  If not, how do you deliberately clean out a table?
C) If one of the tables had FKs or triggers that affect one of the other 
tables, when do these get evaluated/fired?

Overall, I consider it a very, very bad idea.

-- 
-Josh Berkus
 "A developer of Very Little Brain"
 Aglio Database Solutions
 San Francisco


Tue, 10 Aug 2004 11:56:19 -0700
Josh Berkus <josh@agliodbs.com>
Re: Add Missing From?
In article <200408101156.19796.josh@agliodbs.com>,
Josh Berkus <josh@agliodbs.com> writes:

> Harald,
>> You're talking about "the deletion target table".  Sorry to mention
>> the M word again, but MySQL allows deleting from more than one table
>> at the same time.  Should we support that?

> Nope.   In fact, I'd argue pretty strongly against any move to do so.

> MySQL supports multi-table delete for 2 reasons:
> 1) MySQL does not support CASCADE Foriegn Keys, and as a result this is the
> only way they can "clean out" all related records.

Not quite - MySQL implemented ON DELETE CASCADE for foreign keys
_before_ multi-table DELETEs.

> 2) To be blunt: MySQL doesn't care about your data.

I know - what do you think why I'm lurking here? ;-)

> Since we do support CASCADE FKs, there is no reason for us to support this
> syntax; just set your FKs up correctly and it's taken care of.   Were we to
> consider implementing this, the implementors would have to answer the
> following questions:

> A) In what order are the rows deleted, from which table first?

In exactly the same order as for single-table DELETEs -
implementation-defined.

> 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 :-)

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

> 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.
"tbl" could be names or aliases of tables used in fromexp.  If none
supplied, this would default to all tables used there, thus making
"DELETE FROM t1 WHERE whatever" just a degenerate case.

The semantics of that would be:
1. Do a "SELECT * FROM fromexp"
2. For every tbl, delete everything covered by the result set of the
   SELECT, in some arbitrary order (unless restricted by an ORDER BY)


11 Aug 2004 13:50:14 +0200
Harald Fuchs <hf0722x@protecting.net>
Re: Add Missing From?

On Wed, 11 Aug 2004, Harald Fuchs wrote:

> In article <200408101156.19796.josh@agliodbs.com>,
> Josh Berkus <josh@agliodbs.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.

Wed, 11 Aug 2004 08:06:09 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Add Missing From?
In article <20040811074837.Q17280@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

>> 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. ;)

You're right - what I meant was something like "the same order in
which SELECT without an ORDER BY returns its rows" - something you
can't rely upon.

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

I think automatically updatable views are something like views with
automatically generated INSERT/UPDATE/DELETE rules, aren't they?

Well, my hypothetical multi-table-DELETE would then call the DELETE
rule.  Where do you see a problem?

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

Ouch, didn't think about that.

The general problem seems to be that a table can occur at many places
within one query, and at each place different rows are matched.  On
the top level this should not be a problem: just specify the correct
table alias between DELETE and FROM.

Perhaps we could disallow deleting from tables/aliases in deeper
subselect levels?


11 Aug 2004 17:49:22 +0200
Harald Fuchs <hf0722x@protecting.net>
Re: Add Missing From?
On Wed, 11 Aug 2004, Harald Fuchs wrote:

> In article <20040811074837.Q17280@megazone.bigpanda.com>,
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>
> >> 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. ;)
>
> You're right - what I meant was something like "the same order in
> which SELECT without an ORDER BY returns its rows" - something you
> can't rely upon.

Right, the reason it's important is that there are some things now that
are potentially tied together. If you have table A with rows A1,...,An and
table B with rows B1,...,Bm and the delete join condition gives the two
outputs (A1,B1) and (A2,B1) does a before trigger returning NULL for B1
prevent A1 and A2 from being deleted?  Do row triggers for B1 get run
twice? If the row trigger for B1 is run twice and say returns NULL on the
first but not the second, do A1 and B1 get deleted but not A2?  I'm also
afraid that running the triggers multiple times could actually break
people's current triggers.  Also, if we do allow multiple table at a time
deletes, should we be considering multiple table at a time updates, and if
so, what works for those.

I'm also not sure how rules apply to these multi-table delete statements.

> >> > 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.
>
> I think automatically updatable views are something like views with
> automatically generated INSERT/UPDATE/DELETE rules, aren't they?
>
> Well, my hypothetical multi-table-DELETE would then call the DELETE
> rule.  Where do you see a problem?

It's not a problem, but I'd think that:

delete from foo, bar where foo.val=3;

 should probably act similarly to:
create view v as select * from foo,bar where foo.val=3;
delete from v;

 or probably even:
create view v as select * from foo,bar;
delete from v where val=3;

If that's true, we're not merely defining behavior for the first, but
defining what we expect to make the behavior for the latter two as well so
we should think about that as well.

> >> 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.
>
> Ouch, didn't think about that.
>
> The general problem seems to be that a table can occur at many places
> within one query, and at each place different rows are matched.  On
> the top level this should not be a problem: just specify the correct
> table alias between DELETE and FROM.

That's not quite the issue I was thinking of. I was thinking of cases
where the output rows are not directly/easily connected to base table
rows, like
 delete foo from (select sum(a) from tab group by b having sum(a)>10) foo;

> Perhaps we could disallow deleting from tables/aliases in deeper
> subselect levels?

That's probably a good thing for any first implementation. It'd probably
still be good to think about those cases to not box out possible future
enhancements.

Wed, 11 Aug 2004 11:37:06 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Add Missing From?
In article <20040811110840.D23732@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

> Right, the reason it's important is that there are some things now that
> are potentially tied together. If you have table A with rows A1,...,An and
> table B with rows B1,...,Bm and the delete join condition gives the two
> outputs (A1,B1) and (A2,B1) does a before trigger returning NULL for B1
> prevent A1 and A2 from being deleted?  Do row triggers for B1 get run
> twice?

Me thinks that at the time of deletion the join between A and B should
not matter any more.  The DELETE code would see A1 and A2, and it
would see B1.  Thus a B1 trigger would get called only once.

By the way, this would be an issue also for singe-table DELETEs.

> Also, if we do allow multiple table at a time
> deletes, should we be considering multiple table at a time updates, and if
> so, what works for those.

IMHO multi-table UPDATEs would be much harder because in this case the
join conditions would matter at update time: which row in A would get
updated with values from which row in B?

> I'm also not sure how rules apply to these multi-table delete statements.

See above.  If we break the join relationships before deletion we can
issue DELETE requests "logically sequentially", and these requests
would do the right thing: either fire a trigger or get changed by a
rule.

>> Well, my hypothetical multi-table-DELETE would then call the DELETE
>> rule.  Where do you see a problem?

> It's not a problem, but I'd think that:

> delete from foo, bar where foo.val=3;

>  should probably act similarly to:
> create view v as select * from foo,bar where foo.val=3;
> delete from v;

>  or probably even:
> create view v as select * from foo,bar;
> delete from v where val=3;

> If that's true, we're not merely defining behavior for the first, but
> defining what we expect to make the behavior for the latter two as well so
> we should think about that as well.

That's right, but I think it would be well-defined if we say "build
the result set and then for each table seperately delete all distinct
rows covered by the result set".  In either case we would end up with
deleteing some rows in foo and all rows in bar.

>> The general problem seems to be that a table can occur at many places
>> within one query, and at each place different rows are matched.  On
>> the top level this should not be a problem: just specify the correct
>> table alias between DELETE and FROM.

> That's not quite the issue I was thinking of. I was thinking of cases
> where the output rows are not directly/easily connected to base table
> rows, like
>  delete foo from (select sum(a) from tab group by b having sum(a)>10) foo;

>> Perhaps we could disallow deleting from tables/aliases in deeper
>> subselect levels?

> That's probably a good thing for any first implementation. It'd probably
> still be good to think about those cases to not box out possible future
> enhancements.

As you correctly pointed out above, this is a similar problem to
updatable views.  Does anyone know what the SQL standard says about
those beasts?


12 Aug 2004 11:30:13 +0200
Harald Fuchs <hf0722x@protecting.net>
Re: Add Missing From?