Firebird 1.5 : Duplicate columns in a table

Question

I noticed that Firebird creates duplicate columns for a single table, thus incorrect indices are being used in the query which cause query to be slow. Please example below.

I have 2 tables with the same columns and indices, but when checking the table structure, one table shows duplicate columns

Table A : Name VARCHAR(30)
          Age  INTEGER
          BIRTH_DATE TIMESTAMP

          Indices : Name, Birth_date (Asc), Birth_date(Desc)

Table B : Name VARCHAR(30)
          Age  INTEGER
          BIRTH_DATE TIMESTAMP   
          Name VARCHAR(30)
          Age  INTEGER
          BIRTH_DATE TIMESTAMP   

          Indices : Name, Birth_date (Asc), Birth_date(Desc)

When joining the table with Table C and order by Birth_date, Table A is using the Birth_date index Ordered, but Table B is not.

Please help! what is the cause behind this? Thank you.

Answer 1

I just had a problem where a duplicate column had been allowed to be created. This request

SELECT a.RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS a
WHERE a.RDB$FIELD_NAME like '%COLUMN_NAME%'

was showing two COLUMN_NAME lines. By copy pasting the fields elsewhere it became apparent that one column had trailing whitespace, while the other had a carriage return + line feed (CRLF) and then trailing whitespace.

The FlameRobin wizard was used to create the column. My take on it is a copy-paste was used and a CRLF was inserted. Excel and other softwares can do that to you. FlameRobin, FlameRobin's driver and FireBird should each guard against that, though.

We dropped the offending column by crafting some DDL which had the offending CRLF in the column name.