Result of NULL != value in an SQL query (postgres and rails3)

Question

I ran into a unexpected postgres query issue within my Rails3 app.

I thought I'd run this by stackoverflow and see what the brains of the internet have to say :)

Is this result Expected behaviour (and why?!) or is this a bug?

Given that I have a table, Orders, in my Postgres 9.1.4 database:

id        state
=====     ======
1                     <-- nil (default value)
2         'success'
3         'failure'

When I run the query:

Order.where('orders.state != ?', 'success').map { |order| order.id }
Order Load (3.8ms)  SELECT "orders".* FROM "orders" WHERE (orders.state != 'success')

=> [3]

I was expecting the result [1, 3]. There are clearly 2 rows in which (!= 'success') is satisfied.

Why is it that nil != 'success' is not true here? Does != just ignore NULL values? should it?

NOTE: I produced the desired result by using the following query:

Order.where('orders.state IS NULL OR orders.state != ?', 'success').map { |order| order.id }
Order Load (2.3ms) SELECT "orders".* FROM "orders" WHERE (orders.state IS NULL OR orders.state != 'success')

=> [1, 3]

Any comments would be appreciated.

Answer 1

There is an IS DISTINCT FROM comparison operator in PostgreSQL:

Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs:

expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than "unknown".

For example, given your sample data:

=> select * from orders where state is distinct from 'success';
 id |  state  
----+---------
  1 | 
  3 | failure
(2 rows)

So you could say this:

Order.where('orders.state is distinct from ?', 'success').pluck(:id)

Note that I also switched to pluck rather than your map(&:id), that will send this SQL to the database:

select id from orders where orders.state is distinct from 'success'

rather than select orders.* ... with client-side filter to extract the ids.