Re: Bug or stupidity - Mailing list pgsql-general

On Wed, Oct 27, 2004 at 12:15:10AM +0200, Thomas Hallgren wrote:
> 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.

Not true, UPDATE in PostgreSQL does allow a from clause. Observe:

# \h update
Command:     UPDATE
Description: update rows of a table
Syntax:
UPDATE [ ONLY ] table SET col = expression [, ...]
    [ FROM fromlist ]
    [ WHERE condition ]

Perfectly reasonable addition, but not strictly SQL standard. Also, the
scope is not guessed, it's totally unambiguous. I avoid the issue
entirly by either never using aliases, or always using aliases, hence
the issue doesn't come up, but that's me.

Anyway, I think there's a confusion in the phrase "from clause". From
the server's point of view, it's the list of tables the query is
working with and this applies to all kinds of queries, DELETE, SELECT
and UPDATE alike. Internally all those queries are processed the same,
it's just what happens to the selected rows that changes. SELECT and
UPDATE allow you to explicitly list tables, DELETE doesn't. The bit
after FROM in a DELETE query is *not* the from clause by this
definition.

But I guess it comes down to to how strictly you want to follow the SQL
standard.

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

As pointed out above, it's not needed to update. And add_missing_from
currently has no effect on delete, so your suggested option appears to
be merely the inverse of what is already there.

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

The SQL standard (what I can find on the web anyway) doesn't allow an
alias there, and neither does PostgreSQL. Incidently, MS SQL server
allows the following syntax:

DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON ....

The UPDATE syntax extension I mentioned above is also in MS SQL as far
as I can tell (I've never personally used it). Would adding support for
a from clause there make a difference to you?

Ref: http://www.mvps.org/access/queries/qry0022.htm

> Why confuse people with yet another syntax?

Why confuse people by changing a perfectly usable syntax, that's been
present for years (since the beginning I beleive) and generates NOTICEs
already. The difference between NOTICEs and WARNINGs is that NOTICEs
are expected, a direct consequence of the query, whereas warnings are
unexpected, change each time you run the query. By that definition it
clearly should be a NOTICE.

Anyway, this isn't going anywhere. Neither of us is going to make any
changes to the server. And the core has decided to leave it as is for
the time being. Maybe after 8.0 is released it can be revisited.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.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.