same query giving different results in different versions of postgresql !!!!

Question

Folks,

I am running given below query in two different server which has different versions of postgresql which gives strange results.

select distinct "D","E","A","B","F","C","G","H","I","J","K","L" from ABC where "L"=1 group by "D","E","A","B","F","C","G","H","I","L" order by "A", "B", "C";

Server1: db details->PostgreSQL 8.3.9 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 EXPLAIN OUTPUT OF QUERY:

  Unique  (cost=16618.27..16884.84 rows=8202 width=71)
  Sort  (cost=16618.27..16638.78 rows=8202 width=71)
    Sort Key: "A", "B", "C", "D", "E", "F","G", "H", "I", J, K
     GroupAggregate  (cost=13296.68..16085.07 rows=8202 width=71)
           Sort  (cost=13296.68..13501.71 rows=82011 width=71)
                Sort Key: "A", "B", ""C"", "D", "E", "F", "G", "H", "I", "L"
                 Seq Scan on exims  (cost=0.00..3236.14 rows=82011 width=71)
                      Filter: ("L" = 1)                         

  Server2: db details->PostgreSQL 7.4.23 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)

 EXPLAIN OUTPUT OF QUERY:
  Unique  (cost=22.73..22.90 rows=5 width=361)
   Sort  (cost=22.73..22.75 rows=5 width=361)
    Sort Key: "A", "B", "C", "D", "E", "F","G", "H", "I", J, K, "L"
      HashAggregate  (cost=22.65..22.67 rows=5 width=361)
            Seq Scan on exims  (cost=0.00..22.50 rows=5 width=361)
                Filter: ("L" = 1)

for both the servers I am using same input tables with 82011 records and results are server1: output of that query -> 82011 records server2: output of that query -> 53146 records

as for as I can see in server 2 uses Hashaggregate method while server1 uses groupaggregate?

can someone explain which one is the server result I should trust?

BR/ Vijay

Answer 1

My guess would be that the databases are running in different locales. Compare the output of

SHOW lc_collate;

on both databases.

It's a fairly common mistake not to set these to the same when upgrading a system, which can lead to opretaions like ORDER BY, DISTINCT and GROUP BY to deliver different resulsts.