Hi all,

I am facing a problem with a join in Reusable and Non-Reusable executions when one of the non-reusable datasource has data and the other doesn't have the data.

The problem I am facing is that the Non-Reusable executions are being repeated continuously and the Reusable execution is not getting executed at all.

The following is the SQL and please find the attached query plan and test case to reproduce this problem.

SELECT LATEST_HEALTH_STATE.*

FROM

  (SELECT LATEST_HEALTH_STATE.*

   FROM

     (SELECT 'healthstatestream.LATEST_HEALTH_STATES' AS "__objecttype__",

             name,

             rgb,

             severity,

             objecttype,

             worst_severity_by_policy.title

      FROM healthstatestream.health_states

      INNER JOIN

        (SELECT MIN(healthstatefkey) AS worst_health_state,

                Object_type(objfkeyxml) AS objectType,

                metadata.propertyvalue AS title

         FROM (healthstatestream.latest_health_states

               INNER JOIN

                 (SELECT '<demo_policy_metadata.op_demo_policy_type_1 policyid=''policy name 1'' policyname=''policy 1001'' systemid=''sysid 1''></demo_policy_metadata.op_demo_policy_type_1>' AS POLICYFKEYXML) AS policies_configured_in_traffic_light ON latest_health_states.policyfkeyxml = policies_configured_in_traffic_light.policyfkeyxml)

         INNER JOIN chorus_metadata.property AS metadata ON Object_type(objfkeyxml) = metadata.objectname

         AND metadata.propertyname = 'VisibleName'

         AND metadata.subtype = 'Object'

         GROUP BY Object_type(objfkeyxml),

                  metadata.propertyvalue) AS worst_severity_by_policy ON worst_severity_by_policy.worst_health_state = health_states.severity) AS LATEST_HEALTH_STATE,

        (EXEC chorus_time.timetable(30000)) AS chorus_refresh_expression) AS LATEST_HEALTH_STATE

Note: We tested using teiid 8.3.0.Beta2.

Please suggest.

Thanks,

Rakesh.