Re: case-sensitivity inconsistency in quoted column aliases

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.