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

["Hans Zeller (JIRA)" <ji...@apache.org>]

Hans Zeller created TRAFODION-1722:
--------------------------------------

             Summary: "_MD_".KEYS_VIEW does not show descending key columns as such
                 Key: TRAFODION-1722
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1722
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmu
    Affects Versions: 1.2-incubating
         Environment: any
            Reporter: Hans Zeller


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:

{noformat}
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;
{noformat}




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)