Skip site navigation (1) Skip section navigation (2)

BUG #3729: Query doesn't return the right answer

From: "Andrius Glozeckas" <ndrs(at)systemap(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3729: Query doesn't return the right answer
Date: 2007-11-08 11:23:39
Message-ID: 200711081123.lA8BNdIK057877@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3729
Logged by:          Andrius Glozeckas
Email address:      ndrs(at)systemap(dot)com
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

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2007-11-08 12:00:49
Subject: Re: BUG #3729: Query doesn't return the right answer
Previous:From: Zdenek KotalaDate: 2007-11-08 10:04:01
Subject: Re: BUG #3728: pthread autoconf hangs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group