[JBoss JIRA] (TEIID-4661) Subsequent queries hang after materialized view TTL expires.
by Colin Mondesir (JIRA)
Colin Mondesir created TEIID-4661:
-------------------------------------
Summary: Subsequent queries hang after materialized view TTL expires.
Key: TEIID-4661
URL: https://issues.jboss.org/browse/TEIID-4661
Project: Teiid
Issue Type: Bug
Affects Versions: 8.12.x
Reporter: Colin Mondesir
Assignee: Steven Hawkins
In a clustered configuration initially the materialized view is cached correctly but when the TTL expires (10 minutes) and the query is run again the state of the view changes to LOADING and never reverts to "LOADED", so the next query hangs.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4619) left join returns wrong results
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4619?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4619:
---------------------------------------
A quick investigation would be to turn off order by support on the cosara2 to see if that has an effect. There's more logic to prevent that type of issue in 9.1 with TEIID-4129
In more detail, based upon query1 where you are seeing an appropriate number of outer rows, but the inner side of the left outer join is not populated we should validate that the inner side is returning the expected data. The source query is:
SELECT g_0.culturenumber AS c_0, g_0.specimennumber AS c_1, g_0.sampleinsertts AS c_2, g_0.culturename AS c_3, g_0.quotation AS c_4 FROM cosara2.cos2_lab_culture AS g_0 ORDER BY c_1, c_0, c_2
If you run that directly against the source does that query contain return the lab culture entries you expect, and is the ordering (based upon the string field specimennumber) compatible with UCS-2 / ASCII ordering?
> left join returns wrong results
> -------------------------------
>
> Key: TEIID-4619
> URL: https://issues.jboss.org/browse/TEIID-4619
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.4, 9.0.5
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, query1_enclosed_plan.txt, query1_plan.txt, query2_plan.txt, teiid_reduced_case.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this first query there are 66290 results. However if I only look at the lines for infectionid 880 then there are only 16 lines.
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from #tmp_admissions adm
> join cos2_links l on l.admissionid = cast(adm.patientid as string)
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> {code}
> This query does almost the same but returns 30 rows (and is correct).
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from cos2_links l
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> where l.infectionid = 880
> {code}
> cos2_link_culture contains 2 rows for this infectionid. The left join statements should result in 15 rows for both rows. However the left join results in the first query for the first row are null and to my understanding ignored. I'll attach the query plans for both queries.
> I should note that there is a one to many relation between infection and admission so therefore infectionid is for the same admission.
> Strangely enough if you enclode the first query in a group by query and count the rows it does indeed return 2 times 15 for the specific groups (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4625) Consider consolidating/managing materialization status tables
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4625?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4625:
-------------------------------------
[~kylin] can also link your previous findings on improvements this can bring here too?
Ideally I would like to evolve this little bigger all the views etc. But I would like to wait until other stuff with eager/lazy loading is more underway.
> Consider consolidating/managing materialization status tables
> -------------------------------------------------------------
>
> Key: TEIID-4625
> URL: https://issues.jboss.org/browse/TEIID-4625
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Reporter: Steven Hawkins
> Assignee: Kylin Soong
> Fix For: 9.2
>
>
> To enhance usability we should consider consolidating/managing status tables. Management would include auto creation similar (which could just leverage hibernate similar to the database log logic, regardless of whether we use hibernate to read/write).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4625) Consider consolidating/managing materialization status tables
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4625?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4625:
---------------------------------------
> Does the status table for both internal and external or only external?
We're only considering external initially.
> If use hibernate it's easy to auto-create tables, but the read write still in ddl procedure or in Java code?
We may use hibernate for just the auto-create part and not for read/write - especially from DDL.
> Consider consolidating/managing materialization status tables
> -------------------------------------------------------------
>
> Key: TEIID-4625
> URL: https://issues.jboss.org/browse/TEIID-4625
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Reporter: Steven Hawkins
> Assignee: Kylin Soong
> Fix For: 9.2
>
>
> To enhance usability we should consider consolidating/managing status tables. Management would include auto creation similar (which could just leverage hibernate similar to the database log logic, regardless of whether we use hibernate to read/write).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4658) Data scramble feature
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4658?page=com.atlassian.jira.plugin... ]
Steven Hawkins reassigned TEIID-4658:
-------------------------------------
Component/s: Query Engine
Fix Version/s: Open To Community
Assignee: (was: Steven Hawkins)
I'm not sure we have quite enough here to make a good proposal of what built-in support Teiid or tooling should provide. It could be that the process of anonymozing is something that is more of an implementation pattern, rather than a set piece of server functionality. Let's continue the discussion on this issue.
> Data scramble feature
> ---------------------
>
> Key: TEIID-4658
> URL: https://issues.jboss.org/browse/TEIID-4658
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Rodrigo Silva
> Fix For: Open To Community
>
>
> I have a customer asking for a feature to do data scramble.
> The need is change the production data to be used in another environments without the user know the original data.
> e.g:
> Give the person table:
> {code:java}
> CREATE TABLE person (
> id INT PRIMARY KEY,
> name VARCHAR(256) NOT NULL,
> age INT,
> height INT,
> weight DOUBLE
> );
> {code}
> {code:java}
> SELECT * FROM TABLE PERSON WHERE ID=1;
> {code}
> Result:
> The *name* is from row with *ID#15*, the *age* is from row with *ID#3*, the *height* is from row with *ID#5*.
> On this way the customer can work with consistent and natural data, but unreal.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4619) left join returns wrong results
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4619?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-4619:
--------------------------------
Attachment: teiid_reduced_case.txt
> left join returns wrong results
> -------------------------------
>
> Key: TEIID-4619
> URL: https://issues.jboss.org/browse/TEIID-4619
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.4, 9.0.5
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, query1_enclosed_plan.txt, query1_plan.txt, query2_plan.txt, teiid_reduced_case.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this first query there are 66290 results. However if I only look at the lines for infectionid 880 then there are only 16 lines.
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from #tmp_admissions adm
> join cos2_links l on l.admissionid = cast(adm.patientid as string)
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> {code}
> This query does almost the same but returns 30 rows (and is correct).
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from cos2_links l
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> where l.infectionid = 880
> {code}
> cos2_link_culture contains 2 rows for this infectionid. The left join statements should result in 15 rows for both rows. However the left join results in the first query for the first row are null and to my understanding ignored. I'll attach the query plans for both queries.
> I should note that there is a one to many relation between infection and admission so therefore infectionid is for the same admission.
> Strangely enough if you enclode the first query in a group by query and count the rows it does indeed return 2 times 15 for the specific groups (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4619) left join returns wrong results
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4619?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-4619:
-------------------------------------
Hi Steven,
Adding l.infectionid = 880 as a predicate to the join does return the correct results.
I might have a reduced example but it's not completely the same. I've created a second temporary table #tmp_adm2 that only contains 1 row (for the admission where infection 880 belongs to). I've added the columns lc.culturenr,lc.culturespecimennr,lc.culturesampleinsertts to be sure that the values for the join columns are read correctly from cos2_link_culture.
The expected returned values for these queries are now only 2 lines (1 for each lab_culture). However the fields sampleinsertts, specimennumber, culturenumber should be filled out. In query 1 I can see that for the case where earlier 15 rows were missing indeed also these columns are empty. If I change it to use #tmp_adm2 then these columns are filled out.
{code:sql}
-- query 1: columns cl.* are empty for one of the 2 culture links.
select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
cl.culturenumber, cl.culturename, cl.quotation,
lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts,
lc.culturenr,lc.culturespecimennr,lc.culturesampleinsertts
from #tmp_admissions adm
join cos2_links l on l.admissionid = cast(adm.patientid as string)
join cos2_link_culture lc on lc.linkid = l.id
left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
{code}
{code:sql}
-- query 2: columns cl.* are filled out as expected
select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
cl.culturenumber, cl.culturename, cl.quotation,
lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts,
lc.culturenr,lc.culturespecimennr,lc.culturesampleinsertts
from #tmp_adm2 adm
join cos2_links l on l.admissionid = cast(adm.patientid as string)
join cos2_link_culture lc on lc.linkid = l.id
left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
{code}
I've added a file teiid_reduces_cases.txt that contains the query plans and debug logs for both queries.
> left join returns wrong results
> -------------------------------
>
> Key: TEIID-4619
> URL: https://issues.jboss.org/browse/TEIID-4619
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.4, 9.0.5
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, query1_enclosed_plan.txt, query1_plan.txt, query2_plan.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this first query there are 66290 results. However if I only look at the lines for infectionid 880 then there are only 16 lines.
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from #tmp_admissions adm
> join cos2_links l on l.admissionid = cast(adm.patientid as string)
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> {code}
> This query does almost the same but returns 30 rows (and is correct).
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from cos2_links l
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> where l.infectionid = 880
> {code}
> cos2_link_culture contains 2 rows for this infectionid. The left join statements should result in 15 rows for both rows. However the left join results in the first query for the first row are null and to my understanding ignored. I'll attach the query plans for both queries.
> I should note that there is a one to many relation between infection and admission so therefore infectionid is for the same admission.
> Strangely enough if you enclode the first query in a group by query and count the rows it does indeed return 2 times 15 for the specific groups (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months
[JBoss JIRA] (TEIID-4641) Create upsert/delete refresh support
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4641?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4641.
---------------------------------
Resolution: Done
Labels: Beta1 (was: )
Modified load/refresh strategy to use UPSERT/DELETE rather than the INSERT/DELETE from before. The process is documented with this commit [1]. Note, that this process is much more simpler and does not require a staging table nor defining the load scripts. The previous behavior based on staging table, and load scripts still exists for backwards compatibility, it just not documented any more.
[1] https://github.com/teiid/teiid-documents/commit/c30b2ad19e2b5ed9fdc13ee39...
> Create upsert/delete refresh support
> ------------------------------------
>
> Key: TEIID-4641
> URL: https://issues.jboss.org/browse/TEIID-4641
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 9.2
>
>
> There must be an alternative to the implied default of the table rename strategy - which is problematic when a source is not transactional or even when it is - as a multi-part swap can leave the table non-existent for a brief window or the source may have issues with ddl under an xa transaction - TEIID-4579
> This is true for the non-transactional source incremental refresh as well, which is a delete then insert - it leaves the live table briefly in an inconsistent state.
> A upsert/delete approach addresses both of these concerns at the expense of performance - especially when the source does not directly support upsert as the engine must implement a compensating procedure. However now that we have pushdown support for upsert, we'll continue to add source support.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
7 years, 4 months