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

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@systemap.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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