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" <cmcdevitt@greenplum.com> 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 <regular identifier> and a <delimited identifier> are           equivalent if the <identifier body> of the
<regular
identifier>           (with every letter that is a lower-case letter replaced by
the           corresponding upper-case letter or letters) and the
<delimited           identifier body> of the <delimited identifier> (with all           occurrences of <quote> replaced
by<quote symbol> and all           occurrences of <doublequote symbol> replaced by <double 
quote>),           considered as the repetition of a <character string literal>           that specifies a <character
setspecification> of 
SQL_IDENTIFIER           and an implementation-defined collation that is sensitive to           case, compare equally
accordingto the comparison rules in           Subclause 8.2, "<comparison predicate>". 
       27) Two <delimited identifier>s are equivalent if their
<delimited           identifier body>s, considered as the repetition of a
<character           string literal> that specifies a <character set
specification>           of SQL_IDENTIFIER and an implementation-defined collation           that is sensitive to case,
compareequally according to the           comparison rules in Subclause 8.2, "<comparison predicate>". 

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