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

The following bug has been logged on the website:

Bug reference: 13754
Logged by: Ryan Vilim
Email address: ryan(dot)vilim(at)gmail(dot)com
PostgreSQL version: 9.4.1
Operating system: Mac
Description:

I recently found that postgres will accept the

SELECT -144mycol

to be equivalent to

SELECT -144 AS mycol.

As long as the first bits work out to be numbers it accepts it. For example,
it will also interpret

SELECT FLOOR(-144.4)*4mycol

as

SELECT FLOOR(144.4)*4 as mycol

I haven't been able to find any other references to this being standard SQL.
I think it might be a bug in postgres.

Ryan

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

-144mycol

isn't quite valid SQL, but

-144"mycol"

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