[teiid-issues] [JBoss JIRA] (TEIID-5257) Sysadmin Usage table doesn't track column-level usage by a virtual procedure

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5257?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5257:
----------------------------------
     Issue Type: Enhancement  (was: Bug)
    Component/s: Query Engine
                     (was: Server)


Marking as an enhancement rather than a bug.  When column level tracking was added, it was added only for views.  Procedures can be more difficult to track usage as there can be multiple return statements, the use of temporary tables or variables, etc., so this was left unimplemented initially.

> Sysadmin Usage table doesn't track column-level usage by a virtual procedure
> ----------------------------------------------------------------------------
>
>                 Key: TEIID-5257
>                 URL: https://issues.jboss.org/browse/TEIID-5257
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Query Engine
>    Affects Versions: 8.12.x-6.4
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>
> There are not column-level dependencies in relation 'Virtual procedure column -> View/table column'.
> For this vdb:
> {code:xml|title=vdb}
>     <model name="insideVirtualModel" type="VIRTUAL">
>         <metadata type="DDL">
>             <![CDATA[
>                 CREATE VIEW v1 (v1col string) AS SELECT 'a' UNION ALL SELECT 'b';
>                 CREATE VIEW v2 (v2col string) AS SELECT v1col||'b' FROM v1;
>                 CREATE VIRTUAL PROCEDURE p1() RETURNS (p1col string) AS
>                 BEGIN
>                     SELECT v2col FROM v2;
>                 END
>                 CREATE VIEW v7 (v7col string) AS SELECT p1.p1col FROM (CALL p1())AS p1;
>                 ]]>
>         </metadata>
>     </model>
>     <model name="dependentOnPhysicalModel" type="VIRTUAL">
>         <metadata type="DDL">
>             <![CDATA[
>                 CREATE VIEW v3 (v3col string) AS SELECT t1col||'b' FROM t1;
>                 CREATE VIEW v4 (v4col string) AS SELECT p.p2col FROM (CALL p2(1)) AS p;
>             ]]>
>         </metadata>
>     </model>
> {code}
> Following query:
> {code:sql|title=SYSADMIN.Usage query}
> SELECT * FROM SYSADMIN.Usage WHERE VDBName='vdbname' AND SchemaName='insideVirtualModel' AND Name='p1' AND object_type='Column' AND Uses_object_type='Column'
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)