The following bug has been logged online: Bug reference: 3729 Logged by: Andrius Glozeckas Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.5 Operating system: Linux Fedora 7 Description: Query doesn't return the right answer Details:
I have a parent_type_id linking to group_type_id on the same table (group_type). I am trying to get the groups with certain parent_type_id (be it null or 17) and the number of their children in the same query: SELECT g1.*, COUNT(g2.*) FROM group_type g1 LEFT JOIN group_type g2 ON g1.group_type_id = g2.parent_type_id WHERE g1.parent_type_id = null GROUP BY g1.name, g1.type, g1.choice, g1.multiple, g1.self_ref, g1.group_type_id, g1.parent_type_id But this doesn't give me any results, although there are a few records with parent_type_id = null and one with 17. I have tried a simpler query: SELECT g1.*, g2.* FROM group_type g1 LEFT JOIN group_type g2 ON g1.group_type_id = g2.parent_type_id WHERE g1.parent_type_id = null This again doesn't give me any results If I take the WHERE off, I get a list as expected with several g1.parent_type_id = null Explain analyze on the last query above: Result (cost=19.23..59.46 rows=1 width=330) (actual time=0.002..0.002 rows=0 loops=1) One-Time Filter: NULL::boolean -> Hash Left Join (cost=19.23..59.46 rows=1 width=330) (never executed) Hash Cond: (g1.group_type_id = g2.parent_type_id) -> Seq Scan on group_type g1 (cost=0.00..14.10 rows=410 width=165) (never executed) -> Hash (cost=14.10..14.10 rows=410 width=165) (never executed) -> Seq Scan on group_type g2 (cost=0.00..14.10 rows=410 width=165) (never executed) Total runtime: 0.060 ms ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate