Subject: Documentation enancement regarding NULL and operators

From: "Luca Dametto" <[email-masked]>
Date: Sun, 15 Dec 2024 17:35:16 +0000

Hi All,
I'm coming from hours of debugging a SQL trigger that wasn't working 
properly. After a beautiful headache and infinite hours of documentation 
reading I've found out that something doesn't work as I would expect.

Most programming languages return "true" when two null values are 
compared, and false when, being the two values nullable, one of them is 
null and the other one isn't.
Any developer coming from Python, Javascript, PHP (and many more) would 
expected 'example'= null to return false, whilst SQL thanks to 3VL 
returns you a gentle ¯\_(ツ)_/¯ . Not a true, not a false, just nothing - 
in a boolean statement.

Python3:
 >>> "example" == None
False
 >>> None == None
True

NodeJS:
 > "example" == null
false
 > null == null
true

PHP 8:
 > var_dump("example" == null);
bool(false)
 > var_dump(null == null);
bool(true)

Whilst I'd love to discuss the reasons of this, I understand that it 
would be a waste of time for everyone, as we cannot change the 
status-quo even if it made sense, as it would break many thousands of 
projects.

For that reason, I'd just like to improve the documentation to add at 
least a note about "hey, this won't work as you might expect, because it 
works in a different way than 99% of programming languages out there.".
I've tried to understand how to submit my proposal for the documentation 
improvements, but it's way harder than what my brain can handle with the 
current headache caused by this stuff, I've attached a git patch to this 
email in case it's useful.

Content: "
PostgreSQL follows SQL's 3VL, due to that some comparisons regarding 
NULL values may not work as you might expect.
As an example, two nullable columns that contain NULL, when compared 
using the OPERATOR =, will return nothing instead of TRUE like your 
programming language may do. In this case, only 'IS NOT DISTINCT FROM' 
would return the result you expect.
"

Kind regards,
Luca

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 18 Dec 2024 08:11:35 -0800

On 12/15/24 09:35, Luca Dametto wrote:
> returns you a gentle ¯\_(ツ)_/¯ . Not a true, not a false, just nothing 
> - in a boolean statement.
> 
> Python3:
>  >>> "example" == None
> False
>  >>> None == None
> True
> 
> NodeJS:
>  > "example" == null
> false
>  > null == null
> true
> 
> PHP 8:

See:

https://www.postgresql.org/docs/current/functions-comparison.html

" 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 predicates:"

-- 
Adrian Klaver
[email-masked]