Thread: BUG #7543: Invalid table alias: DELETE FROM table *

The following bug has been logged on the website:

Bug reference:      7543
Logged by:          Barry Brown
Email address:      barrybrown@sierracollege.edu
PostgreSQL version: 9.1.5
Operating system:   Ubuntu 12.04
Description:        =


I sometime see my users delete all rows from a table using a command like
this:

DELETE FROM customer *;

The question is: what is the star? Is it a table alias or an
output_expression?

The grammar for DELETE is:

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

According to the grammar, the star could be an alias. We can alias the table
being affected with a "normal" alias. The following also works:

DELETE FROM customer foo;
DELETE FROM customer foo where foo.id =3D 1;

However, the star isn't treated as an alias in this next command. Instead,
it generates a syntax error, as one might expected:

DELETE FROM customer * where *.id =3D 1;

So perhaps the star is an output_expression. The grammar for the RETURNING
clause is:

RETURNING * | output_expression [ [ AS ] output_name ] [, ...]

Since it has no parentheses to alter the operator precedence, it is
interpreted as:

(RETURNING *) | (output_expression [ [ AS ] output_name ] [, ...])

In other words, one can write just an output_expression without the
RETURNING keyword. However, I'm sure the grammar is supposed to require the
RETURNING keyword. Consequently, the grammar ought to be:

RETURNING (* | output_expression [ [ AS ] output_name ] [, ...])

The written documentation implies that * is a valid output_expression.

In summary:
Should * be an acceptable table alias for the DELETE command?
Should the grammar for the RETURNING clause include some parentheses to make
it clearer what's expected?

barrybrown@sierracollege.edu writes:
> I sometime see my users delete all rows from a table using a command like
> this:

> DELETE FROM customer *;

> The question is: what is the star? Is it a table alias or an
> output_expression?

Neither; it specifies to search the table and its inheritance children,
ie, the opposite of ONLY.  This has been the default behavior (unless
you change the setting of sql_inheritance) for many years, so "*" has
largely fallen into disuse; but it's still accepted.

However ... I went looking for documentation on this point, and I'm
darned if I can find any.  There certainly used to be some, but
apparently somebody got over-eager about editing the docs to reflect
the modern default behavior.  The "*" doesn't even appear in the syntax
summaries for most of the commands where it's allowed, which is flat
wrong --- anywhere you can write "ONLY tablename", it's valid to write
"tablename*" instead.

So we have some docs work to do.  Thanks for pointing it out.

            regards, tom lane

On Sun, Sep 16, 2012 at 11:58:06PM -0400, Tom Lane wrote:
> barrybrown@sierracollege.edu writes:
> > I sometime see my users delete all rows from a table using a command like
> > this:
>
> > DELETE FROM customer *;
>
> > The question is: what is the star? Is it a table alias or an
> > output_expression?
>
> Neither; it specifies to search the table and its inheritance children,
> ie, the opposite of ONLY.  This has been the default behavior (unless
> you change the setting of sql_inheritance) for many years, so "*" has
> largely fallen into disuse; but it's still accepted.
>
> However ... I went looking for documentation on this point, and I'm
> darned if I can find any.  There certainly used to be some, but
> apparently somebody got over-eager about editing the docs to reflect
> the modern default behavior.  The "*" doesn't even appear in the syntax
> summaries for most of the commands where it's allowed, which is flat
> wrong --- anywhere you can write "ONLY tablename", it's valid to write
> "tablename*" instead.
>
> So we have some docs work to do.  Thanks for pointing it out.

Is there any value to having * vs just not using ONLY?  I am not sure
documenting this is helping us, and it would add more clutter.  Isn't
this like how we don't document the old COPY syntax.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:
> Is there any value to having * vs just not using ONLY?  I am not sure
> documenting this is helping us, and it would add more clutter.  Isn't
> this like how we don't document the old COPY syntax.

I beg your pardon?  The old COPY syntax certainly is documented; see
the bottom of the COPY reference page.

We can't just not document it, or we'll get complaints exactly like
this one.  I'm not sure how come we didn't get some sooner.

            regards, tom lane