Re: Inconsistencies around Composite Row nullness
| От | David G. Johnston |
|---|---|
| Тема | Re: Inconsistencies around Composite Row nullness |
| Дата | |
| Msg-id | CAKFQuwYOtnVh0GAn0kHFOmarHii2fc1+Nd_0Q48Y-r8UC42ZkA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Inconsistencies around Composite Row nullness (Chris Hanks <christopher.m.hanks@gmail.com>) |
| Список | pgsql-bugs |
On Sunday, November 2, 2025, Chris Hanks <christopher.m.hanks@gmail.com> wrote:
Hello -I've experienced some logically inconsistent query output on my local Postgres instance, version string: PostgreSQL 18.0 (Homebrew) on aarch64-apple-darwin25.0.0, compiled by Apple clang version 17.0.0 (clang-1700.3.19.1), 64-bitI also reproduced it on the most recent Postgres version available at db-fiddle.com, version string: PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bitThis first statement resolves, reasonably, to NULL:SELECT ROW(NULL::integer, 2) = ROW(NULL::integer, 2)
Yes, ROW constructed values within an equality resolve using SQL row constructor comparison rules.
This next statement resolves to ROW(NULL, 2):SELECT coalesce(ROW(NULL::integer, 2), ROW(1, 2))
This statement is technically impossible - nothing resolves to “ROW(…)” - the fact that ROW (a row constructor) is involved is erased when passing the result of the expression through a function such that a plain composite/record is produced. It is necessary, for the rest of the system to function correctly, that records are comparable using (null equals null => true) semantics (is distinct; composite type comparison).
These final two statements each resolve to true, which is inconsistent with the previous statements (each should resolve to NULL):SELECT coalesce(ROW(NULL::integer, 2), ROW(1, 2)) = ROW(NULL::integer, 2)SELECT coalesce(ROW(NULL::integer, 2)) = ROW(NULL::integer, 2)
See specifically the commentary in row constructor comparison 9.25.5 and composite type comparison 9.25.6 in the documentation.
There is a patch to further expound/consolidate discussion on this topic (null handling in PostgreSQL) presently awaiting committer attention.
David J.
В списке pgsql-bugs по дате отправления: