Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3052

Error on using MAKEDEP hint within union

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 8.9
    • 8.7
    • Query Engine
    • None
    • Workaround Exists
    • Hide

      Use an exception handling block to force the full execution within the procedure scope:

      BEGIN
      ...
      EXCEPTION e
      RAISE e.exception;
      END

      Show
      Use an exception handling block to force the full execution within the procedure scope: BEGIN ... EXCEPTION e RAISE e.exception; END

    Description

      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

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            markaddleman_jira Mark Addleman (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: