[teiid-issues] [JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-4822.
---------------------------------


> predicate in on clause is not pushed down to view's underlying select statement
> -------------------------------------------------------------------------------
>
>                 Key: TEIID-4822
>                 URL: https://issues.jboss.org/browse/TEIID-4822
>             Project: Teiid
>          Issue Type: Enhancement
>    Affects Versions: 9.1.4
>            Reporter: Bram Gadeyne
>            Assignee: Steven Hawkins
>
> Hi,
> I have a view called tv_retrieve_labres that contains the following select statement
> {code:sql}
> SELECT
> 		f.PatientID AS admissionid, f.Labogroep, f.SampleTime, f.EnterTime, f.varvalue, f.StringValue
> FROM (
> 	SELECT v.PatientID, v.Labogroep, v.SampleTime, v.EnterTime, v.varvalue, v.StringValue, 
> 		ROW_NUMBER() OVER (PARTITION BY v.PatientID, v.Labogroep, v.SampleTime ORDER BY v.EnterTime DESC) AS rang 
> 	FROM (
> 		SELECT lcv.PatientID, lm.Labogroep, lcv.SampleTime, lcv.EnterTime, lcv.varvalue, lcv.StringValue, 
> 			ROW_NUMBER() OVER (PARTITION BY lcv.PatientID, lcv.variableid_oud, lcv.ResultID ORDER BY lcv.ResultNo DESC) AS rang 
> 		FROM izisprod.prod_U_P_LabMapping AS lm 
> 		INNER JOIN /*+ MAKEDEP */ izisprod.prod_U_PV_LabCorrectValues AS lcv ON 
> 			lcv.variableid_oud = lm.VariableID AND 
> 			bitand(convert(lcv.Status, integer), 8) = 8 AND 
> 			bitand(convert(lcv.Status, integer), 2) <> 2 
> 		UNION 
> 		SELECT lcv.PatientID, lm.Labogroep, lcv.SampleTime, lcv.EnterTime, lcv.varvalue, lcv.StringValue, 
> 			ROW_NUMBER() OVER (PARTITION BY lcv.PatientID, lcv.variableid_oud, lcv.ResultID ORDER BY lcv.ResultNo DESC) AS rang 
> 		FROM iziswh.wh_U_P_LabMapping AS lm 
> 		INNER JOIN /*+ MAKEDEP */ iziswh.wh_U_PV_LabCorrectValues AS lcv ON 
> 			lcv.variableid_oud = lm.VariableID AND 
> 		bitand(convert(lcv.Status, integer), 8) = 8 AND 
> 		bitand(convert(lcv.Status, integer), 2) <> 2
> 	) AS v 
> 	WHERE v.rang = 1
> ) AS f
> WHERE
> f.rang = 1
> {code}
> When I execute the following select statement the value for lrs.Labogroep is not pushed down.
> {code:sql}
> select gd.patientid, lrs.varvalue, lrs.entertime, lrs.sampletime
> from prod_P_GeneralData gd
> join tv_retrieve_labres lrs on 
> 	lrs.admissionid = gd.PatientID
> 	and lrs.Labogroep = 'pH-type'
> 	and lrs.varvalue = 1
> where gd.status = 1 
> {code}
> Should this normally be working? I'm wondering if it doesn't work because of the union statement in the view.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)