From: "Luca Dametto" <postgresqlmailing@damettoluca.com>
To: pgsql-general@postgresql.org
Subject: Documentation enancement regarding NULL and operators
Date: Sun, 15 Dec 2024 17:35:16 +0000
Message-Id: <emc054a8a5-7ede-476f-8bea-0558d655bbd1@6050b6ba.com>
Reply-To: "Luca Dametto" <postgresqlmailing@damettoluca.com>
Disposition-Notification-To: "Luca Dametto" <postgresqlmailing@damettoluca.com>
User-Agent: eM_Client/10.1.4828.0
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------=_MBE7AEBD0A-B7B9-41D2-BDDA-F4B500AEB45E"
Archived-At: <https://www.postgresql.org/message-id/emc054a8a5-7ede-476f-8bea-0558d655bbd1%406050b6ba.com>
Precedence: bulk

--------=_MBE7AEBD0A-B7B9-41D2-BDDA-F4B500AEB45E
Content-Type: multipart/alternative;
 boundary="------=_MB2056DE52-0708-40B4-A196-1530C11F497E"

--------=_MB2056DE52-0708-40B4-A196-1530C11F497E
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi All,
I'm coming from hours of debugging a SQL trigger that wasn't working=20
properly. After a beautiful headache and infinite hours of documentation=20
reading I've found out that something doesn't work as I would expect.

Most programming languages return "true" when two null values are=20
compared, and false when, being the two values nullable, one of them is=20
null and the other one isn't.
Any developer coming from Python, Javascript, PHP (and many more) would=20
expected 'example'=3D null to return false, whilst SQL thanks to 3VL=20
returns you a gentle =C2=AF\_(=E3=83=84)_/=C2=AF . Not a true, not a false, =
just nothing -=20
in a boolean statement.

Python3:
 >>> "example" =3D=3D None
False
 >>> None =3D=3D None
True

NodeJS:
 > "example" =3D=3D null
false
 > null =3D=3D null
true

PHP 8:
 > var_dump("example" =3D=3D null);
bool(false)
 > var_dump(null =3D=3D null);
bool(true)

Whilst I'd love to discuss the reasons of this, I understand that it=20
would be a waste of time for everyone, as we cannot change the=20
status-quo even if it made sense, as it would break many thousands of=20
projects.

For that reason, I'd just like to improve the documentation to add at=20
least a note about "hey, this won't work as you might expect, because it=20
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=20
improvements, but it's way harder than what my brain can handle with the=20
current headache caused by this stuff, I've attached a git patch to this=20
email in case it's useful.

Content: "
PostgreSQL follows SQL's 3VL, due to that some comparisons regarding=20
NULL values may not work as you might expect.
As an example, two nullable columns that contain NULL, when compared=20
using the OPERATOR =3D, will return nothing instead of TRUE like your=20
programming language may do. In this case, only 'IS NOT DISTINCT FROM'=20
would return the result you expect.
"

Kind regards,
Luca
--------=_MB2056DE52-0708-40B4-A196-1530C11F497E
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head>

<style id=3D"css_styles"> =20
blockquote.cite { margin-left: 5px; margin-right: 0px; padding-left: 10px;=
 padding-right:0px; border-left: 1px solid #cccccc }
blockquote.cite2 {margin-left: 5px; margin-right: 0px; padding-left: 10px;=
 padding-right:0px; border-left: 1px solid #cccccc; margin-top: 3px; padding=
-top: 0px; }
a img { border: 0px; }
table { border-collapse: collapse; }
li[style=3D'text-align: center;'], li[style=3D'text-align: center; '], li[s=
tyle=3D'text-align: right;'], li[style=3D'text-align: right; '] {  list-sty=
le-position: inside;}
body { font-family: 'Segoe UI'; font-size: 9pt; }
.quote { margin-left: 1em; margin-right: 1em; border-left: 5px #ebebeb soli=
d; padding-left: 0.3em; }
a.em-mention[href] { text-decoration: none; color: inherit; border-radius:=
 3px; padding-left: 2px; padding-right: 2px; background-color: #e2e2e2; }

  </style>
</head>
<body style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; line-br=
eak: after-white-space;">Hi All,=20
<div>I'm coming from hours of debugging a SQL trigger that wasn't working p=
roperly. After a beautiful headache and infinite hours of documentation rea=
ding I've found out that something doesn't work as I would expect.=20
</div><div><br /></div><div>Most programming languages return "true" when t=
wo null values are compared, and false when, being the two values nullable, =
one of them is null and the other one isn't.=20
</div><div>Any developer coming from Python, Javascript, PHP (and many more=
) would expected 'example'=3D null to return false, whilst SQL thanks to 3V=
L returns you a gentle =C2=AF\_(=E3=83=84)_/=C2=AF . Not a true, not a fals=
e, just nothing - in a boolean statement.=20
</div><div><br /></div><div>Python3:
</div><div>&gt;&gt;&gt;=C2=A0"example" =3D=3D None
</div><div>False
</div><div>&gt;&gt;&gt;=C2=A0None =3D=3D None
</div><div>True
</div><div><br /></div><div>NodeJS:
</div><div>&gt;=C2=A0"example" =3D=3D null
</div><div>false
</div><div>&gt;=C2=A0null =3D=3D null
</div><div>true
</div><div><br /></div><div>PHP 8:
</div><div>&gt;=C2=A0var_dump("example" =3D=3D null);
</div><div>bool(false)
</div><div>&gt;=C2=A0var_dump(null =3D=3D null);
</div><div>bool(true)
</div><div><br /></div><div>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 c=
hange the status-quo even if it made sense, as it would break many thousand=
s of projects.=20
</div><div><br /></div><div>For that reason, I'd just like to improve the d=
ocumentation to add at least a note about "hey, this won't work as you migh=
t expect, because it works in a different way than 99% of programming langu=
ages out there.".
</div><div>I've tried to understand how to submit my proposal for the docum=
entation improvements, but it's way harder than what my brain can handle wi=
th the current headache caused by this stuff, I've attached a git patch to=
 this email in case it's useful.</div><div><br /></div><div>Content: "
</div><div>PostgreSQL follows SQL's 3VL, due to that some comparisons regar=
ding NULL values may not work as you might expect.=20
</div><div>As an example, two nullable columns that contain NULL, when comp=
ared using the OPERATOR =3D, will return nothing instead of TRUE like your=
 programming language may do. In this case, only 'IS NOT DISTINCT FROM' woul=
d return the result you expect.
</div><div>"</div><div><br /></div><div>Kind regards,=20
</div><div>Luca=C2=A0</div></body></html>
--------=_MB2056DE52-0708-40B4-A196-1530C11F497E--

--------=_MBE7AEBD0A-B7B9-41D2-BDDA-F4B500AEB45E
Content-Type: application/octet-stream;
 name=0001-Improved-documentation-for-Syntax-s.-Operators-Prece.patch
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename=0001-Improved-documentation-for-Syntax-s.-Operators-Prece.patch

RnJvbSA2NjJkNjg0ZDBlZGRmZmIzODEzNjllOTA5ZDhjNzBiZDcyMzAyNWFjIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMdWNhIERhbWV0dG8gPHBvc3RncmVzcWxtYWlsaW5nQGRhbWV0
dG9sdWNhLmNvbT4KRGF0ZTogU3VuLCAxNSBEZWMgMjAyNCAxODozMzoxMyArMDEwMApTdWJqZWN0
OiBbUEFUQ0hdIEltcHJvdmVkIGRvY3VtZW50YXRpb24gZm9yIFN5bnRheCwgcy4gT3BlcmF0b3Jz
IFByZWNlZGVuY2UKCi0tLQogZG9jL3NyYy9zZ21sL3N5bnRheC5zZ21sIHwgMTIgKysrKysrKysr
KystCiAxIGZpbGUgY2hhbmdlZCwgMTEgaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlvbigtKQoKZGlm
ZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9zeW50YXguc2dtbCBiL2RvYy9zcmMvc2dtbC9zeW50YXgu
c2dtbAppbmRleCA5MTYxODlhN2Q2Li43OWYzYjljZTVmIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3Nn
bWwvc3ludGF4LnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3N5bnRheC5zZ21sCkBAIC0xMTExLDcg
KzExMTEsNyBAQCBDQVNUICggJzxyZXBsYWNlYWJsZT5zdHJpbmc8L3JlcGxhY2VhYmxlPicgQVMg
PHJlcGxhY2VhYmxlPnR5cGU8L3JlcGxhY2VhYmxlPiApCiAgICAgICAgPGVudHJ5Pjx0b2tlbj4m
bHQ7PC90b2tlbj4gPHRva2VuPiZndDs8L3Rva2VuPiA8dG9rZW4+PTwvdG9rZW4+IDx0b2tlbj4m
bHQ7PTwvdG9rZW4+IDx0b2tlbj4mZ3Q7PTwvdG9rZW4+IDx0b2tlbj4mbHQ7Jmd0OzwvdG9rZW4+
CiA8L2VudHJ5PgogICAgICAgIDxlbnRyeT48L2VudHJ5PgotICAgICAgIDxlbnRyeT5jb21wYXJp
c29uIG9wZXJhdG9yczwvZW50cnk+CisgICAgICAgPGVudHJ5PmNvbXBhcmlzb24gb3BlcmF0b3Jz
ICo8L2VudHJ5PgogICAgICAgPC9yb3c+CiAKICAgICAgIDxyb3c+CkBAIC0xMTUxLDYgKzExNTEs
MTYgQEAgQ0FTVCAoICc8cmVwbGFjZWFibGU+c3RyaW5nPC9yZXBsYWNlYWJsZT4nIEFTIDxyZXBs
YWNlYWJsZT50eXBlPC9yZXBsYWNlYWJsZT4gKQogICAgIG1hdHRlciB3aGF0IHlvdXJzIGRvZXMu
CiAgICA8L3BhcmE+CiAKKyAgIDxwYXJhPgorICAgICogTm90ZSB0aGF0IDxwcm9kdWN0bmFtZT5Q
b3N0Z3JlU1FMPC9wcm9kdWN0bmFtZT4gZm9sbG93cyBTUUwncyAzVkwsCisgICAgZHVlIHRvIHRo
YXQgc29tZSBjb21wYXJpc29ucyByZWdhcmRpbmcgTlVMTCB2YWx1ZXMgbWF5IG5vdCB3b3JrIGFz
CisgICAgeW91IG1pZ2h0IGV4cGVjdC4KKyAgICBBcyBhbiBleGFtcGxlLCB0d28gbnVsbGFibGUg
Y29sdW1ucyB0aGF0IGNvbnRhaW4gTlVMTCwgd2hlbiBjb21wYXJlZCB1c2luZworICAgIHRoZSA8
bGl0ZXJhbD5PUEVSQVRPUjwvbGl0ZXJhbD4gPHRva2VuPj08L3Rva2VuPiwgd2lsbCByZXR1cm4g
bm90aGluZworICAgIGluc3RlYWQgb2YgVFJVRSBsaWtlIHlvdXIgcHJvZ3JhbW1pbmcgbGFuZ3Vh
Z2UgbWF5IGRvLgorICAgIEluIHRoaXMgY2FzZSwgb25seSAnSVMgTk9UIERJU1RJTkNUIEZST00n
IHdvdWxkIHJldHVybiB0aGUgcmVzdWx0IHlvdSBleHBlY3QuCisgICA8L3BhcmE+CisgICAKICAg
IDxwYXJhPgogICAgIFdoZW4gYSBzY2hlbWEtcXVhbGlmaWVkIG9wZXJhdG9yIG5hbWUgaXMgdXNl
ZCBpbiB0aGUKICAgICA8bGl0ZXJhbD5PUEVSQVRPUjwvbGl0ZXJhbD4gc3ludGF4LCBhcyBmb3Ig
ZXhhbXBsZSBpbjoKLS0gCjIuMzkuNSAoQXBwbGUgR2l0LTE1NCkKCg==
--------=_MBE7AEBD0A-B7B9-41D2-BDDA-F4B500AEB45E--