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.

Don't know why but your first request is checking qb_sync_status whereas it should check state... — Raindal
– Raindal, Commented May 10, 2013 at 16:10
aah, 'cause I poorly copied&pasted this example. corrected, thanks Sparda — Matt Riemer
– Matt Riemer, Commented May 10, 2013 at 16:19
This is the way the underlying SQL is design to work. The NULL value can cause problems when selecting data because when comparing an unknown value, i.e. NULL, to any other value, the result is always unknown and not included in the final results. — thisfeller
– thisfeller, Commented May 10, 2013 at 16:26

mu is too short · Accepted Answer · 2013-05-10 17:10:45Z

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.

noel · Accepted Answer · 2013-11-25 21:59:04Z

1

This is part of the SQL standard, where a comparison between two values involving at least one NULL returns neither true, nor false, but unknown.

From http://www-cs-students.stanford.edu/~wlam/compsci/sqlnulls

A boolean comparison between two values involving a NULL returns neither true nor false, but unknown in SQL's three-valued logic. [3] For example, neither NULL equals NULL nor NULL not-equals NULL is true. Testing whether a value is NULL requires an expression such as IS NULL or IS NOT NULL.

An SQL query selects only values whose WHERE expression evaluates to true, and groups whose HAVING clause evaluates to true.

One solution is to use COALESCE:

Order.where('COALESCE(orders.state,"NIL") != ?', 'success').map(&:id)

If orders.state is NULL, it will replace it with 'NIL', which will then return true or false from the comparison.

You can of course use an additional condition:

Order.where('orders.state is null OR orders.state != ?', 'success').map(&:id)

edited Nov 25, 2013 at 21:59

answered May 10, 2013 at 16:41

noel

2,15514 silver badges14 bronze badges

1 Comment

mu is too short Over a year ago

isnull() is, AFAIK, a MySQLism. The standard way is to say orders.state is null.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related