Re: [SQL] Case Preservation disregarding case

We treated quoted identifiers as case-specific, as the spec requires.

In the catalog, we stored TWO columns... The column name with case
converted as appropriate (as PostgreSQL already does), used for looking
up the attribute,
And a second column, which was the column name with the case exactly as
entered by the user.

So, your example would work just fine.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Monday, October 30, 2006 10:35 PM
To: Chuck McDevitt
Cc: beau hargis; pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [SQL] Case Preservation disregarding case
sensitivity?

"Chuck McDevitt"  writes:
> At Teradata, we certainly interpreted the spec to allow
case-preserving,
> but case-insensitive, identifiers.

Really?

As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2)
       26) A  and a  are           equivalent if the  of the            (with every letter that is a lower-case letter replaced by
the           corresponding upper-case letter or letters) and the
 of the  (with all           occurrences of  replaced by  and all           occurrences of  replaced by ),           considered as the repetition of a            that specifies a  of
SQL_IDENTIFIER           and an implementation-defined collation that is sensitive to           case, compare equally according to the comparison rules in           Subclause 8.2, "".
       27) Two s are equivalent if their
s, considered as the repetition of a
 that specifies a            of SQL_IDENTIFIER and an implementation-defined collation           that is sensitive to case, compare equally according to the           comparison rules in Subclause 8.2, "".

Note well the "sensitive to case" bits there.  Now consider
CREATE TABLE tab (	"foobar" int,	"FooBar" timestamp,	"FOOBAR" varchar(3));

We can *not* reject this as containing duplicate column names, else we
have certainly violated rule 27.  Now what will you do with
SELECT fooBar FROM tab;

?  The spec is unquestionably on the side of "you selected the varchar
column"; historical Postgres practice is on the side of "you selected
the int column".  AFAICS a case-insensitive approach would have to
fail with some "I can't identify which column you mean" error.  I am
interested to see where you find support for that in the spec...
		regards, tom lane