Feedback-ID: i76984098:Fastmail
Message-ID: <179fbea7-d621-4456-ad3c-afd82241776f@aklaver.com>
Date: Wed, 18 Dec 2024 08:11:35 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Documentation enancement regarding NULL and operators
To: Luca Dametto <postgresqlmailing@damettoluca.com>,
 pgsql-general@postgresql.org
References: <emc054a8a5-7ede-476f-8bea-0558d655bbd1@6050b6ba.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <emc054a8a5-7ede-476f-8bea-0558d655bbd1@6050b6ba.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/179fbea7-d621-4456-ad3c-afd82241776f%40aklaver.com>
Precedence: bulk

On 12/15/24 09:35, Luca Dametto wrote:
> 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.
> "

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


> 
> Kind regards,
> Luca

-- 
Adrian Klaver
adrian.klaver@aklaver.com