DEV Community

Pavel Myslik
Pavel Myslik

Posted on

I Thought My Rails Query Was Fine — Until NULL Ate My Data

#rails #webdev #sql #postgres

I ran into this while working on a task where I needed to process all contracts that were not coming from SAP.

At first, the query looked perfectly fine:

Contract.where.not(source: 'sap')
Enter fullscreen mode Exit fullscreen mode

Then I double-checked the result by counting returned objects — and something didn’t add up.

You write a Rails query.
It looks correct.
It runs without errors.

But it's quietly hiding records from you.

Welcome to one of the most common — and maybe most dangerous — SQL gotchas.

The Setup

Imagine you have a contracts table with a source column — which is nullable.

Some contracts have:

  • source = 'sap'
  • source = 'web'
  • and some have NULL

Now you want to count all contracts that are not from 'sap'.

Sounds simple:

Contract.where.not(source: 'sap').count
# => 68
Enter fullscreen mode Exit fullscreen mode

68 contracts. Looks reasonable.

But to be sure, I double-checked using Ruby:

Contract.reject { |c| c.source == 'sap' }.size
# => 310
Enter fullscreen mode Exit fullscreen mode

Wait... 310?

Where did 242 records go?

The Problem: NULL Is Not a Value

In SQL, NULL does not mean empty.

It means unknown.

That distinction changes everything.

When SQL evaluates:

source != 'sap'
Enter fullscreen mode Exit fullscreen mode

there are three possible outcomes:

'web'     != 'sap'  => TRUE  (included)  
'sap'     != 'sap'  => FALSE (excluded)  
NULL      != 'sap'  => NULL  (excluded)
Enter fullscreen mode Exit fullscreen mode

That third row is the trap.

NULL != 'sap' does not return TRUE.

It returns NULL.

And in SQL’s three-valued logic, the WHERE clause only keeps rows where the condition is TRUE.

FALSE is excluded.
NULL is also excluded.

Silently.

This isn’t just an issue with !=. Any comparison or negation— <, >, NOT LIKE, NOT IN—can silently exclude NULL rows.

The Fix

Here are three common ways to handle this safely, depending on your database and preference:

Option 1: Rails-style .or with where.not 

Contract.where.not(source: 'sap')
        .or(Contract.where(source: nil))
        .count
# => 310
Enter fullscreen mode Exit fullscreen mode
  • Rails-native syntax.
  • Includes all NULL values.
  • Works on any database supported by Rails.

 Option 2: Explicitly include NULLs in SQL with OR 

Contract.where("source != ? OR source IS NULL", 'sap')
        .count
# => 310
Enter fullscreen mode Exit fullscreen mode
  • Simple SQL pattern.
  • Works reliably across most databases (PostgreSQL, MySQL, SQLite).

Option 3: Use IS DISTINCT FROM (PostgreSQL) 

Contract.where("source IS DISTINCT FROM ?", 'sap')
        .count
# => 310
Enter fullscreen mode Exit fullscreen mode
  • PostgreSQL-specific.
  • NULL is treated like a regular value.

The Takeaway

NULL didn’t break my query. It behaved exactly as SQL intended.

That’s what makes it dangerous — no error, no warning, no sign anything went wrong. Just missing data, quietly waiting for you to notice.

Once you understand that NULL means unknown, not empty, the behavior starts to make sense.

Until then… it can silently cost you 242 records.

Have you ever shipped a bug caused by NULL hiding in a query?

Top comments (0)