Submitted by: Eugenk Konkov (kes)

Is duplicated by CORE5766

Votes: 1

For more details see 'Upper case behaviour' thread in mailing list 'firebird-support@yahoogroups.com'
>Martijn Tonies
>> Along with Eugen Konkov, I would like clarification of where case
>> preservation would cause problems with tools that assume identifiers pulled
>> from metadata can always be quoted. Seems like it should not be a problem.

>It will be a problem.
>Case preserving, and then quoting and re-creating objects leads to
>case sensitive objects.
create table BigTable (...)
if I create the table 'BigTable' (when I use single quotes I just mark
object name in text. This is not mean "quoting" at all)
then extract metadata by some tool, which will quote "BigTable"
create table "BigTable" (...)
both queries original and extracted are the same. (see about)

>Ann W.Harrison
>Sure. You do this:
>create table BigTable (BigField varchar (20000));
>The metadata is stored preserving case. A portable tool does a
>show tables to get its table list and sees
>...
>BigTable
>...
>It then generates a query
>select * from "BigTable";
>Which fails because "BigTable" is not the same as BigTable under
>the ISO SQL Standard.

Why are those not the same?
When executing query 'select * from "BigTable"' the
"BigTable" case sensetively matches BigTable
so both queries
'select * from BigTable' case insensetively matches BigTable
'select * from "BigTable' case sensetively matches BigTable
will be same, will not?

Another question: SQL says that object names must not be case sensetive
so
'create table a (...)' will seccess and second 'create table A (...)' must fail
because of you can not create object with name of object which is already in database
So that is a BUG when FireBird allow to create two objects 'a' and 'A' in the same database

Other cases:
'create table a (...)' will create table 'a' in database
so
'create table A (...)" must fail because of there is the object 'a' in database
'create table "a" (...)" must fail because of there is the object 'a' in database
'create table "A" (...)" must fail because of there is the object 'a' in database

>Doug Chamberlin
>Is there no indicator stored in the database that a tool can use to tell
>whether identifiers were created with un-quoted or quoted style?
>Adam
>eg. RDB$RELATIONS.RDB$RELATION_NAME can have a sister field
>RDB$RELATIONS.RDB$RELATION_DISPLAYNAME
> Geoff Worboys
> If the display name is not the same as the actual name then you will need to
>ensure that tools/options are available to retrieve the
>actual names too
FireBird just return names AS IS and tools must show names AS IS so there will not any ambigious
It is no need any indicator. FIRST of all indicator or additional RDB$ will do application logic more complex
That will force you to remember is name you get RELATION_DISPLAYNAME or
RELATION_NAME and if you foget that fact it may be the cause of a BUG
Let's propose next algorithm to do with quotes
if (objectName.isQuoted) {
//objectName is equal "myTable"
match ( dbObject, objectName ) // matches as is without any convertions: 'myTable' == 'myTable'
}
else {
//objectName is equal to 'myTable' or 'mytable' or 'MYTABLE' all is unquoted
match ( UPPERCASE(dbObjectName), UPPERCASE(objectName) )
// you UPPERCASE database object names and names in query so
// UPPERCASE( 'myTable' ) == UPPERCASE( 'myTable' )
// UPPERCASE( 'myTable' ) == UPPERCASE( 'mytable' )
// UPPERCASE( 'myTable' ) == UPPERCASE( 'MYTABLE' )
// for speed purpose you can cache anywhere uppercased name for database object
match( dbObjectName.uppercased , UPPERCASE(objectName) )
}
CONCLUSION: case-presernig has no effect. so it is safe to use case-preserve instead of UPPERCASE

SECOND. Third party tools must not do any suggestion about queries, names and so on
What will you do if tool suppose 0.0 when you actually get 0?
Why do third party suppose quotes 'select * from "myTable"' when there is actually no any 'select * from myTable'?
I agree with quoting only in one case when they get object name alone. For example,
create table statement:
create table myTable (
fieldID integer
);
that all be OK if I get
create table "myTable" (
"fieldID" integer
);
Here no any changes of query meaning because of both
'fieldID' and "fieldID" will match fieldID case insensetive and case sensetive accordingly
But when in my stored proc, for example if I mean:
BEGIN
....
select fieldID from myTable;
....
END
I DO NOT MEAN CASE SENSETIVE AT ALL!!! notice no quotes

Why tools suppose quoting in this case?
BEGIN
....
select "fieldID" from "myTable";
....
END
So that is a bug of third party tool.
Let see what is going today. All agree with that fact that
select fieldid from mytable; and
select FIELDID from MYTABLE; are the same
so when I write in my stored proc:
BEGIN
....
select FieLdID from MyTaBLE;
....
END
I do not mean "FieLdID" and "MyTaBLE". So third party tools MUST NOT do any suggestion
and MUST NOT return
BEGIN
....
select "FieLdID" from "MyTaBLE";
....
END
because it will fail even now with current behaviour of FireBird.
Therefore you must agree with that fact that third party tools MUST NOT do any sugestion and
MUST return names in expresioins/statements AS IS:
BEGIN
....
select FieLdID from MyTaBLE;
....
END
now if FireBird is case preserving this query in stored proc still match 'fieldID' field of 'myTable' table
CONCLUSION: case-presernig has no effect. so it is safe to use case-preserve instead of UPPERCASE

another case.
Let's suppose you support two and more objects to have same name case sensively
create table aA ( ... );
create table aa ( ... );
Both queries will create two databse tables 'aA' and 'aa'
Notice that I have not used quotes to create tables
and if you will preserve case there no conflict! (there no SQL ERROR: table 'aa' is aready exists)
now suppose that I allow such names 'aA' and 'aa'
so if I allow I MUST BE responsible while write such queries
select * from AA; MUST RESULT
SQL ERROR: table 'AA' is ambiguous
but
select * from aa; must be OK
select * from aA; must also be OK
(see algorith at the bottom)
doing such queries I am not resposible for my actions here
so I repeat.
AS I ALLOW same names in database like 'aa' and 'aA'
I MUST be responsible for that and must use quoting
select * from "aa";
select * from "aA";
Because of this is RARE case and 99% no not use such names as 'aa' and 'aA'
in database WILL BE only unique names when uppercasing them
A, B, AA, AB, BA and so on
so that WILL SAFE to leave object names case-preserve instead of UPPERCASE
that WILL SAFE also to do next:
create table myTable (...);
select * from mytable;
select * from MYTABLE;
select * from myTable;
select * from "myTable";
all of this MUST mean THE SAME!!

BUT:
select * from "mytable" must mean exact 'mytable'
select * from "MYTABLE" must mean exact 'MYTABLE'

See next algorithm to do with quotes
if (objectName.isQuoted) {
//objectName is equal "myTable"
match ( dbObject, objectName ) // matches as is without any convertions: 'myTable' == 'myTable'
}
else {
//objectName is equal to 'myTable' or 'mytable' or 'MYTABLE' all is unquoted
match ( UPPERCASE(dbObjectName), UPPERCASE(objectName) )
// you UPPERCASE database object names and names in query so
// UPPERCASE( 'myTable' ) == UPPERCASE( 'myTable' )
// UPPERCASE( 'myTable' ) == UPPERCASE( 'mytable' )
// UPPERCASE( 'myTable' ) == UPPERCASE( 'MYTABLE' )
// for speed purpose you can cache anywhere uppercased name for database object
match( dbObjectName.uppercased , UPPERCASE(objectName) )
}

I think this is VERY SAFE

>Martijn Tonies
>So you end up with 1 column of table names with values like:

>MyTABLE
>MYTABLE
>MyTAble

>These are 3 different tables, obviously, MYTABLE can be used unquoted
>(all uppercase), and the other ones need to be quoted. Why? Because they
>contain mixed case identifiers.

AS I ALLOW same names in database like 'aa' and 'aA'
I MUST be responsible for that and must use quoting
select * from "aa";
select * from "aA";

so queries
select * from MyTABLE;
select * from MYTABLE;
select * from MyTAble;
must result SQL ERROR: ambigious object name 'MyTABLE'/'MYTABLE'/'MyTAble'