Thread: BUG #13501: Bug with subqueries in WHERE clause
The following bug has been logged on the website: Bug reference: 13501 Logged by: Egidio Caprino Email address: me@egidiocaprino.it PostgreSQL version: 9.3.6 Operating system: Linux (don't know the distribution) Description: This query returns one record select * from merge.organization where school_id = 1000056 This query returns no record select * from merge.account where school_id = 1000056 This query returns no record select * from merge.sd3_hierarchy where school_id = 1000056 This query returns no record select * from merge.organization where school_id not in (select school_id from merge.account) or school_id not in (select school_id from merge.sd3_hierarchy) So I have one organisation that does not exists in the account table and does not exist in the sd3_hierarchy. The last should return at least one record.
me@egidiocaprino.it writes: > This query returns no record > select * > from merge.organization > where school_id not in (select school_id from merge.account) > or school_id not in (select school_id from merge.sd3_hierarchy) This usually means that you've got NULLs in the school_id column. NOT IN never returns true if there are any nulls in the sub-select result. This is per SQL standard. regards, tom lane