[teiid-issues] [JBoss JIRA] (TEIID-3511) usage of BITAND function in exists statement results in duplicate rows
Bram Gadeyne (JIRA)
issues at jboss.org
Fri Jun 19 05:41:02 EDT 2015
[ https://issues.jboss.org/browse/TEIID-3511?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13081442#comment-13081442 ]
Bram Gadeyne commented on TEIID-3511:
-------------------------------------
Sorry for the late reply. I'm sorry but I can not give you the data because it is medical data. I tried to reproduce this with less joins but then everything seems to work out fine. Do you have any idea what might have caused this?
I now also used a temp table joined with 2 tables and then a where clause that has an exists condition with and without a BITAND function call.
select *
from #tmp_res gd
join prod_P_Pharmas ps on ps.PatientID = gd.PatientID
join prod_S_PharmaRef pr on pr.PharmaID = ps.PharmaID and pr.GenericName = 'vancomycine hydrochloride'
where exists(
select 1
from prod_P_PharmaRec pr
where pr.OrderNumber = ps.OrderNumber and pr.PharmaID = ps.PharmaID
and BITAND(pr.Status, 2) <> 2
);
> usage of BITAND function in exists statement results in duplicate rows
> ----------------------------------------------------------------------
>
> Key: TEIID-3511
> URL: https://issues.jboss.org/browse/TEIID-3511
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.9.1
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Fix For: 8.12
>
> Attachments: debugplan_bitand.txt, debugplan_bitand_correct.txt, debugplan_nobitand.txt, debugplan_nobitand_correct.txt
>
>
> I've added the debug plan for the query without the BITAND function and with the BITAND function as an attachment.
> The version without the BITAND function returns 438 rows and the version with BITAND function returns 833 rows. I can see however that rows are duplicated in this second result. I've checked this by executing the version with BITAND statement with select *.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
More information about the teiid-issues
mailing list