Re: case sensitive vs insensitive

On 10 May 2011, at 20:13, David McNeil wrote:
> On the working group call today we were discussing whether SQL identifiers in R2RML should be interpreted in a case insensitive or case sensitive manner. I think I mis-heard Ted's proposal at the end.
> 
> Ted - If you were saying that for the simplest case they need to be interpreted in a case _in_sensitive manner... then "yes" I agree.

I don't think that works. Assuming I have my SQL quoting rules straight ...

If I do:

   CREATE TABLE Foo (Bar INT);

Then you are right; one can refer to these identifiers in a case-insensitive manner. All of these work:

   SELECT bar FROM foo;
   SELECT BAR FROM FOO;
   SELECT Bar FROM Foo;

But if I do:

   CREATE TABLE "Foo" ("Bar" INT);

Then neither of these will work in standard SQL:

   SELECT bar FROM foo;
   SELECT BAR FROM FOO;
   SELECT Bar FROM Foo;

I *have* to put the identifiers into quotes again to refer to it:

   SELECT "Bar" FROM "Foo";

But identifiers in quotes are case-sensitive, so treating them in a case-insensitive manner will produce errors. SELECT "bar" or SELECT "BAR" doesn't work.

So I believe the two possible approaches are:

a) never use quotes around identifiers in the mapping language; require that authors match the case that was used when creating the identifier if it was created with quotes; treat everything case-sensitive; let the implementation insert quotes around identifiers when it creates SQL queries (this is what D2RQ does)

b) allow use of quotes in mapping language; require that authors match the quoting style that was used when creating the identifier; follow SQL rules for case sensitivity (quoted is case sensitive; unquoted is case insensitive); let the implementation use whatever quotes were provided by the author when it creates SQL queries (this is what I intended to propose as the resolution in the call)

Best,
Richard


(Also tagging this as ISSUE-35)

Received on Tuesday, 10 May 2011 20:14:14 UTC