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
