[teiid-issues] [JBoss JIRA] (TEIID-3052) Error on using MAKEDEP hint within union

[Steven Hawkins (JIRA)]

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

Steven Hawkins resolved TEIID-3052.
-----------------------------------

    Fix Version/s: 8.9
       Resolution: Done


Added logic to pre-process the results, which is already done when catching exceptions or in transactional scenarios.  Also logged TEIID-3053 to improve the performance of the fix.

> Error on using MAKEDEP hint within union
> ----------------------------------------
>
>                 Key: TEIID-3052
>                 URL: https://issues.jboss.org/browse/TEIID-3052
>             Project: Teiid
>          Issue Type: Bug
>      Security Level: Public(Everyone can see) 
>          Components: Query Engine
>    Affects Versions: 8.7
>            Reporter: Mark Addleman
>            Assignee: Steven Hawkins
>             Fix For: 8.9
>
>
> The following anon proc block fails with error TEIID30226 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30226 Temporary table "table_spaces_v6" does not exist
> BEGIN
> create local temporary table ssid_version (sysplex varchar, lpar varchar, ssid varchar, version varchar);
> insert into ssid_version(sysplex, lpar, ssid, version) values ('plex1', 'ca11', 'd91a', 'v5');
> insert into ssid_version(sysplex, lpar, ssid, version) values ('plex1', 'ca11', 'd91b', 'v6');
>  
>  
> create local temporary table table_spaces_v5 (sysplex varchar, lpar varchar, ssid varchar, table_space_id varchar);
> insert into table_spaces_v5 (sysplex, lpar, ssid, table_space_id) values ('plex1', 'ca11', 'd91a', 'ts1');
>  
>  
> create local temporary table table_spaces_v6 (sysplex varchar, lpar varchar, ssid varchar, table_space_id varchar);
> insert into table_spaces_v6 (sysplex, lpar, ssid, table_space_id) values ('plex1', 'ca11', 'd91b', 'ts2');
>  
>  
> select table_space_id from
> (
> select * from (select v.sysplex, v.lpar, v.ssid, t.table_space_id from ssid_version v join table_spaces_v5 t on t.sysplex=v.sysplex and t.lpar=v.lpar and t.ssid=v.ssid option makedep table_spaces_v5) t
> union all
> select * from (select v.sysplex, v.lpar, v.ssid, t.table_space_id from ssid_version v join table_spaces_v6 t on t.sysplex=v.sysplex and t.lpar=v.lpar and t.ssid=v.ssid option makedep table_spaces_v6) t
> ) t
> where ssid='d91a';
> END
> It also fails when the /*+ MAKEDEP */ form is used instead of "option makedep" 
> I don't believe there should be any failure



--
This message was sent by Atlassian JIRA
(v6.2.6#6264)