[teiid-issues] [JBoss JIRA] (TEIID-4619) left join returns wrong results

[Bram Gadeyne (JIRA)]

Bram Gadeyne created TEIID-4619:
-----------------------------------

             Summary: 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 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.



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)