Re: case sensitivity

On Mon, Jun 29, 2009 at 9:23 AM, David Fetter <[email protected]> wrote:

> On Mon, Jun 29, 2009 at 04:26:30PM +0100, Tim Bunce wrote:
> > Oracle is one of those databases that uppercases (unquoted) names.
> > That's perfectly valid behaviour - though it can be a major pain.
>
> It's not just valid behavior.  It's mandated by the SQL standard :(
>


The standard also says that these are 3 separate tables:

CREATE TABLE "foo" (...);
CREATE TABLE "FOO" (...);
CREATE TABLE "Foo" (...);

Further, empirically, one of the first two is equivalent to:

CREATE TABLE foo (...);

The standard implies it is the second (upper-case) version; historic
practice usually treats it as the first (lower-case) version.

Note that the following are all equivalent to each other:

CREATE TABLE foo (...);
CREATE TABLE FOO (...);
CREATE TABLE Foo (...);

This complicates the dictionary look-up enormously.  You have to observe
precisely how the identifier was written in the SQL statement,

Of course, extra idiosyncrasies also come into effect.

* Informix Dynamic Server only recognizes delimited identifiers (inside
double quotes) if configured to do so.
* MS SQL Server uses square brackets in place of double quotes.
* MySQL uses back quotes in place of double quotes.

I expect there are other idiosyncrasies in other DBMS that I'm simply
unaware of.  (I'm also studiously ignoring schema names - the complicate the
matter still more.)

-- 
Jonathan Leffler <[email protected]>  #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."