Thread: Different results based on number of records out of same query

I have a problem which is disheartening.
I have a query where I am trying to find records with differnt case. For
example 'AA' vs 'Aa'. If I run the query on a table with 7.5 million
records I am getting an incorrect result. If I copy a small subset of the
data and run the same query I get a correct result.

On the big table I have 84 records for 'little irish nut' of which 1 is
'LITTLE IRISH NUT'.

I run the query:
insert into tmp (horse,hcount) select lower(he.horse) as lhorse, count(*)
as hcount from
(select distinct horse from hearn) as he group by lhorse;

Basically I do a sub select with distinct on the table so I get 1 record
for each case; I then do a select to count the instances of lower(horse).
For any horse which the distinct returned more than one instance I would
expect the count(*) to show how many variances of the case there were.

If I create small table with 100 records or so the same query returns the
correct result. ie. it indicates on the count() the variances for the
case.

Is this a bug?

Francisco Reyes <lists@natserv.com> writes:
> Is this a bug?

How is anyone to tell?  You haven't shown us the results you believe
are incorrect, nor told your reasons for believing them incorrect.

            regards, tom lane

On Sat, 10 Nov 2001, Tom Lane wrote:

> Francisco Reyes <lists@natserv.com> writes:
> > Is this a bug?
>
> How is anyone to tell?  You haven't shown us the results you believe
> are incorrect, nor told your reasons for believing them incorrect.

Found the problem.
My mistake. I was using a wrong table in one of the sub-selects.