DBC.COLUMNS ... contains duplicate rows for a table

General

DBC.COLUMNS ... contains duplicate rows for a table

We have a strange behavior we are noticing with the DBC.COLUMNS (view).

1.

There are some (certainly not all) tables with duplicate columns for the same table.  For example a table with CK_SUM defined as a column, it is showing twice.  One with the ~ as the default value, the other with null defined as default value. 

CK_SUM  X(32)  CF  32  '~                               '          Y             5/8/2014 10:42:21

CK_SUM  X(32)  CF  32  ?                                           Y             11/24/2014 04:03:12

However, when we do a SHOW TABLE on the table, we are not seeing the 11/24 definition column, we see the 5/8 definition.

CK_SUM CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '~                               ')

I would have expected to see … just one row per column per table.

Recently we upgraded to TD14.  I don’t think this problem existed prior to the upgrade, but there is no telling for sure.

2.

There are times when we query the DBC.COLUMNS table and a column may appear in the list of columns for a table and yet, when that table is queried, the column does not exist (and shouldn’t exists).  It isn’t always the same column and it isn’t always the same table.  Inconsistent results.

Any thoughts and/or insights would be appreciated.

3 REPLIES
Teradata Employee

Re: DBC.COLUMNS ... contains duplicate rows for a table

Is it possible that there are 2 tables with the same name in different databases? Also, always access the modern view: DBC.COLUMNSV.

N/A

Re: DBC.COLUMNS ... contains duplicate rows for a table

Open an incident with TD - saw this also in a production system after an upgrade to 14.10

Re: DBC.COLUMNS ... contains duplicate rows for a table

Thank you for the suggestions.  DBC.COLUMNSV does not show the duplicate values.