Problems in the field editor with fields of the same name in two different tables

[tkszeos]

Hi,
I have the following problem in both Code Typhon and Delphi:
I have two tables, PREVHDR and LINGUE_PC, with many fields including two of these having the same name: "Arrivo".
PREVHDR."Arrivo" is DATE type, LINGUE_PC."Arrivo" is VARCHAR(20) type.

If I execute the following query in TZQuery:

Code: Select all

SELECT
    LINGUE_PC."Arrivo",
    PREVHDR."Arrivo"
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

in the Field Editor, correctly, LINGUE_PC."Arrivo" is TStringField type and PREVHDR."Arrivo" is TDateField type.
But if I run this other query:

Code: Select all

SELECT
    PREVHDR."Arrivo",
    LINGUE_PC."Arrivo"
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

in the Field Editor both fields are of the TDateField type.

If, on the other hand, I create VIEW:

Code: Select all

CREATE VIEW TEST_VIEW(
  "Arrivo",
  "Arrivo1")
AS
SELECT
    PREVHDR."Arrivo",
    LINGUE_PC."Arrivo"
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice");

and then I run the query:

Code: Select all

SELECT * FROM TEST_VIEW

in the Field Editor the types of the two fields are correct.

Is it a zeoslib bug?

I use zeoslib-code-0-4080-trunk
The problem occurs with various versions of firebird.
It was not present in Zeoslib 6.6.6

Thanks.

[tkszeos]

I noticed that the problem occurs only if the field names, in the tables, are in lower case

[marsupilami]

hello tkszeos,

which database (MySQL, Firebird, ...) do you use?

With best regards,

Jan

[tkszeos]

I'm sorry.
I use Firebird. The problem occurred with Firebird 2.5 but I have reason to believe that it occurs with all versions of Firebird.

Thanks

[Fr0sT]

What do you expect to get selecting two fields with the same name? I don't know what exactly the driver returns in this case but any high-level component will be confused with this task anyway. All dataset components are designed based on the presumption that field names are unique. Some "smart" components automatically add a number to duplicated fields but then you'll be dependent on how a component processes this case internally.
I'd say you're just doing bad things and no surprise Zeos is doing bad things too.

Simple renaming

Code: Select all

SELECT
    PREVHDR."Arrivo" AS PH_Arrivo,
    LINGUE_PC."Arrivo" AS LPC_Arrivo
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

will solve your problem.
Btw, is it really necessary to use quoted filed names? It's not recommended unless absolutely unavoidable.

[tkszeos]

Hi,
I have tested your query but, unfortunately, does not solve the problem.
I believe the error is related to Zeoslib. In fact, using other components such as IBX, UIB or SQLQuery in Code Typhon, the problem does not occur.
Among other things, I have always used the names of the listed fields without any problem.
Also I do not explain why, only by changing the position of the fields in the query, in one case it works and in the other not.
Again, the error did not occur with version 6.6.6 of Zeoslib.
Unfortunately I can not change the name of the fields because it is a database to which other software accesses.

Help me, thanks

[Fr0sT]

Are you telling that selecting fields with aliases produce the same result? Very hardly but who knows... If you provide minimal reproducible database sample or a whole project it will be easier to locate the issue.

[tkszeos]

Hi FrOsT,
I created a simple TESTDB.FDB database that demonstrates the error.
Tables T1 and T2 have a field with the same name. In T1 "Same_Name_Field" is DATE type, in T2 "Same_Name_Field" is VARCHAR (20) type.

Code: Select all

/* SQL Manager Lite for InterBase and Firebird 5.5.1.50223 */
/* ------------------------------------------------------- */
/* Host     : localhost                                    */
/* Database : C:\TEST\TESTDB.FDB                           */

CREATE DATABASE 'localhost/3050:C:\TEST\TESTDB.FDB'
  USER 'SYSDBA'
  PAGE_SIZE = 4096
  DEFAULT CHARACTER SET ISO8859_1
  COLLATION ISO8859_1;

/* Structure for the `T1` table :  */

CREATE TABLE T1 (
  T1_ID INTEGER NOT NULL,
  "Same_Name_Field" DATE NOT NULL);

ALTER TABLE T1 ADD PRIMARY KEY (T1_ID) USING INDEX RDB$PRIMARY2;

/* Structure for the `T2` table :  */

CREATE TABLE T2 (
  T2_ID INTEGER NOT NULL,
  "Same_Name_Field" VARCHAR(20),
  ID INTEGER);

ALTER TABLE T2 ADD PRIMARY KEY (T2_ID) USING INDEX RDB$PRIMARY3;

/* Privileges for the `T1` :  */

GRANT SELECT, INSERT, DELETE, REFERENCES, UPDATE ON T1 TO SYSDBA WITH GRANT OPTION;

/* Privileges for the `T2` :  */

GRANT SELECT, INSERT, DELETE, REFERENCES, UPDATE ON T2 TO SYSDBA WITH GRANT OPTION;

If I use a TZQuery with the following query:

Code: Select all

SELECT
  T1."Same_Name_Field",
  T2."Same_Name_Field"
FROM
  T1 INNER JOIN
  T2 ON (T1.T1_ID = T2.ID)

in the field editor the fields Same_Name_Field and Same_Name_Field_1 are, erroneously, both TDateField type.
The result does not change if I use the following query as you suggested

Code: Select all

SELECT
  T1."Same_Name_Field" AS T1_Same,
  T2."Same_Name_Field" AS T2_Same
FROM
  T1 INNER JOIN
  T2 ON (T1.T1_ID = T2.ID)

If, however, query is:

Code: Select all

SELECT
  T2."Same_Name_Field",
  T1."Same_Name_Field"
FROM
  T1 INNER JOIN
  T2 ON (T1.T1_ID = T2.ID)

in the field editor, correctly, Same_Name_Field is TStringField type and Same_Name_Field_1 is TDateField type.

Attached is the project that demonstrates what has been stated.
I use:
Windows 10
Firebird 2.5
Code Typhon 6.30 32 bit
ZeosLib zeoslib-code-0-4080-trunk

[Fr0sT]

OK, I'll have a look.
---
Reproduced the issue on XE2.

[Fr0sT]

As far as I see the root of the issue are quoted field names (I told they're evil!). FB query for table metadata executed in TZInterbase6DatabaseMetadata.UncachedGetColumns returns these names unquoted so comparations with quoted name in TZAbstractResultSetMetadata.ReadColumnByName all fail (you can check this yourself with query `select rdb$field_name from RDB$RELATION_FIELDS`). So column info is returned half-cleared and then Chaos arises.
Currently I've no idea how to get quoted field name from DB metadata though it is definitely possible somehow. If you manage to find the proper option I'll try to include it but honestly I'd recommend to get rid of quoted field names.

[marsupilami]

Hello Fr0st,

UncachedGetColumns should be supplied with column names in the correct case - upper case if field names are unquoted. So if the case is not correct the caller should be corrected. I hope, this somehow helps you.

With best regards,

Jan

[Fr0sT]

Hello Jan,
no it's not the case. Everything is called correctly but then the field name to find ( '"SomeField"' ) is compared with string value from metadata query which not includes quotes ( 'SomeField' ) and of course comparison fails.
In fact I did some tests and I suspect there's no such thing as quoted names in the metadata. Client software just seems to check field names against non-identifier or lowercased letters. So for me the best solution seems to check if input field name is quoted (luckily there's IZIdentifierConvertor.IsQuoted) and if yes, unquote it with IZIdentifierConvertor.ExtractQuote and compare with metadata only case-insensibly.
This seems quite easy to implement but it will require changes in generic TZAbstractResultSetMetadata.ReadColumnByName thus touching all DBC's. IDK if is acceptable or even correct for other drivers.

[marsupilami]

Hello Fr0st,

I had a talk with EgonHugeist yesterday. From an API perspective we agree that usually the identifier patterns or identifierrs that go into GetColumns and the like should already have the correct case and should not be quoted. So the right way to go would be to modify all callers to not use quoted identifiers.

But since all drivers would be affected we think that we should use a split aproach:

Since Zeos 7.2 is in Beta state and external programmers might rely on Zeos to work the way it works currently, we think that in Zeos 7.2 the Firebird driver should be made to accept quoted identifiers and remove the quotes if necessary. This should reduce work on Zeos 7.2 and minimize compatibility problems.

For Zeos 7.3 we think that we should go the other way around: Zeos should not use quoted identifiers internally when calling GetColumns and functions like that. If errors arise because of this, these should be fixed - eitehr in the drivers if they require quotes to work correctly or in the upper layers of Zeos if they use quotes.

What do you think?

[Fr0sT]

Hello Jan,

we think that in Zeos 7.2 the Firebird driver should be made to accept quoted identifiers and remove the quotes if necessary

The problem is that this stuff has no relations to FB:
ZDbcResultSetMetadata.TZAbstractResultSetMetadata.LoadColumn
ZDbcResultSetMetadata.TZAbstractResultSetMetadata.LoadColumns
ZDbcResultSetMetadata.TZAbstractResultSetMetadata.IsReadOnly
ZAbstractRODataset.TZAbstractRODataset.InternalInitFieldDefs
ZAbstractRODataset.TZAbstractRODataset.InternalOpen
TDataSet.DoInternalOpen
TDataSet.Open

Alternatively the changes must be right where a query is analyzed but it's again a generic method: TZGenericStatementAnalyser.FillFieldRefs.

So I see only 3 options:
1) Unquote field names in TZGenericStatementAnalyser.FillFieldRefs - global change for all drivers; the most early processing and does what you plan for 7.3. But: SelectSchema doesn't link to IdentifierConvertor so unquoting will be the same for all drivers or additional reference to convertor object is required.
2) Unquote field names in TZSelectSchema.ConvertIdentifiers - global change for all drivers; the most logical solution as there is already quote processing stuff. But: influences all drivers with possible breaking changes.
3) Somehow add pre- or postprocessing to FB only. The most awkward option IMHO. But the least intrusive/breaking.

[marsupilami]

Hello Fr0st,

1) Unquote field names in TZGenericStatementAnalyser.FillFieldRefs - global change for all drivers; the most early processing and does what you plan for 7.3. But: SelectSchema doesn't link to IdentifierConvertor so unquoting will be the same for all drivers or additional reference to convertor object is required.
2) Unquote field names in TZSelectSchema.ConvertIdentifiers - global change for all drivers; the most logical solution as there is already quote processing stuff. But: influences all drivers with possible breaking changes.

Basically these two options seem to cover what we suggest for Zeos 7.3 - remove all quoting stuff. Maybe add exceptions to GetColumns and others so we (and users that use the DBC Layer directly) get notified if some caller uses the old calling convention with quotes.

3) Somehow add pre- or postprocessing to FB only. The most awkward option IMHO. But the least intrusive/breaking.

This is what we propose for Firebird in Zeos 7.2. If I understood you correctly, the problem is, that a caller calls GetColumns with the tablename quoted -> "TableName" instead of TableName. So for Zeos 7.2 the Firebird driver should remove the quotes if they are supplied before the value is used for any comparisons. This should solver the problem there too?

What do you think?