Re: Bug or stupidity - Mailing list pgsql-general

Martijn,
I realize that the change I'm proposing might be too complex to be added
in the upcoming 8.0 release. I do find this discussion interesting
though, so please bear with me while I try to tie up some loose ends.

>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.
>
Ok, bad choice of words. It's not guessed, and I agree, this is
perfectly reasonable.

>Anyway, I think there's a confusion in the phrase "from clause".
>
There's no confusion. I fully understand the differences. That's why
think that the term 'add_missing_from' is misleading. From a strict
syntax point of view it implies expansion to the statement we both
agreed should be disallowed. The fact that it doesn't actually add a
missing from but rather expands the scope for the predicate is somewhat
confusing. Hence my suggestion that the variable is renamed.

>But I guess it comes down to to how strictly you want to follow the SQL
>standard.
>
>
I think it's OK to deviate from the standard and add features. My whole
argument in this thread is based on the fact that PostgreSQL adds tables
to the FROM clause of a SELECT which may produce incorrect results and
that this "magic" is performed by default.

>>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.
>
>
What I was trying to say is that: a) since the 'add_missing_from'
affects the predicate scope for DELETE's, UPDATE's, and SELECT's, and
since those statements have different ways of expressing this scope, the
current choice of name is a bit confusing and b) it would be nice if the
variable affected DELETE and UPDATE scopes only. Now you point out that
an UPDATE can have a FROM clause, so let me revise my suggestion and
instead say:

1. Let's add a variable named "autoscope_for_delete" that is enabled by
default and only affects the scope of a DELETE predicate. We do this to
maintain backward compatibility.
2. Let's change so that "add_missing_from" is disabled by default and
doesn't affect the DELETE statement at all.
3. The "autoscope_for_delete" will use generate notices and
"add_missing_from" will generate warnings.

>>DELETE FROM first_table x
>>  WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo > 4)
>>
>>
>The SQL standard (what I can find on the web anyway) doesn't allow an
>alias there, and neither does PostgreSQL.
>
The SQL 2003 draft I have states:

<delete statement: searched> ::=
    DELETE FROM <target table> [ [ AS ] <correlation name> ]
        [ WHERE <search condition> ]

whereas SQL 3 is more elaborated:

<table reference> ::=
      <table name> [ [ AS ] <correlation name>
          [ <left paren> <derived column list> <right paren> ] ]
    | <derived table> [ AS ] <correlation name>
          [ <left paren> <derived column list> <right paren> ]
    | <joined table>

<delete statement: searched> ::=
    DELETE FROM <table reference>
      [ WHERE <search condition> ]

Perhaps PostgreSQL should adopt this?

>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?
>
>
I'm happy as long as the 'add_missing_from' is disabled or changed so
that it doesn't affect SELECT. And yes, this extension looks good.
Perhaps consider changing the second FROM to USING (mimicking MySQL
instead of MS SQL server). I think it would lessen the risk of
introducing ambiguities in the parser (and it looks better than repeated
FROM's).

Regards,
Thomas Hallgren