Inconsistent result from subquery using max function - database bug?
Two queries with same subquery did not return same counts. The first query returns correct number of counts. The second one returns more counts.
query 1 - correct counts
select max(rownum) from
(select c_day, b_num, max(gap), max(postingdate) from block_gap
group by c_day, b_num having max(gap)>=1 and max(postingdate)<20051102)
MAX(ROWNUM)
----------------------
329658
query 2 - Incorrect counts
select count(*) from
(select c_day, b_num, max(gap), max(postingdate) from block_gap
group by c_day, b_num having max(gap)>=1 and max(postingdate)<20051102)
COUNT(*)
----------------------
361002
I tried to find why the second query returned more items than first one by running the following queries and found that the seond query returned some items which contains postingdate >20051102, and it is incorrect return.
select c_day, b_num, max(gap), max(postingdate) from block_gap
group by c_day, b_num having max(gap)>=1 and max(postingdate)<20051102
correct result - return 329658 rows
select * from (
select c_day, b_num, max(gap), max(postingdate) from block_gap
group by c_day, b_num having max(gap)>=1 and max(postingdate)<20051102)
wrong result - return 361002 rows