Thread: 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 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

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

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 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.

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.