Обсуждение: BUG #3729: Query doesn't return the right answer
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
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
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
Andrius, see the explanation about that at http://www.postgresql.org/docs/8.3/static/functions-comparison.html for more details. Cheers Julo Heikki Linnakangas wrote: > 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. >
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. This is not a bug. The correct syntax for testing for NULL is "column IS NULL". Do not use "column = NULL". -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ "Changing the world ... one keyboard at a time!" (www.DVzine.org)
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