[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:comment-tabpanel&focusedCommentId=13382350#comment-13382350 ] 

Steven Hawkins commented on TEIID-4822:
---------------------------------------

I hadn't seen your follow up comment initially, so I may not have understood you fully.

What I was describing is that in a situation where you have:

select ROW_NUMBER() OVER (PARTITION BY col ORDER by col1) from tbl where col2 = val

The predicate col2 = val is only pushed below the select clause when the partitioning contains col2 as a column.

If your issue is resolved with a makedep, then that is something different.  Predicates in the on clause are eligible for pushing depending upon the join type.  In the case of an inner join, they can always be pushed if they only reference a single side of the join.

> 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.2.3#72005)