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?