[JBoss JIRA] (TEIID-4823) joining 2 procedures results in QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4823?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-4823:
-------------------------------------
Added plan1 for the first query and plan2 for the second query (which results in the error).
> joining 2 procedures results in QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria
> --------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-4823
> URL: https://issues.jboss.org/browse/TEIID-4823
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.1.4
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: plan1.txt, plan2.txt
>
>
> I have a query like this where I join a regular table with a virtual procedure.
>
> {code:sql}
> select gd.patientid, ip.production
> from prod_P_GeneralData gd
> join pr_patient_in_production ip on ip.patientid = gd.patientid
> where gd.status = 1;
> {code}
>
> This works fine and returns 58 rows with no null values. The pr_patient_in_production procedure has 1 input parameter called patientid (integer) and one output parameter called production (boolean)
>
> Adding a second procedure call results in an excepion:
>
> {code:sql}
> select gd.patientid, lrs.datetime, lrs.varvalue as phtype
> from prod_P_GeneralData gd
> join pr_patient_in_production ip on ip.patientid = gd.patientid
> join pr_retrieve_labres lrs on
> lrs.patientid_in = gd.patientid
> and lrs.production = ip.production
> and lrs.resultgroup_in = 'pH-type'
> and lrs.varvalue = 1
> where gd.status = 1
> order by lrs.datetime, lrs.entertime desc
> {code}
>
> This pr_retrieve_labres procedure has patientid_in (integer), production (boolean) and resultgroup_in(string) as input parameters and also some output parameters.
>
> The exception is: Error: TEIID30275 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria, Access patterns: [[Access Pattern: Unsatisfied [ip.patientid] History [[ip.patientid]]], [Access Pattern: Unsatisfied [lrs.production, lrs.patientid_in] History [[lrs.patientid_in, lrs.production, lrs.resultgroup_in]]]]
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years
[JBoss JIRA] (TEIID-4823) joining 2 procedures results in QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4823?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-4823:
--------------------------------
Attachment: plan1.txt
plan2.txt
> joining 2 procedures results in QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria
> --------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-4823
> URL: https://issues.jboss.org/browse/TEIID-4823
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.1.4
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: plan1.txt, plan2.txt
>
>
> I have a query like this where I join a regular table with a virtual procedure.
>
> {code:sql}
> select gd.patientid, ip.production
> from prod_P_GeneralData gd
> join pr_patient_in_production ip on ip.patientid = gd.patientid
> where gd.status = 1;
> {code}
>
> This works fine and returns 58 rows with no null values. The pr_patient_in_production procedure has 1 input parameter called patientid (integer) and one output parameter called production (boolean)
>
> Adding a second procedure call results in an excepion:
>
> {code:sql}
> select gd.patientid, lrs.datetime, lrs.varvalue as phtype
> from prod_P_GeneralData gd
> join pr_patient_in_production ip on ip.patientid = gd.patientid
> join pr_retrieve_labres lrs on
> lrs.patientid_in = gd.patientid
> and lrs.production = ip.production
> and lrs.resultgroup_in = 'pH-type'
> and lrs.varvalue = 1
> where gd.status = 1
> order by lrs.datetime, lrs.entertime desc
> {code}
>
> This pr_retrieve_labres procedure has patientid_in (integer), production (boolean) and resultgroup_in(string) as input parameters and also some output parameters.
>
> The exception is: Error: TEIID30275 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria, Access patterns: [[Access Pattern: Unsatisfied [ip.patientid] History [[ip.patientid]]], [Access Pattern: Unsatisfied [lrs.production, lrs.patientid_in] History [[lrs.patientid_in, lrs.production, lrs.resultgroup_in]]]]
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years
[JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
by Steven Hawkins (JIRA)
[ 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)
9 years
[JBoss JIRA] (TEIID-4823) joining 2 procedures results in QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria
by Bram Gadeyne (JIRA)
Bram Gadeyne created TEIID-4823:
-----------------------------------
Summary: joining 2 procedures results in QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria
Key: TEIID-4823
URL: https://issues.jboss.org/browse/TEIID-4823
Project: Teiid
Issue Type: Bug
Affects Versions: 9.1.4
Reporter: Bram Gadeyne
Assignee: Steven Hawkins
I have a query like this where I join a regular table with a virtual procedure.
{code:sql}
select gd.patientid, ip.production
from prod_P_GeneralData gd
join pr_patient_in_production ip on ip.patientid = gd.patientid
where gd.status = 1;
{code}
This works fine and returns 58 rows with no null values. The pr_patient_in_production procedure has 1 input parameter called patientid (integer) and one output parameter called production (boolean)
Adding a second procedure call results in an excepion:
{code:sql}
select gd.patientid, lrs.datetime, lrs.varvalue as phtype
from prod_P_GeneralData gd
join pr_patient_in_production ip on ip.patientid = gd.patientid
join pr_retrieve_labres lrs on
lrs.patientid_in = gd.patientid
and lrs.production = ip.production
and lrs.resultgroup_in = 'pH-type'
and lrs.varvalue = 1
where gd.status = 1
order by lrs.datetime, lrs.entertime desc
{code}
This pr_retrieve_labres procedure has patientid_in (integer), production (boolean) and resultgroup_in(string) as input parameters and also some output parameters.
The exception is: Error: TEIID30275 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30275 Join region with unsatisfied access patterns cannot be satisfied by the join criteria, Access patterns: [[Access Pattern: Unsatisfied [ip.patientid] History [[ip.patientid]]], [Access Pattern: Unsatisfied [lrs.production, lrs.patientid_in] History [[lrs.patientid_in, lrs.production, lrs.resultgroup_in]]]]
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years
[JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-4822:
-------------------------------------
Hi,
Is that a part of the partitioning or the whole partitioning?
> 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)
9 years
[JBoss JIRA] (TEIID-4815) How could I debug JDV memory allocation properly
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4815?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4815:
---------------------------------------
There's nothing more that I'm aware of based upon other issues.
I'll update the oracle translator documentation to refer to this issue and the oracle whitepaper - http://www.oracle.com/technetwork/topics/memory.pdf
> How could I debug JDV memory allocation properly
> ------------------------------------------------
>
> Key: TEIID-4815
> URL: https://issues.jboss.org/browse/TEIID-4815
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Rafael Coutinho
> Assignee: Steven Hawkins
> Attachments: Screenshot from 2017-03-17 17-54-24.png, Screenshot from 2017-03-17 18-14-50.png
>
>
> We hare having trouble with memory allocation on our JDV server (using Red Hat JBoss Data Virtualization - Version 6.3.0) for some reason memory gets allocated but never released.
> For simple queries memory increases just a little, however when we make complex joins etc, we are seeing scenarios with 20GB+ allocated heap.
> I wonder if there is any inspection tool for debugging what is consuming it in JDV.
> Our main datasource is an Oracle DS, but we do have a MariaDB being used too.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years
[JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4822.
-----------------------------------
Resolution: Rejected
The value cannot be pushed through the windowing projection unless it matches the partitioning - as it would change the results if it were applied first.
> 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)
9 years
[JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-4822:
-------------------------------------
Adding /*+ MAKEDEP */ resolves this issue
> 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)
9 years
[JBoss JIRA] (TEIID-4603) Insert issue when cache contains multiple pojo's with the same key value
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4603?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4603:
------------------------------------
This is really really a documentation issue, as infinispan does not recommend the usage scenario. The documentation should be changed to state that the usage of a cache should not done.
> Insert issue when cache contains multiple pojo's with the same key value
> ------------------------------------------------------------------------
>
> Key: TEIID-4603
> URL: https://issues.jboss.org/browse/TEIID-4603
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 9.2
> Reporter: Van Halbert
> Assignee: Van Halbert
> Priority: Blocker
> Fix For: 9.2, 8.12.x-6.4
>
>
> There is an INSERT issue when there are multiple pojo's stored in the same cache and the keys' overlap (meaning the different types of pojo's have the same key).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years