Thread: case sensitivity

case sensitivity

From
Shachar Shemesh
Date:
--
Shachar Shemesh
Open Source integration consultant
Home page & resume - http://www.shemesh.biz/
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :    Shachar Shemesh
Your email address    :       psql@shemesh.biz


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Windows XP Pro

  PostgreSQL version (example: PostgreSQL-7.3.3):   PostgreSQL-7.3.1wina1

  Compiler used (example:  gcc 2.95.2)        : Binary package from the site


Please enter a FULL description of your problem:
------------------------------------------------
When sending SQL queries that are not double quoted, the documentation states
that postgresql should treat it as case independant. This is also the SQL standard.

Postgresql, instead, makes the identifiers in the query lowercase. While this is
equivalent if ALL queries are not double quoted, this is a major problem when
migrating data from other databases.

Also, the MFC rowset class opens the data using a query that does have double quotes,
which means that applications that are meant to work with more than one backend,
and which use the MFC class, do not work (and have no way of being made to work without
rewriting the entire application).


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
create table "Table" ( id int );
select * from Table;

You get "relation table not found".


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
When matching against unquoted IDs, simply ignore the case.


Re: case sensitivity

From
Stephan Szabo
Date:
>Postgresql, instead, makes the identifiers in the query lowercase. While

Which we know is incorrect.  We should instead make it uppercase, but
that would break compatibility with older version (SQL 92 draft, 5.2 SR10)

>create table "Table" ( id int );
>select * from Table;

>You get "relation table not found".

That seems to me to be the correct results given 5.2 SR 13.

"A <regular identifier> and a <delimited identifier> are equiva-
lent if the <identifier body> of the <regular identifier> (with
every letter that is a lower-case letter replaced by the equiva-
lent 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 <dou-
blequote symbol> replaced by <double quote>), considered as
the repetition of a <character string literal> that specifies a
<character set specification> of SQL_TEXT and an implementation-
defined collation that is sensitive to case, compare equally
according to the comparison rules in Subclause 8.2, "<comparison
predicate>"."

I believe that it would require the identifiers in the following to
be the same, whereas PostgreSQL would treat them as different.
create table "TABLE"(id int);
select * from Table;

Re: case sensitivity

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I believe that it would require the identifiers in the following to
> be the same, whereas PostgreSQL would treat them as different.

See also the example and footnote at the end of section 1.1.1 of our
documentation:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
(not sure why the link to the footnote doesn't work in that version, but
the footnote is at the bottom of the page).

There has been some talk of providing an alternate mode in which
unquoted identifiers are folded per spec, but this seems likely to
break enough code that no one has really wanted to do it.  My guess
is that we will remain intentionally non compliant on this point
forever.

            regards, tom lane

Re: case sensitivity

From
Shachar Shemesh
Date:
Tom Lane wrote:

>See also the example and footnote at the end of section 1.1.1 of our
>documentation:
>http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
>(not sure why the link to the footnote doesn't work in that version, but
>the footnote is at the bottom of the page).
>
>There has been some talk of providing an alternate mode in which
>unquoted identifiers are folded per spec, but this seems likely to
>break enough code that no one has really wanted to do it.  My guess
>is that we will remain intentionally non compliant on this point
>forever.
>
>            regards, tom lane
>
>
Just so we are clear what's at stake here.

I am trying to perform a migration to PG-SQL for some company. They
already support access, MS-SQL and Oracle, and want to support PG-SQL as
well. The problem is that the application is MFC, and CRowSet opens the
table double-quoting identifiers. The application itself does not. All
databases carry all identifiers in allcaps. As some of the ODBC code is
outside the specific application's control, I cannot tell them to "quote
or unquote all statements". This may drop the whole project, which would
be a real shame.

Documenting this incompatibility is fine as far as it goes, but it does
not cover the migration very well.

Also something to ponder is this. When I run psql on Debian Sid, and I do:
create table "Test" ();
select * from table;

The query runs fine! It seems that PG-SQL 7.3.3. on Linux (at least the
Debian version) treats unquoted as case independant. Am I missing
something? Why can't I set a "compatibility" flag for the DB?

                Shachar

--
Shachar Shemesh
Open Source integration consultant
Home page & resume - http://www.shemesh.biz/

Re: case sensitivity

From
Stephan Szabo
Date:
On Sun, 6 Jul 2003, Shachar Shemesh wrote:

> Tom Lane wrote:
>
> >See also the example and footnote at the end of section 1.1.1 of our
> >documentation:
> >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
> >(not sure why the link to the footnote doesn't work in that version, but
> >the footnote is at the bottom of the page).
> >
> >There has been some talk of providing an alternate mode in which
> >unquoted identifiers are folded per spec, but this seems likely to
> >break enough code that no one has really wanted to do it.  My guess
> >is that we will remain intentionally non compliant on this point
> >forever.
>
> Just so we are clear what's at stake here.
>
> I am trying to perform a migration to PG-SQL for some company. They
> already support access, MS-SQL and Oracle, and want to support PG-SQL as
> well. The problem is that the application is MFC, and CRowSet opens the
> table double-quoting identifiers. The application itself does not. All
> databases carry all identifiers in allcaps. As some of the ODBC code is

Do you mean that the names are always allcaps like FOO?  That would
certainly show the incompatibility case, yeah.

> outside the specific application's control, I cannot tell them to "quote
> or unquote all statements". This may drop the whole project, which would
> be a real shame.
>
> Documenting this incompatibility is fine as far as it goes, but it does
> not cover the migration very well.
>
> Also something to ponder is this. When I run psql on Debian Sid, and I do:
> create table "Test" ();
> select * from table;

Do you mean Test here? I'd wonder if you had a test table already defined,
I can't reproduce on my 7.3.x box under redhat.

> The query runs fine! It seems that PG-SQL 7.3.3. on Linux (at least the
> Debian version) treats unquoted as case independant. Am I missing
> something? Why can't I set a "compatibility" flag for the DB?

As Tom said, noone's wanted to go through and find all the things that
giving an uppercase folding option would break.  For example, fairly
quickly after simply making the case folding go the other way for
identifiers, initdb breaks.  It'd probably require someone who had the
time and interest in changing it and fixing all the breakage and probably
some work from then on making sure that it stays working.

Re: case sensitivity

From
Shachar Shemesh
Date:
Stephan Szabo wrote:

>On Sun, 6 Jul 2003, Shachar Shemesh wrote:
>
>
>
>>Tom Lane wrote:
>>
>>
>>
>>>See also the example and footnote at the end of section 1.1.1 of our
>>>documentation:
>>>http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
>>>(not sure why the link to the footnote doesn't work in that version, but
>>>the footnote is at the bottom of the page).
>>>
>>>There has been some talk of providing an alternate mode in which
>>>unquoted identifiers are folded per spec, but this seems likely to
>>>break enough code that no one has really wanted to do it.  My guess
>>>is that we will remain intentionally non compliant on this point
>>>forever.
>>>
>>>
>>Just so we are clear what's at stake here.
>>
>>I am trying to perform a migration to PG-SQL for some company. They
>>already support access, MS-SQL and Oracle, and want to support PG-SQL as
>>well. The problem is that the application is MFC, and CRowSet opens the
>>table double-quoting identifiers. The application itself does not. All
>>databases carry all identifiers in allcaps. As some of the ODBC code is
>>
>>
>
>Do you mean that the names are always allcaps like FOO?  That would
>certainly show the incompatibility case, yeah.
>
That's how the app doesn't care whether its queries are quoted or not.

>>outside the specific application's control, I cannot tell them to "quote
>>or unquote all statements". This may drop the whole project, which would
>>be a real shame.
>>
>>Documenting this incompatibility is fine as far as it goes, but it does
>>not cover the migration very well.
>>
>>Also something to ponder is this. When I run psql on Debian Sid, and I do:
>>create table "Test" ();
>>select * from table;
>>
>>
>
>Do you mean Test here?
>
Yes, I did. Sorry.

> I'd wonder if you had a test table already defined,
>I can't reproduce on my 7.3.x box under redhat.
>
>
Neither can I, now. I guess it was something basic I missed when filing
the report. Please ignore.

>>The query runs fine! It seems that PG-SQL 7.3.3. on Linux (at least the
>>Debian version) treats unquoted as case independant. Am I missing
>>something? Why can't I set a "compatibility" flag for the DB?
>>
>>
>
>As Tom said, noone's wanted to go through and find all the things that
>giving an uppercase folding option would break.  For example, fairly
>quickly after simply making the case folding go the other way for
>identifiers, initdb breaks.  It'd probably require someone who had the
>time and interest in changing it and fixing all the breakage and probably
>some work from then on making sure that it stays working.
>
>
I'm willing to give it a go, but I'm going to need a few pointers. I
have never done anything with the psql source, and I will need to some
help in navigating.

Can you please point me to the place where the case comparison is being
performed?

             Shachar

--
Shachar Shemesh
Open Source integration consultant
Home page & resume - http://www.shemesh.biz/