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:
Show quoted text
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 VIEWLong 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;
CREATENote 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
Import Notes
Resolved by subject fallback
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 VIEWThis 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 VIEWThis 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