public inbox for [email protected]
help / color / mirror / Atom feedDocumentation enancement regarding NULL and operators
2+ messages / 2 participants
[nested] [flat]
* Documentation enancement regarding NULL and operators
@ 2024-12-15 17:35 Luca Dametto <[email protected]>
2024-12-18 16:11 ` Re: Documentation enancement regarding NULL and operators Adrian Klaver <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Luca Dametto @ 2024-12-15 17:35 UTC (permalink / raw)
To: pgsql-general
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><</token> <token>></token> <token>=</token> <token><=</token> <token>>=</token> <token><></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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Documentation enancement regarding NULL and operators
2024-12-15 17:35 Documentation enancement regarding NULL and operators Luca Dametto <[email protected]>
@ 2024-12-18 16:11 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-12-18 16:11 UTC (permalink / raw)
To: Luca Dametto <[email protected]>; pgsql-general
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]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-12-18 16:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-15 17:35 Documentation enancement regarding NULL and operators Luca Dametto <[email protected]>
2024-12-18 16:11 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox