[TRAFODION-1722] "_MD_".KEYS_VIEW does not show descending key columns as such

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.2-incubating
• Component/s: sql-cmu
• Environment: any
• Assignee: Anoop Sharma
• Reporter: Hans Zeller
• Created: 23/Dec/15 01:37
• Updated: 06/Feb/16 21:39

Description

The KEYS_VIEW metadata view has a column "ORDERING", which should show descending columns, e.g. in the primary key. However, it doesn't, while the underlying KEYS table shows descending columns. I think the reason is that the view only selects rows from the keys table that are associated with constraints, while the ascending/descending information is associated with the table object. Here is a test case where the view shows no descending columns, while the underlying KEYS table does:

create table tab_with_desc_col(a int not null,
                               b int not null,
                               primary key (a desc, b));

select ordinal_position, column_name, column_number, ordering
from "_MD_".keys_view
where table_name = 'TAB_WITH_DESC_COL';

select keyseq_number, column_name, column_number, ordering
from "_MD_".objects o join "_MD_".keys k on o.object_uid = k.object_uid
where o.object_name = 'TAB_WITH_DESC_COL'
order by 1;