From: Andrew Dunstan <andrew@dunslane.net>
To: Chuck McDevitt <cmcdevitt@greenplum.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: beau hargis <beauh@bluefrogmobile.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case
Date: Tue, 31 Oct 2006 12:55:46 -0500
Message-ID: <45478E22.8060805@dunslane.net> (raw)
In-Reply-To: <EB48EBF3B239E948AC1E3F3780CF8F88012F89F8@MI8NYCMAIL02.Mi8.com>
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>
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...
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew@dunslane.net, cmcdevitt@greenplum.com, tgl@sss.pgh.pa.us, beauh@bluefrogmobile.com, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case
In-Reply-To: <45478E22.8060805@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox