Icon View Incident Report

Serious Serious
Reported By: James Shedden
Reported On: 4/29/2011
For: Version 4.30 Build 4
# 3441 INTERSECT Queries Not Handling Empty Sets Properly When More than Two Queries Involved

In the attached 'differentResults.sql' the intersect query picks up patient 6, who does not receive agent 'C', while my original query doesn't. I'm expecting intersection with a null set to return null.

drop table if exists cTest;
create table cTest
(
patnum largeint not null,
mention smallint,
agent varchar(1),
primary key(patnum asc, mention asc)
);

--insert into cTest values(0,1,'');
--insert into cTest values(0,2,'');
--insert into cTest values(0,3,'');
--insert into cTest values(1,1,'');
--insert into cTest values(1,2,'');
insert into cTest values(1,3,'C');
--insert into cTest values(2,1,'');
insert into cTest values(2,2,'B');
--insert into cTest values(2,3,'');
--insert into cTest values(3,1,'');
insert into cTest values(3,2,'B');
insert into cTest values(3,3,'C');
insert into cTest values(4,1,'A');
--insert into cTest values(4,2,'');
--insert into cTest values(4,3,'');
insert into cTest values(5,1,'A');
--insert into cTest values(5,2,'');
insert into cTest values(5,3,'C');
insert into cTest values(6,1,'A');
insert into cTest values(6,2,'B');
--insert into cTest values(6,3,'');
insert into cTest values(7,1,'A');
insert into cTest values(7,2,'B');
insert into cTest values(7,3,'C');

select distinct patnum
into AandBandC
from cTest
where
 patnum in (select patnum from cTest where agent='A') and
 patnum in (select patnum from cTest where agent='B') and
 patnum in (select patnum from cTest where agent='C');

select patnum
into AandBandC_intersect
from cTest
where agent='A'
intersect
select patnum from cTest where agent='B'
intersect
select patnum from cTest where agent='C';



Comments Comments
The problem was related to how DBISAM was handling more than two queries involved in an INTERSECT operation.


Resolution Resolution
Fixed Problem on 4/30/2011 in version 4.30 build 5


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image