[JBoss JIRA] (TEIID-4620) MongoDB: Model "_id" during metadata import as string data type
by Ramesh Reddy (JIRA)
Ramesh Reddy created TEIID-4620:
-----------------------------------
Summary: MongoDB: Model "_id" during metadata import as string data type
Key: TEIID-4620
URL: https://issues.jboss.org/browse/TEIID-4620
Project: Teiid
Issue Type: Enhancement
Components: Misc. Connectors
Affects Versions: 8.7
Reporter: Ramesh Reddy
Assignee: Ramesh Reddy
Fix For: 9.2
Currently when metadata is imported through NATIVE metadata processor, the "_id" column of the MongoDB collection is represented as "Object" but the value is always handled as "string". Convert this to a string type such that is column then can be used in criteria columns.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years
[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:
--------------------------------
Description:
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).
was:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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).
> 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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.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)
8 years
[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:
--------------------------------
Description:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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).
was:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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 desired rows (see enclosed_queryplan.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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this setting there are 66290 results. However if I 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)
8 years
[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: enclosed_queryplan.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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this setting there are 66290 results. However if I 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 desired rows (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years
[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:
--------------------------------
Description:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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 desired rows (see enclosed_queryplan.txt).
was:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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.
> 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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this setting there are 66290 results. However if I 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 desired rows (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years
[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:
--------------------------------
Description:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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.
was:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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.
> 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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this setting there are 66290 results. However if I 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.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years
[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: correct_result.txt
wrong_result.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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this setting there are 66290 results. However if I 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.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years
[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:
--------------------------------
Description:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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.
was:
I have the following situation.
I have a temporary table #tmp_admissions that contains 8047 rows.
In this setting there are 66290 results. However if I 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 880 rows.
{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.
> 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
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this setting there are 66290 results. However if I 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.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years