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

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

From: David Fetter <david(at)fetter(dot)org>
To: Andrius Glozeckas <ndrs(at)systemap(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3729: Query doesn't return the right answer
Date: 2007-11-08 15:21:57
Message-ID: 20071108152157.GB3910@fetter.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Thu, Nov 08, 2007 at 11:23:39AM +0000, Andrius Glozeckas wrote:
> 
> 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 

This should read:

WHERE g1.parent_type_id IS NULL

If you're handling input parameters where you don't know whether they
will be NULL when you write the SQL, use constructs like

WHERE foo IS NOT DISTINCT FROM $1

Cheers,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

In response to

pgsql-bugs by date

Next:From: Daniel Cristian CruzDate: 2007-11-08 16:14:44
Subject: BUG #3731: ash table "PROCLOCK hash" corrupted
Previous:From: Alvaro HerreraDate: 2007-11-08 14:49:28
Subject: Re: BUG #3729: Query doesn't return the right answer

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