The following is against Teiid 8.7

I have the following test case:

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 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

union all

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

where ssid='d91a';

END

It fails with a parsing error:

Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "option makedep table_spaces_v5 [*]union[*] all select" at line 15, column 1.

Was expecting: "makedep" | "makenotdep" | "nocache" | "," | "(" | ")"

Logically, it seems like this ought to work but I can see from the BNF how this fails parsing. 

The following syntax is valid:

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

But fails with the interesting error TEIID30226 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30226 Temporary table "table_spaces_v6" does not exist.  Using the /*+ MAKEDEP */ form yields the same, interesting error.