Martijn,
> Do you have a better
> suggestion, other than forbidding the currently allowed syntax?
>
Yes I do.
We agree that my second example should be disallowed since the semantics
of the FROM clause is different for a DELETE so the "add_missing_from"
is actually not adding to a FROM clause, it is guessing the scope for
the predicate. I assume the same is true for an UPDATE where there is no
FROM at all.
My suggestion is that we rename the add_missing_from to:
update_delete_autoscope
and that this option has no effect on SELECT clauses. It would be more
or less harmless to have it enabled by default.
Perhaps the add_missing_from should remain but then only affect the
SELECT and as disabled by default.
> Anyway, I think the reasoning so far is, the default stays as it is
> until someone comes up with a non-confusing way of adding a real FROM
> clause to DELETEs.
>
SQL already defines a stright forward way to do that. Consider the
following PostgreSQL syntax:
DELETE FROM first_table
WHERE first_table.id = second_table.xid AND second_table.foo > 4
in standard SQL this would be:
DELETE FROM first_table x
WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo > 4)
The number of characters is almost the same in both statements even for
a very simple WHERE clause thanks to aliasing. The benefits of aliasing
increases as the WHERE clause gets more complicated.
For composite keys or other non key based relationships, the EXISTS
clause can be used.
Why confuse people with yet another syntax?
Regards,
Thomas Hallgren
