Re: Bug or stupidity

On Tue, Oct 26, 2004 at 06:21:23PM +0200, Thomas Hallgren wrote:
> Do you consider this overly complex? Compare:
>
> DELETE FROM x WHERE EXISTS (SELECT * FROM table WHERE x.a = table.a and
> x.b > table.b and table.c = 4)
>
> to:
>
> DELETE FROM x, table WHERE x.a = table.a and x.b > table.b and table.c = 4
>
> In the latter, what is it you are deleting? Is it x or table? I'm not at
> all in favor of listing several tables in the FROM clause of a DELETE
> statement (that includes implicitly adding them).

The problem is that in DELETE, there is no FROM clause in the sense
there is with the other commands, the FROM keyword is used for a
different purpose. The FROM clause the tables are automatically added
to does not have an equivalent in the original SQL statement.

I'm in favour of the status quo, exactly the current default behaviour.
That second example you give is confusing and should be disallowed. But
no-one has come up with anything better. Do you have a better
suggestion, other than forbidding the currently allowed syntax?

> >Every DB interface I've used so far displays the notices
> >where I can see them. This notice is one of the less useful, there
> >are other more useful warnings which are much more handy to see...
> >
> >
> Right. Useful "warnings"! Seems you agree that this should be a warning,
> not a notice.

Hmm, I consider a notice to be a warning anyway, something you should
always read. The default log level is notice anyway, so if you're
seeing warnings, you'll see the notices too...

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. Requiring people upgrading to add missing tables in
the FROM for SELECT and UPDATE is one thing. Asking them to rewrite
every DELETE query as a subselect is a bit too far. It would be nice
also because then you could then also use aliases.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.