3

I am working on a rails 3.2 application and using postures as database.

Following query, generated by Active Record doesn't work. The code which generates this query is in this question Scope for an optional has_one association with itself.

SELECT "indents".* FROM "indents" WHERE "indents"."type" IN ('Purchase') AND
(id not in (NULL,15017)) ORDER BY indent_date DESC

But if I manually remove the NULL, and try, it works. 

SELECT "indents".* FROM "indents" WHERE "indents"."type" IN ('Purchase') AND
(id not in (15017)) ORDER BY indent_date DESC

What is wrong here?

Community
  • 1
  • 1
Bot
  • 1,001
  • 2
  • 13
  • 32

1 Answers1

3

That is by design. You never get TRUE when comparing anything to NULL. I quote the manual here:

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values.

You can find a detailed explanation here:
NOT IN in postgresql not working

If the left side of the expression (id) is NOT NULL, one way to repair this would be:

AND   (id IN (NULL,15017)) IS NOT TRUE
Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137