Re: Documentation enancement regarding NULL and operators

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(dot)klaver(at)aklaver(dot)com