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?