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

[r***@gmail.com]

The following bug has been logged on the website:

Bug reference: 13754
Logged by: Ryan Vilim
Email address: ***@gmail.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

[Tom Lane]

Post by r***@gmail.com
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.

Post by r***@gmail.com
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.

Post by r***@gmail.com
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