cancel
Showing results for 
Search instead for 
Did you mean: 

Subquery returning incorrect results. (Bug?)

Former Member
3,244

I have a subquery that is returning incorrect results. When I run the subquery by itself it returns the correct values, but when it is run as a subquery it returns a different result set.

When I run the following:

select * from
(select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp
group by acct, patient_code, sub_num having count(*) > 1)
zas order by acct

It returns:
acct,patient_code,sub_num,acoverage,cnt
1623,'B',2,2,2
3025,'B',2,1,2
3029,'B',2,1,2
(Sometimes acoverage is all 1's or some combination of 1's and 2's)

When I run the subquery alone:

select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp
group by acct, patient_code, sub_num having count(*) > 1
order by acct

It returns:
acct,patient_code,sub_num,acoverage,cnt
1623,'B',2,2,2
3025,'B',2,2,2
3029,'B',2,2,2
(Notice acoverage is now 2)

Is there something I am missing, or is this really a SQL bug? Thanks

The code to create the #temp table is as below:

create table #temp (acct integer, patient_code char(1), coverage integer, sub_num integer);

begin
declare @i integer;
  set @i = 1;
  while @i < 5500 loop
    insert into #temp (acct, patient_code, coverage, sub_num) values (@i,'B',1,2); 
    set @i = @i + 1;
  end loop;
end;

insert into #temp (acct, patient_code, coverage, sub_num) values (1623,'B',2,2);
insert into #temp (acct, patient_code, coverage, sub_num) values (3025,'B',2,2);    
insert into #temp (acct, patient_code, coverage, sub_num) values (3029,'B',2,2);

This is running on 12.0.1.3152 on Windows 7.

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

It seems to be a bug solved in newer versions.

With 12.0.1.3324, I can reproduce your results. When using the newest Windows build (12.0.1.3519), the query with the derived table lists the desired results, i.e. both queries return the same result:

select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp
group by acct, patient_code, sub_num having count(*) > 1
order by acct;

select * from
   (select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp
    group by acct, patient_code, sub_num having count(*) > 1) zas
order by acct

So I would recommend to use a current 12.0.1 EBF - you seem to be using the (quite old) original GA version 12.0.1.3152.

BTW: AFAIK, "zas" is not used as a subquery but as a "derived query" here.

Former Member
0 Kudos

Thanks for the term. It originally was happening on a subquery, but this was easier to recreate and I never changed it.

Former Member
0 Kudos

Thanks, works on 12.0.1.3519 (latest release).

Answers (0)