Do any databases get NULL and "NULL" confused, or is it always an application design failure?

Question

I've read a few articles like this one about a license plate value of NULL (about Droogie from DEF CON 27), including part of chapter three Little Data in the book Humble Pi by Matt Parker (talking about Steve Null), where storing a string value of "NULL" in a database matches NULL values.

With databases I've used (at least AFAIK), "NULL" isn't the same as a NULL value. The state of a field being NULL is stored separately from the value.

So SQL like

SELECT bar 
FROM foo
WHERE foobar IS NULL;

would be different than

SELECT bar 
FROM foo
WHERE foobar = 'NULL';

When I first heard these stories I thought they must be urban legends, but after seeing a few more it made me wonder if I was missing something? Is it a matter of some databases that don't distinguish "NULL" from is NULL (if so, which ones, any current ones)? Or is it a matter of whoever built the database application storing "NULL" as a string for NULL values or some other poor design?

Further reading:

To summarize my question:

Do some databases mishandle NULL vs. "NULL" ?
- Is that only historical databases, or are there current databases that do the same?
Is there a logical reason to have "NULL" equal IS NULL from architecture POV?
Or are all of these an example of an application design failure?
- How can you mess it up that bad?

I'd really love to see an example of some SQL that confuses NULL and 'NULL'.

I would suggest that those are issue with application logic, not the underlying database. — Gordon Linoff, Aug 25 '20 at 17:36
If an application breaks when someone enters the string "null", then that app is probably wide open to SQL Injection. — The Impaler, Aug 25 '20 at 19:03
@TheImpaler That was my thought too. But still, it seems like it should read the input as 'NULL' instead of the NULL keyword. It seems like the developers would need to intentionally mess this up. — Jim McKeeth, Aug 25 '20 at 19:05
I agree that it is extremely unlikely that a DBMS has any problem here, as `NULL` is used with `IS` and `IS NOT`, which makes its use distinct from that of `= 'NULL'` or `'<> 'NULL'`. However, you may want to edit your request, because while `NULL` is the absence of a value and `'NULL'` is a string, `"NULL"` is not a string, but a name. — Thorsten Kettner, Aug 25 '20 at 21:58

score 1 · Answer 1 · answered Aug 25 '20 at 17:37

1

It's a design flaw. "NULL" is a string and you shouldn't have to use a string to represent Nothing. So in 1 case, you're looking for a populated value of exactly "NULL" where with IS NULL there actually is nothing there.

answered Aug 25 '20 at 17:37

Jimmy Smith

2,377
1
14
19

1

This is exactly what I thought. But when I read about it they usually try to make it sound like a database error, but the articles are usually written by non-technical people. – Jim McKeeth Aug 25 '20 at 18:27
1

After a decade in the industry, I've learned sometimes the issue is the author is trying to make things "too simple", and therefore, complicating it again! – Jimmy Smith Aug 25 '20 at 18:47
1

Actually `"NULL"` is an identifier, `'NULL'` would be a string – a_horse_with_no_name Aug 25 '20 at 18:57
1

It is 8 0s representing nothing - least it was in the early 2000s... – Jimmy Smith Aug 25 '20 at 18:59

The Impaler · Accepted Answer · 2020-08-25T19:01:27.863

You are describing two different concepts:

In relational databases 'null' is a CHAR value (of length 4), as simple as that.
On the other side null is not a value, but represents a "missing value". It's not the absence of value either; it means that "the value does exist, but we failed to recover it."

Therefore, they are both very different concepts. I don't know a database that mishandles nulls as you present it. However, I can think of applications that do not distinguish them well. I would consider that defects on the app, not on the database engines themselves.

Anyway, here are a few SQL expressions in PostgreSQL and their values to illustrate the definitions above:

select 
  'null' = 'null', -- 1. TRUE
  'null' = null,   -- 2. null (actually UNKNOWN)
  'null' <> null,  -- 3. null (actually UNKNOWN)
  'null' is null,     -- 4. FALSE
  'null' is not null, -- 5. TRUE
  null is null,       -- 6. TRUE
  null is not null,   -- 7. FALSE
  null = null,        -- 8. null (actually UNKNOWN)
  null <> null,       -- 9. null (actually UNKNOWN)
  null is not distinct from null, -- 10. TRUE
  (null = null) is unknown,       -- 11. TRUE
  (null = null) is true,          -- 12. FALSE
  (null = null) is false,         -- 13. FALSE
  (null <> null) is unknown       -- 14. TRUE

See running example at DB Fiddle.

Note. When you typically compare against null the result is the bona fide value UNKNOWN, not TRUE, not FALSE. However, most database drivers convert that value to a null when sending it to your app, as you can see in the cases #2, #3, #8, and #9 above.

Exactly. Thanks for making the DB Fiddle. That is really useful and interesting. It seems like someone would have to intentionally mess up NULL vs 'NULL'. — Jim McKeeth, Aug 25 '20 at 19:03
I think that many developers don't understand/know that a Java/C/PHP null is very different from a SQL null. Also a Java/C/PHP boolean has two possible values, while a SQL boolean has three (true, false, unknown). There's a big impedance there that few developers understand. Also many ORMs fail to map them correctly. — The Impaler, Aug 25 '20 at 19:06
2, 3, 8, 9, 11, 12, 13 and 14 should actually raise a syntax error exception. The NULL keyword cannot syntactically be an argument to the comparison operators =, >, >=, . (According to ANSI/ISO SQL.) — jarlh, Aug 25 '20 at 21:12

Do any databases get NULL and "NULL" confused, or is it always an application design failure?

2 Answers2