from: john foderaro, jkf@franz.com
platform: x86 running Redhat 6.2 Linux from rpm
postgresql-server-6.5.3-6
postgresql version: 6.5.3 (my guess based on rpm name).
problem: count() return the wrong result given a very simple join:
sample: we create a table of 3 elements and join it with itself creating
a table of 9 elements. select shows the correct rows but count(*)
can't count them, it returns 3 rather than 9.
jkf=> drop table foo;
DROP
jkf=> create table foo(a int);
CREATE
jkf=> insert into foo values(1);
INSERT 18729 1
jkf=> insert into foo values(2);
INSERT 18730 1
jkf=> insert into foo values(3);
INSERT 18731 1
jkf=> select * from foo;
a
-
1
2
3
(3 rows)
jkf=> select * from foo aa, foo bb;
a|a
-+-
1|1
2|1
3|1
1|2
2|2
3|2
1|3
2|3
3|3
(9 rows)
jkf=> select count(*) from foo aa, foo bb;
count
-----
3 <<<<<<<<<<<<<<<<<<< should be 9 .....................
(1 row)
jkf=> \q
