[teiid-issues] [JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
[
https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin...
]
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)