Join of reusable and non reusable executions
rakeshsagar Feb 20, 2013 9:07 AMHi 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.
-
Test Case.zip 44.9 KB
-
Query_Plan.txt.zip 278.7 KB