Message-ID: <45478E22.8060805@dunslane.net>
Date: Tue, 31 Oct 2006 12:55:46 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.6) Gecko/20060804 Fedora/1.0.4-0.5.1.fc5 SeaMonkey/1.0.4
MIME-Version: 1.0
To: Chuck McDevitt <cmcdevitt@greenplum.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, beau hargis <beauh@bluefrogmobile.com>,
	pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case
References: <200610271623.37525.beauh@bluefrogmobile.com>
	<1162003132.687.8.camel@pampa>
	<200610301928.51255.beauh@bluefrogmobile.com>
	<23177.1162265010@sss.pgh.pa.us>
	<EB48EBF3B239E948AC1E3F3780CF8F88012F88DD@MI8NYCMAIL02.Mi8.com>
	<24861.1162276496@sss.pgh.pa.us>
	<EB48EBF3B239E948AC1E3F3780CF8F88012F89F8@MI8NYCMAIL02.Mi8.com>
In-Reply-To: <EB48EBF3B239E948AC1E3F3780CF8F88012F89F8@MI8NYCMAIL02.Mi8.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


There was some discussion a couple of years ago on the -hackers list 
about it, so you might like to review the archives. The consensus seemed 
to be that behaviour would need to be set no later than createdb time.  
The options I thought of were:

. current postgres behaviour (we need to do this for legacy reasons, of 
course, as well as to keep happy the legions who hate using upper case 
for anything)
. strictly spec compliant (same as current behaviour, but folding to 
upper case for unquoted identifiers rather than lower)
. fully case sensitive even for unquoted identifiers (not spec compliant 
at all, but nevertheless possibly attractive especially for people 
migrating from MS SQLServer, where it is an option, IIRC).

To this you propose, as I understand it, to have a fourth possibility 
which would be spec compliant for comparison purposes but would label 
result set columns with the case preserved name originally used (or 
would you use the casing used in the query?).

These could be accomplished I think with a second catalog column like 
you suggest, in a number of places, but making sure all the code paths 
were covered might be somewhat laborious. We could probably add the 
second option without being nearly so invasive, though, and some people 
might feel that that would be sufficient.

cheers

andrew

Chuck McDevitt wrote:
> 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 set specification> of
> SQL_IDENTIFIER
>             and an implementation-defined collation that is sensitive to
>             case, compare equally according to 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, compare equally 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...
>
>