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

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

     [ https://issues.apache.org/jira/browse/TRAFODION-1722?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Hans Zeller updated TRAFODION-1722:
-----------------------------------
    Priority: Minor  (was: Major)

> "_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
>            Priority: Minor
>
> 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)