2 Replies Latest reply on Jul 25, 2014 7:36 PM by Mark Addleman

    Should this be valid?

    Mark Addleman Master

      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.

        • 1. Re: Should this be valid?
          Steven Hawkins Master

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

           

          It's not so much an issue for the initial branches, but you would have an ambiguity with the last branch - do you associate things like limit, options etc. with the last union or with the over all union?  So to clarify this the definition of union doesn't allow the branches to contain those common end clauses unless nested in parens.

           

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


          Yes, you wouldn't expect an exception like that.  Can you log the details of this as a JIRA - is this an anonymous block, a procedure definition body, etc.

          • 2. Re: Should this be valid?
            Mark Addleman Master

            > So to clarify this the definition of union doesn't allow the branches to contain those common end clauses unless nested in parens.

            Makes sense. 

             

            > Can you log the details of this as a JIRA - is this an anonymous block, a procedure definition body, etc.

            TEIID-3052