public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Luca Dametto <[email protected]>
To: [email protected]
Subject: Re: Documentation enancement regarding NULL and operators
Date: Wed, 18 Dec 2024 08:11:35 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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
[email protected]

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Documentation enancement regarding NULL and operators
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox