Postgres Pro
Downloads
Home   >   mailing lists

Thread: underscore split to alias

underscore split to alias

From
"Erik Rijkers"
Date:
I am not sure this is a bug, but I was surprised by the following behaviour
in HEAD and 8.4.4 (instances built today, 2010.05.14):

Invalid (?) values like 123_456 are split before the underscore and interpreted as
123 as "456":


$ psql -p 6591 -d testdb -c "select 123_456, current_setting('server_version')"_456 | current_setting
------+----------------- 123 | 9.0beta1
(1 row)


$ psql -p 6584 -d testdb -c "select 123_456, current_setting('server_version')"_456 | current_setting
------+----------------- 123 | 8.4.4
(1 row)


Older versions (8.3 thru 7.4) all give an error:


$ psql -p 6583 -d testdb -c "select 123_456, current_setting('server_version')"
ERROR:  syntax error at or near "_456"
LINE 1: select 123_456, current_setting('server_version')                 ^

The error given by the older servers seems justified.  I assume
this is a bug and not a deliberate change?


thanks,


Erik Rijkers

Re: underscore split to alias

From
Tom Lane
Date:
"Erik Rijkers" <er@xs4all.nl> writes:
> I am not sure this is a bug, but I was surprised by the following behaviour
> in HEAD and 8.4.4 (instances built today, 2010.05.14):

> Invalid (?) values like 123_456 are split before the underscore and interpreted as
> 123 as "456":

All versions of postgres will parse 123_456 as an integer (123)
immediately followed by an identifier (_456).  In the particular context
that this is all of a top-level SELECT item, the SQL spec requires that
we parse this as an integer and a column alias (with an implied AS).
We failed to do that before 8.4, but now honor the spec requirement
that AS can be omitted.  Personally I think that's one of the stupider,
more error-prone aspects of the spec's syntax, but nonetheless it's
required by spec ....
        regards, tom lane