Subquery returning incorrect results. (Bug?)

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.

asked 03 Jan '12, 11:39

tmjac2
36●1●1●7
accept rate: 0%

edited 03 Jan '12, 12:01

Volker Barth
39.5k●355●539●811

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 03 Jan '12, 11:55

Volker Barth
39.5k●355●539●811
accept rate: 34%

edited 04 Jan '12, 03:36

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

(03 Jan '12, 12:01) tmjac2

Thanks, works on 12.0.1.3519 (latest release).

(03 Jan '12, 14:42) tmjac2

Comment Text Removed

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
sql ×90
bug ×43
derived-table ×5

question asked: 03 Jan '12, 11:39

question was seen: 2,717 times

last updated: 17 Jan '12, 12:34

SAP SQL Anywhere

Subquery returning incorrect results. (Bug?)

Follow this question

Related questions