Re: BUG #13754: Postgres possibly accepts a typo as valid input

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: ryan(dot)vilim(at)gmail(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #13754: Postgres possibly accepts a typo as valid input
Date: 2015-11-04 02:57:44
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

ryan(dot)vilim(at)gmail(dot)com writes:
> I recently found that postgres will accept the
> SELECT -144mycol
> to be equivalent to
> SELECT -144 AS mycol.

Yup. There are two things going on there:

1. "-144mycol" is read as three tokens, "-", "144", and "mycol".

2. AS is optional.

> I haven't been able to find any other references to this being standard SQL.

As best I can tell, the SQL spec says that in spec-conformant SQL there
should be a space between "144" and "mycol", because it says

Any <token> may be followed by a <separator>. A <nondelimiter
token> shall be followed by a <delimiter token> or a <separator>.

and <regular identifier>, <key word>, and <unsigned numeric literal> all
count as <nondelimiter token>s. (White space is a <separator>.) On the
other hand, this restriction seems a tad arbitrary, because quoted
identifiers count as <delimiter token>s. Which means that


isn't quite valid SQL, but


is gold-plated valid SQL. So you would need a rather curious set of
assumptions about the abilities of your lexer in order to decide that the
first case is ambiguous while the second isn't. You could maybe argue
that the second case is more readable, but that argument seems a bit thin.
In any case, the odds that we'd change this behavior in PG are nil;
there's no real upside to doing so and we'd inevitably break a lot of
existing applications if we did.

As for #2 (AS being optional), that's required by spec as well.
Personally I find it one of the dumbest syntax decisions in the whole
language, but it's in the spec so we're stuck with it.

> I think it might be a bug in postgres.

You can argue whether it's a bug or not, but it's intentional behavior.

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message breen 2015-11-04 06:23:15 BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled
Previous Message Alvaro Herrera 2015-11-04 02:16:18 Re: BUG #13741: vacuumdb does not accept valid password