--- In ib-support@y..., Nando Dessena <nandod@d...> wrote:

> Jordi,
>
> > CREATE TABLE "empleado" (
> > "dni" INTEGER NOT NULL,
> > "nombre" VARCHAR(50));
>
> > select * from EMPLEADO => ERROR!
> > select * from empleado => ERROR!
> > select * from "EMPLEADo" => ERROR!
> > select * from "EMPLEADO" => ERROR!
> > select * from "empleado" => WORKS!
> >
> > But if database objects are declared without double quotes:
> >
> > CREATE TABLE empleado (
> > dni INTEGER NOT NULL,
> > nombre VARCHAR(50));
>
> > select * from EMPLEADO => WORKS!
> > select * from empleado => WORKS!
> > select * from "EMPLEADo" => ERROR!
> > select * from "EMPLEADO" => WORKS!
> > select * from "empleado" => ERROR!
> >
> > What happens? is it an Interbase bug or the problem is in IBExpert

or maybe

> > the Gemini ODBC?
>
> to me, it is exactly as designed/implemented/advertised.

I agree -- what you are seeing is exactly the behaviour I'd expect,
according to the SQL standard.

In the SQL standard, names without quotes are called <regular
identifiers> and are case-insensitive. Names within quotes are called
<delimited identifiers> and are case sensitive.

If you create a table (or column or whatever) and specify its name
within double quotes, then you'd better be prepared to refer to it
within double quotes, and using the original case, forever more.
(Personally, I avoid using delimited identifiers as much as possible.)

Given that, it might surprise you that in your second set of tests,
select * from "EMPLEADO" works. This, too, according to the SQL
standard, is correct.

From SQL92 subclause 5.2 <token> and <separator>, syntax rule 13 says:

"A <regular identifier> and a <delimited identifier> are equivalent if
the <identifier body> of the <regular identifier> (with every letter
that is a lower-case letter replaced by the equivalent upper-case
letter or letters) and the <delimited identifier body> of the
<delimited identifier> <... blah blah blah ... >, compare equally
according to the comparison rules in Subclause 8.2, "<comparison
predicate>".

Cheers,
db