From: | Francisco Reyes <lists(at)natserv(dot)com> |
---|---|
To: | Pgsql Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Different results based on number of records out of same query |
Date: | 2001-11-10 16:09:31 |
Message-ID: | 20011110105302.S36758-100000@zoraida.natserv.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-10 17:16:10 | Re: Different results based on number of records out of same query |
Previous Message | Brett W. McCoy | 2001-11-09 21:45:13 | Re: |