Обсуждение: Bug #545: Keyword USER not detected in CREATE VIEW

Avi Kivity (avi@argo.co.il) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Keyword USER not detected in CREATE VIEW

Long Description
The reserved keyword USER is rejected in CREATE TABLE, as expected:

  avi=> create table x (user varchar);
  ERROR:  parser: parse error at or near "user"

In my ignorance, however, I tried to create a view with a field named user:

  avi=> create table y (x varchar);
  CREATE
  avi=> create view z as select x as user from y;
  CREATE

Note no error.

  avi=> \d z
                   View "z"
   Attribute |       Type        | Modifier
  -----------+-------------------+----------
   user      | character varying |
  View definition: SELECT y.x AS "user" FROM y;

  avi=> insert into y values ('zz');
  INSERT 43771 1
  avi=> select user from z;
   current_user
  --------------
   avi
  (1 row)

Wrong value returned.

  avi=> select "user" from z;
   user
  ------
   zz
  (1 row)

Correct value returned.

My expected result: an error message in CREATE VIEW


Sample Code


No file was uploaded with this report

This is because user is a "reserved" word.
user is a macro for the function current_user()
Try this SQL:   SELECT user;

It is best to change the table name from user to username;

However it is probably a wise idea for Postgres to emit some sort of error
on the create mentioning the reserved word.


At 11:23 AM 12/28/01 -0500, you wrote:
>Avi Kivity (avi@argo.co.il) reports a bug with a severity of 3
>The lower the number the more severe it is.
>
>Short Description
>Keyword USER not detected in CREATE VIEW
>
>Long Description
>The reserved keyword USER is rejected in CREATE TABLE, as expected:
>
>  avi=> create table x (user varchar);
>  ERROR:  parser: parse error at or near "user"
>
>In my ignorance, however, I tried to create a view with a field named user:
>
>  avi=> create table y (x varchar);
>  CREATE
>  avi=> create view z as select x as user from y;
>  CREATE
>
>Note no error.
>
>  avi=> \d z
>                   View "z"
>   Attribute |       Type        | Modifier
>  -----------+-------------------+----------
>   user      | character varying |
>  View definition: SELECT y.x AS "user" FROM y;
>
>  avi=> insert into y values ('zz');
>  INSERT 43771 1
>  avi=> select user from z;
>   current_user
>  --------------
>   avi
>  (1 row)
>
>Wrong value returned.
>
>  avi=> select "user" from z;
>   user
>  ------
>   zz
>  (1 row)
>
>Correct value returned.
>
>My expected result: an error message in CREATE VIEW
>
>
>Sample Code
>
>
>No file was uploaded with this report
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>

pgsql-bugs@postgresql.org writes:
>   avi=> create view z as select x as user from y;

> My expected result: an error message in CREATE VIEW

This is not a bug, it is intentional behavior.  "AS" names are not
restricted to be non-reserved words.

            regards, tom lane

> >   avi=> create view z as select x as user from y;
> > My expected result: an error message in CREATE VIEW
> This is not a bug, it is intentional behavior.  "AS" names are not
> restricted to be non-reserved words.

Sure, but his point is that the resulting view has a column which is
named "user" and which is not directly accessible without using double
quotes.

I'm not sure that would qualify as a bug, but certainly is a gotcha...

                      - Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:
> avi=> create view z as select x as user from y;
> My expected result: an error message in CREATE VIEW
>> This is not a bug, it is intentional behavior.  "AS" names are not
>> restricted to be non-reserved words.

> Sure, but his point is that the resulting view has a column which is
> named "user" and which is not directly accessible without using double
> quotes.

Yeah, but what would you do about it?  The identical situation can be
created in at least three ways:

    create table foo ("user" varchar);

    create view foo as select x as "user" from y;

    create view foo as select x as user from y;

I would certainly object to emitting a warning notice in the first two
cases.  There might be some merit in warning about the third, but I don't
see a reasonable way to distinguish it from the others.

A not-unrelated point: would you also have the system warn about

    create table foo ("Foo" varchar);

Here again the column cannot be named without using quotes.  But I'd
see issuing a warning about it as unwarranted nannyism.

            regards, tom lane

...
> Here again the column cannot be named without using quotes.  But I'd
> see issuing a warning about it as unwarranted nannyism.

Yup. I'm not suggesting a change, but the more explicit discussion on
*why* this may be acceptable is probably helpful. I think we are pretty
close in 'tude on the nannyism issues; I like building bigger guns too
;)

                      - Thomas