>>select * from "BigTable";
>>
>>Which fails because "BigTable" is not the same as BigTable
>>under the ISO SQL Standard.

> Ah, so the problem arises when un-quoted and quoted usage is
> both used over time. Why would anyone do that or why would a
> tool do that?

There is nothing, currently, stopping a person from mixing name
styles in the one database. Indeed anyone that does use
delimited/quoted names has a database with mixed name styles
because all the metadata uses undelimited names.

Firebird and SQL are actually defined to have case-sensitive
names, it is merely input parser that ignores case when no
delimiters are used. This is quite different to being case-
insensitive (xref with Windows file names that are always
case-insensitive, quote delimiting is only used to handle
special characters and do not change the case sensitivity).
This impact of this in SQL can be seen in the following...

create table MYTABLE (...)
create table "MYTABLE" (...)
create table myTable (...)

Are all equivalent statements, they all create a table with a
name of 'MYTABLE' that can be accessed by any of the following
statements:
select * from MYTABLE
select * from "MYTABLE"
select * from myTable

Whereas this:
create table "myTable" (...)

creates a table with the name 'myTable' that is distinct from
'MYTABLE' and can exist in the same database (although it would
not be recommended). This table can ONLY be accessed with
the statement
select * from "myTable"


Now turn around and change the rules, telling Firebird to be
case-preserving, and what happens to the above statements?

Look at:
create table myTable (...)
and create table "myTable" (...)

Are these distinct tables now?

select * from myTable
and select * from "myTable"

Which tables do these select?

AFAICT only way to resolve those questions reliably would be
to make it so that MYTABLE and "MYTABLE" were different
identifiers and that is a distinct and unpleasant change in
behaviour. At the moment it is safe for a program or tool to
_always_ quote a name read from the database (because MYTABLE
and "MYTABLE" are equivalent).


If Firebird/SQL were truly case-insensitive with respect to
names then myTable, MYTABLE, "myTable" and "MYTABLE" would
all refer to the same table, but quote delimiting does not
work that way in SQL (mores the pity).

And notice the inconsistency here. In Firebird, obeying SQL
rules, the name MYTABLE and "MYTABLE" are equivalent. In
other systems (ones using lower case internally), also obeying
the SQL rules, this would not work.

--
Geoff Worboys
Telesis Computing