public inbox for [email protected]  

help / color / mirror / Atom feed

From: Luca Dametto <[email protected]>
To: [email protected]
Subject: Documentation enancement regarding NULL and operators
Date: Sun, 15 Dec 2024 17:35:16 +0000
Message-ID: <[email protected]> (raw)

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

Attachments:

  [application/octet-stream] 0001-Improved-documentation-for-Syntax-s.-Operators-Prece.patch (1.7K, 3-0001-Improved-documentation-for-Syntax-s.-Operators-Prece.patch)
  download | inline diff:
From 662d684d0eddffb381369e909d8c70bd723025ac Mon Sep 17 00:00:00 2001
From: Luca Dametto <[email protected]>
Date: Sun, 15 Dec 2024 18:33:13 +0100
Subject: [PATCH] Improved documentation for Syntax, s. Operators Precedence

---
 doc/src/sgml/syntax.sgml | 12 +++++++++++-
 1 file changed, 11 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 916189a7d6..79f3b9ce5f 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1111,7 +1111,7 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
        <entry><token>&lt;</token> <token>&gt;</token> <token>=</token> <token>&lt;=</token> <token>&gt;=</token> <token>&lt;&gt;</token>
 </entry>
        <entry></entry>
-       <entry>comparison operators</entry>
+       <entry>comparison operators *</entry>
       </row>
 
       <row>
@@ -1151,6 +1151,16 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
     matter what yours does.
    </para>
 
+   <para>
+    * Note that <productname>PostgreSQL</productname> 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 <literal>OPERATOR</literal> <token>=</token>, 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.
+   </para>
+   
    <para>
     When a schema-qualified operator name is used in the
     <literal>OPERATOR</literal> syntax, as for example in:
-- 
2.39.5 (Apple Git-154)

view thread (2+ messages)  latest in thread

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]
  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