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

Andrius Glozeckas wrote:
>
> 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

Use the IS NULL operator, instead of "= NULL", for testing if a column
is null: "WHERE gl.parent_type_id IS NULL". This is a basic SQL thing,
not a bug.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com