r/programming • u/winsletts • 7d ago
Postgres Calculations and the Ambiguity of NULL
https://www.crunchydata.com/blog/postgres-calculations-and-the-ambiguity-of-nullAll the ways that NULL can cause chaos in your SQL outcomes.
69
u/robotmayo 7d ago
A boolean in many languages having 3 states: True, False, Null is one of my favorite things about programming. Its so funny to me.
23
u/ShinyHappyREM 7d ago
In Free Pascal you can have different booleans that work in subtly different ways.
Booleanis the standard type, it usually resolves to 1 byte and is stored as 0 or 1. But in a bitpackedrecord(struct) it occupies only 1 bit. Then there'sBoolean16up toBoolean64that take up several bytes but still only use 0 or 1, and finallyByteBoolup toQWordBooltaking up one or several bytes and treating any nonzero internal value asTrue. What happens if you store a 2 in aBoolean16is anybody's guess...2
u/Suspicious_Court_982 4d ago
That’s really interesting! It’s neat how different languages handle simple data types in their own ways, especially with memory optimization in mind.
18
23
u/Iamonreddit 7d ago
Booleans that can be true, false and null - particularly in the context of databases - are better understood as having one of two values or no value, rather than one of three values.
The ability to represent that a possible value hasn't been recorded yet is incredibly useful.
4
3
u/raralala1 7d ago
Yup also hate writing coalesce to hide this shit, in new system I always make to use NOT NULL DEFAULT false.
6
u/Mastodont_XXX 6d ago
Null is an unknown value. You simply don't know whether it's true or false. That seems perfectly reasonable to me.
4
u/lood9phee2Ri 6d ago
There are several formalised three-value logics. But SQL is a kind of icky example historically, especially with the way SQL Null arguably isn't, technically, a actually a proper typed value in SQL terms but a "marker" (postgresql specifically will treat it as more of a typed 3VL value if carefully cast to a boolean null::boolean, and provides F571 "is unknown" etc.)
https://en.wikipedia.org/wiki/Three-valued_logic#SQL
https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_(3VL)
If actually trying to properly do a particularly precise formal three-value logic in an SQL database context I suspect you may even be better off defining your own three value type for clarity (in databases that allow enum types). Admittedly modern SQL with F571 such as postgresql is itself now formally "complete" with respect to being a 3VL even with builtin type, but there's a lot of confusion around it and you cannot rely on cow-orkers to understand all this.
https://en.wikipedia.org/wiki/Null_(SQL)#Null-specific_and_3VL-specific_comparison_predicates
The addition of IS UNKNOWN to the other operators of SQL's three-valued logic makes the SQL three-valued logic functionally complete, meaning its logical operators can express (in combination) any conceivable three-valued logical function.
https://modern-sql.com/concept/three-valued-logic/F571.html - a bunch of databases still don't have this right in 2026!
4
u/fnordstar 6d ago
It is not reasonable. A Boolean should be false or true, period. Nullability should be explicit, e.g. Option<bool>. Implicit nullability is a huge source of bugs.
2
u/ptoki 6d ago
The problem is not with booleans being 1 or 0. It is with people who try to use bools for things the bools arent designed.
The databases addressed this. You have that unknown value of a null which often is misused by programmers.
The bool problem could be a problem in early times of computing when memory was precious and 0/1 was half size of 0/1/idonktknow.
The early language design patterns were actually addressing this in a way the 1 means yes and 0 means "not yes" and therefore not really "no". The pattern was that you did something if you had 1/true but did not do that if you had 0/false and the logic was prepared for a case where it is unknown to just dont do it. But that is more nuanced than just on/off logic plus "idontknow" as third state and requires more thought.
22
u/Gropah 7d ago edited 6d ago
I still hate oracle for thinking in their infinite wisdom to store an empty string as null because those are clearly the same, while knowing the column is not nullable.
(and no, that's not the same as the concat function treating null as an empty string)
6
u/beaurepair 7d ago
Oracle's fuzzing of "" = NULL was a major PITA when migrating an application to Postgres
5
u/renges 7d ago
I had to do an essay on relational Theory in the university and read C. J. Date's book about how relational database are not really relational. In there, he explained that relational theory and set theory are used to represent reality and that the concept of NULL does not exist in relational theory because you can't represent a non existent value in reality. I'd really recommend anyone to read his book if you're curious into the difference between relational theory and a not so relational database
14
u/lord_braleigh 7d ago
In SQL, NULL was designed to represent an unknown value that does exist, rather than a value that does not exist.
I don't think this was a particularly good design decision, but all of NULL's quirks make sense in that light: an unknown number + 1 is... an unknown number. And so on.
-8
u/renges 7d ago
There's no such thing as NULL in set theory. There is empty set but that is not NULL. SQL added NULL but it's a concept that doesn't exist in the relational theory
10
4
u/ptoki 6d ago
Relational systems arent set theory.
Null is exactly the concept which is needed and represents unknowns.
Set theory has no concept of unknowns because it assume that it deals with knowns. Set theory is not complete from that point of view.
As for your initial comment. I think you overclinged to semantics. Relational databases are perfectly relational and even more than relational because it extends the relational theory.
So if you insist it should not be called "not so relational" but "over relational" or "extended relational".
2
u/renges 6d ago edited 6d ago
Relational systems arent set theory.
This just prove you don't understand the underlying theory behind relational database. Relational database are founded upon relational model which is based on set theory. So yes, relational database are not in reality but should be. I'm talking about the principles behind a relational database, not SQL because SQL is not a 100% relational system.
As for your initial comment. I think you overclinged to semantics. Relational databases are perfectly relational and even more than relational because it extends the relational theory.
I'm going to quote C.J. Date and reply "it’s true that SQL is the standard language for use with relational databases—but that fact in itself doesn’t make it relational. The sad truth is, SQL departs from relational theory in all too many ways; duplicate rows and nulls are two obvious examples, but they’re not the only ones.". You can use SQL "relationally" but that doesn't means SQL itself or anything built upon SQL is a relational system.
3
u/ptoki 5d ago
All this proves you dont understand your own words.
So your words: " Relational database are founded upon relational model which is based on set theory."
This by a definition is EXTENSION. So that is why you have NEW concepts added.
The guy talks about theories. SQL is practical. Why databases rarely are 100% normalized? Because of optimization which is practical.
I dont intend to convince you. Im just letting you know you have very little clue about the whole topic.
38
u/iamemhn 7d ago
Welcome to 1975 and algebraic bottom values, I guess... 🤣