case-sensitivity inconsistency in quoted column aliases in FROM subselects
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Mike Hoolehan
Your email address : mike@sycamorehq.com
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : RH 7.2, Kernel 2.4.9-13
PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3
Compiler used (example: gcc 2.95.2) : gcc 2.96 (rpm version)
Please enter a FULL description of your problem:
------------------------------------------------
if a quoted column alias in a FROM clause sub-select contains upper-case
chars, then that column cannot be later referenced without using quotes
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery
WHERE Foo = 'whatever';
results in
"ERROR: Attribute 'foo' not found"
no matter what capitalization is used for "Foo" in the where clause
(i.e. foo='whatever', FOO='whatever', etc).
I assume this is a bug, since if the column is aliases as "foo" (with
quotes, all lowercase), then the column can later be referenced
without quotes with case-insensitivity.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Mike Hoolehan <mike@sycamorehq.com> writes:
SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery
WHERE Foo = 'whatever';
results in
"ERROR: Attribute 'foo' not found"
no matter what capitalization is used for "Foo" in the where clause
(i.e. foo='whatever', FOO='whatever', etc).
I assume this is a bug, since if the column is aliases as "foo" (with
quotes, all lowercase), then the column can later be referenced
without quotes with case-insensitivity.
This is not a bug, it's the expected behavior. See the discussion of
identifier case sensitivity in the manual, eg
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
regards, tom lane
Mike Hoolehan writes:
if a quoted column alias in a FROM clause sub-select contains upper-case
chars, then that column cannot be later referenced without using quotes
This is expected behaviour. See also
http://www.de.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
--
Peter Eisentraut peter_e@gmx.net
On Wed, 9 Jan 2002, Mike Hoolehan wrote:
Please enter a FULL description of your problem:
------------------------------------------------
if a quoted column alias in a FROM clause sub-select contains upper-case
chars, then that column cannot be later referenced without using quotesPlease describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery
WHERE Foo = 'whatever';
results in
"ERROR: Attribute 'foo' not found"
no matter what capitalization is used for "Foo" in the where clause
(i.e. foo='whatever', FOO='whatever', etc).
I assume this is a bug, since if the column is aliases as "foo" (with
quotes, all lowercase), then the column can later be referenced
without quotes with case-insensitivity.
Unquoted column and table names are folded to lower case (thus Foo
is the same as foo or FOO or "foo", but "Foo" is different). In
general, although "foo" will be matched, if you use quotes around
the name in its creation you should always use quotes thereafter.