public inbox for [email protected]
help / color / mirror / Atom feedBUG #19524: NaN handling in btree_gist's float4/float8 opclasses
10+ messages / 3 participants
[nested] [flat]
* BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
@ 2026-06-28 02:14 Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Kim @ 2026-06-28 02:14 UTC (permalink / raw)
To: [email protected]
Hi all,
I have a patch for this and `make check` passes (core + all contrib, plus
new btree_gist NaN cases covering the three scenarios in the report).
Posting here first in case someone is already further along — if not,
I'll send it to pgsql-hackers and register it in the open CF in a couple
of days.
The fix swaps the five gbt_float{4,8}{gt,ge,eq,le,lt} comparators and
gbt_float{4,8}key_cmp for the NaN-aware float{4,8}_* helpers and
float{4,8}_cmp_internal() from utils/float.h — same total order the
btree opclass already uses. No on-disk change.
Bill
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
@ 2026-06-28 18:54 ` Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Daniel Gustafsson @ 2026-06-28 18:54 UTC (permalink / raw)
To: Bill Kim <[email protected]>; +Cc: [email protected]
> On 28 Jun 2026, at 04:14, Bill Kim <[email protected]> wrote:
>
> Hi all,
>
> I have a patch for this and `make check` passes (core + all contrib, plus
> new btree_gist NaN cases covering the three scenarios in the report).
> Posting here first in case someone is already further along — if not,
> I'll send it to pgsql-hackers and register it in the open CF in a couple
> of days.
Even if someone else is working on it, always feel free to send your patch.
Multiple implementations of a fix is not a problem but an opportunity for
everyone to learn from each other.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
@ 2026-06-29 12:04 ` Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Kim @ 2026-06-29 12:04 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: [email protected]
Hi,
contrib/btree_gist's float4/float8 GiST opclasses compared keys with
raw C operators (==, <, >). Under IEEE 754 every comparison involving
NaN returns false, so GiST disagreed with the regular btree opclass,
which uses float[4|8]_cmp_internal() (all NaNs equal, NaN sorts after
every non-NaN value).
The disagreement was reachable from SQL:
* an index scan on a GiST index over a float column returned no rows
for `WHERE a = 'NaN'`, while a sequential scan returned the NaN row;
* `EXCLUDE USING gist (a WITH =)` accepted two rows whose key was NaN,
because gbt_float8eq(NaN, NaN) was false;
* an RLS predicate `USING (a != 'NaN')` leaked NaN rows on index scan.
The fix swaps the five gbt_float{4,8}{gt,ge,eq,le,lt} comparators and
the picksplit gbt_float{4,8}key_cmp for the NaN-aware
float{4,8}_{gt,ge,eq,le,lt} / float{4,8}_cmp_internal() helpers in
utils/float.h — same total order the btree opclass already uses. No
on-disk format change.
Added regression coverage in contrib/btree_gist/sql/float{4,8}.sql for
the three scenarios above. `make check` is green: core 245/245 +
contrib btree_gist 32/32 + all 48 contrib modules.
Originally reported as BUG #19501 and BUG #19524:
https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org
https://www.postgresql.org/message-id/19524-9559d302c8455664%40postgresql.org
I used an LLM (Claude Code) to help with the analysis and to draft the
patch. I reviewed and tested the diff myself before sending.
Regards,
Bill Kim
2026년 6월 29일 (월) 오전 3:54, Daniel Gustafsson <[email protected]>님이 작성:
> > On 28 Jun 2026, at 04:14, Bill Kim <[email protected]> wrote:
> >
> > Hi all,
> >
> > I have a patch for this and `make check` passes (core + all contrib,
> plus
> > new btree_gist NaN cases covering the three scenarios in the report).
> > Posting here first in case someone is already further along — if not,
> > I'll send it to pgsql-hackers and register it in the open CF in a
> couple
> > of days.
>
> Even if someone else is working on it, always feel free to send your patch.
> Multiple implementations of a fix is not a problem but an opportunity for
> everyone to learn from each other.
>
> --
> Daniel Gustafsson
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
@ 2026-06-29 15:04 ` Tom Lane <[email protected]>
2026-06-29 15:27 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2026-06-29 15:04 UTC (permalink / raw)
To: Bill Kim <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
Bill Kim <[email protected]> writes:
> I used an LLM (Claude Code) to help with the analysis and to draft the
> patch. I reviewed and tested the diff myself before sending.
I don't see any actual patch attached?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
@ 2026-06-29 15:27 ` Bill Kim <[email protected]>
2026-06-29 15:35 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Kim @ 2026-06-29 15:27 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
missed the file and please refer to attached.
2026년 6월 30일 (화) 오전 12:04, Tom Lane <[email protected]>님이 작성:
> Bill Kim <[email protected]> writes:
> > I used an LLM (Claude Code) to help with the analysis and to draft the
> > patch. I reviewed and tested the diff myself before sending.
>
> I don't see any actual patch attached?
>
> regards, tom lane
>
Attachments:
[application/octet-stream] 0001-btree_gist-respect-NaN-ordering-in-float4-float8-opc.patch (12.7K, ../../CAMQXxch6yrJQdeHQK9Mypa9cmDKjQOc5DJ=SL72pom1djL_OLg@mail.gmail.com/3-0001-btree_gist-respect-NaN-ordering-in-float4-float8-opc.patch)
download | inline diff:
From 80d760d268b0e1a72d2a1260ebc81c0876687384 Mon Sep 17 00:00:00 2001
From: Bill Kim <[email protected]>
Date: Sun, 28 Jun 2026 00:19:30 +0900
Subject: [PATCH] btree_gist: respect NaN ordering in float4/float8 opclasses
The float4 and float8 btree_gist opclasses compared keys with raw C
operators (==, <, >). IEEE 754 makes every comparison involving NaN
false, so GiST disagreed with the regular btree opclass, which uses
float[4|8]_cmp_internal() (all NaNs equal, NaN sorts after every
non-NaN value).
The disagreement was reachable from SQL:
* an index scan on a GiST index over a float column returned no rows
for `WHERE a = 'NaN'`, while a sequential scan returned the NaN row;
* `EXCLUDE USING gist (a WITH =)` accepted two rows whose key was NaN,
because gbt_float8eq(NaN, NaN) was false;
* an RLS predicate `USING (a != 'NaN')` leaked NaN rows on index scan.
Replace the five boolean comparators with float[4|8]_{gt,ge,eq,le,lt}
from utils/float.h, and the picksplit comparator with
float[4|8]_cmp_internal(). No on-disk format change.
Add regression coverage for the three scenarios above.
Reported-by: Man Zeng <[email protected]>
Reported-by: Yuelin Wang <[email protected]>
Discussion: https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org
Discussion: https://www.postgresql.org/message-id/19524-9559d302c8455664%40postgresql.org
---
contrib/btree_gist/btree_float4.c | 31 +++++++++-------
contrib/btree_gist/btree_float8.c | 31 +++++++++-------
contrib/btree_gist/expected/float4.out | 51 ++++++++++++++++++++++++++
contrib/btree_gist/expected/float8.out | 51 ++++++++++++++++++++++++++
contrib/btree_gist/sql/float4.sql | 29 +++++++++++++++
contrib/btree_gist/sql/float8.sql | 29 +++++++++++++++
6 files changed, 196 insertions(+), 26 deletions(-)
diff --git a/contrib/btree_gist/btree_float4.c b/contrib/btree_gist/btree_float4.c
index c076918..083c616 100644
--- a/contrib/btree_gist/btree_float4.c
+++ b/contrib/btree_gist/btree_float4.c
@@ -26,30 +26,38 @@ PG_FUNCTION_INFO_V1(gbt_float4_penalty);
PG_FUNCTION_INFO_V1(gbt_float4_same);
PG_FUNCTION_INFO_V1(gbt_float4_sortsupport);
+/*
+ * Use the NaN-aware comparators from utils/float.h. Raw C operators treat
+ * NaN as unordered, which makes the GiST consistent / picksplit logic
+ * disagree with the regular btree opclass: GiST would then miss NaN rows on
+ * index scans, let EXCLUDE constraints accept duplicate NaNs, etc. The
+ * helpers below match btree's total order (all NaNs equal, NaN sorts after
+ * every non-NaN value).
+ */
static bool
gbt_float4gt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) > *((const float4 *) b));
+ return float4_gt(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4ge(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) >= *((const float4 *) b));
+ return float4_ge(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4eq(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) == *((const float4 *) b));
+ return float4_eq(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4le(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) <= *((const float4 *) b));
+ return float4_le(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4lt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) < *((const float4 *) b));
+ return float4_lt(*((const float4 *) a), *((const float4 *) b));
}
static int
@@ -57,16 +65,13 @@ gbt_float4key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
{
float4KEY *ia = (float4KEY *) (((const Nsrt *) a)->t);
float4KEY *ib = (float4KEY *) (((const Nsrt *) b)->t);
+ int res;
- if (ia->lower == ib->lower)
- {
- if (ia->upper == ib->upper)
- return 0;
-
- return (ia->upper > ib->upper) ? 1 : -1;
- }
+ res = float4_cmp_internal(ia->lower, ib->lower);
+ if (res != 0)
+ return res;
- return (ia->lower > ib->lower) ? 1 : -1;
+ return float4_cmp_internal(ia->upper, ib->upper);
}
static float8
diff --git a/contrib/btree_gist/btree_float8.c b/contrib/btree_gist/btree_float8.c
index d7386e8..5c80948 100644
--- a/contrib/btree_gist/btree_float8.c
+++ b/contrib/btree_gist/btree_float8.c
@@ -27,30 +27,38 @@ PG_FUNCTION_INFO_V1(gbt_float8_same);
PG_FUNCTION_INFO_V1(gbt_float8_sortsupport);
+/*
+ * Use the NaN-aware comparators from utils/float.h. Raw C operators treat
+ * NaN as unordered, which makes the GiST consistent / picksplit logic
+ * disagree with the regular btree opclass: GiST would then miss NaN rows on
+ * index scans, let EXCLUDE constraints accept duplicate NaNs, etc. The
+ * helpers below match btree's total order (all NaNs equal, NaN sorts after
+ * every non-NaN value).
+ */
static bool
gbt_float8gt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) > *((const float8 *) b));
+ return float8_gt(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8ge(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) >= *((const float8 *) b));
+ return float8_ge(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8eq(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) == *((const float8 *) b));
+ return float8_eq(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8le(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) <= *((const float8 *) b));
+ return float8_le(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8lt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) < *((const float8 *) b));
+ return float8_lt(*((const float8 *) a), *((const float8 *) b));
}
static int
@@ -58,16 +66,13 @@ gbt_float8key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
{
float8KEY *ia = (float8KEY *) (((const Nsrt *) a)->t);
float8KEY *ib = (float8KEY *) (((const Nsrt *) b)->t);
+ int res;
- if (ia->lower == ib->lower)
- {
- if (ia->upper == ib->upper)
- return 0;
-
- return (ia->upper > ib->upper) ? 1 : -1;
- }
+ res = float8_cmp_internal(ia->lower, ib->lower);
+ if (res != 0)
+ return res;
- return (ia->lower > ib->lower) ? 1 : -1;
+ return float8_cmp_internal(ia->upper, ib->upper);
}
static float8
diff --git a/contrib/btree_gist/expected/float4.out b/contrib/btree_gist/expected/float4.out
index dfe7320..3109ca3 100644
--- a/contrib/btree_gist/expected/float4.out
+++ b/contrib/btree_gist/expected/float4.out
@@ -89,3 +89,54 @@ SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
-158.17741 | 20.822586
(3 rows)
+-- NaN handling: GiST should agree with the regular btree opclass
+-- (NaN equals NaN, NaN sorts after any non-NaN value).
+CREATE TABLE float4nan (a float4);
+INSERT INTO float4nan VALUES (1.0), (2.0), ('NaN'), (3.0);
+CREATE INDEX float4nan_idx ON float4nan USING gist (a);
+SET enable_seqscan = on;
+SET enable_indexscan = off;
+SET enable_bitmapscan = off;
+SELECT count(*) FROM float4nan WHERE a = 'NaN'::float4;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM float4nan WHERE a > 1.0::float4;
+ count
+-------
+ 3
+(1 row)
+
+SET enable_seqscan = off;
+SET enable_indexscan = on;
+SET enable_bitmapscan = on;
+SELECT count(*) FROM float4nan WHERE a = 'NaN'::float4;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM float4nan WHERE a > 1.0::float4;
+ count
+-------
+ 3
+(1 row)
+
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float4excl (a float4, EXCLUDE USING gist (a WITH =));
+INSERT INTO float4excl VALUES ('NaN'::float4);
+INSERT INTO float4excl VALUES ('NaN'::float4); -- expect: violates EXCLUDE
+ERROR: conflicting key value violates exclusion constraint "float4excl_a_excl"
+DETAIL: Key (a)=(NaN) conflicts with existing key (a)=(NaN).
+SELECT count(*) FROM float4excl;
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/contrib/btree_gist/expected/float8.out b/contrib/btree_gist/expected/float8.out
index ebd0ef3..02a616e 100644
--- a/contrib/btree_gist/expected/float8.out
+++ b/contrib/btree_gist/expected/float8.out
@@ -89,3 +89,54 @@ SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
-1769.73634 | 120.26366000000007
(3 rows)
+-- NaN handling: GiST should agree with the regular btree opclass
+-- (NaN equals NaN, NaN sorts after any non-NaN value).
+CREATE TABLE float8nan (a float8);
+INSERT INTO float8nan VALUES (1.0), (2.0), ('NaN'), (3.0);
+CREATE INDEX float8nan_idx ON float8nan USING gist (a);
+SET enable_seqscan = on;
+SET enable_indexscan = off;
+SET enable_bitmapscan = off;
+SELECT count(*) FROM float8nan WHERE a = 'NaN'::float8;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM float8nan WHERE a > 1.0::float8;
+ count
+-------
+ 3
+(1 row)
+
+SET enable_seqscan = off;
+SET enable_indexscan = on;
+SET enable_bitmapscan = on;
+SELECT count(*) FROM float8nan WHERE a = 'NaN'::float8;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM float8nan WHERE a > 1.0::float8;
+ count
+-------
+ 3
+(1 row)
+
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float8excl (a float8, EXCLUDE USING gist (a WITH =));
+INSERT INTO float8excl VALUES ('NaN'::float8);
+INSERT INTO float8excl VALUES ('NaN'::float8); -- expect: violates EXCLUDE
+ERROR: conflicting key value violates exclusion constraint "float8excl_a_excl"
+DETAIL: Key (a)=(NaN) conflicts with existing key (a)=(NaN).
+SELECT count(*) FROM float8excl;
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/contrib/btree_gist/sql/float4.sql b/contrib/btree_gist/sql/float4.sql
index 3da1ce9..7c95e7f 100644
--- a/contrib/btree_gist/sql/float4.sql
+++ b/contrib/btree_gist/sql/float4.sql
@@ -35,3 +35,32 @@ SELECT count(*) FROM float4tmp WHERE a > -179.0::float4;
EXPLAIN (COSTS OFF)
SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
+
+-- NaN handling: GiST should agree with the regular btree opclass
+-- (NaN equals NaN, NaN sorts after any non-NaN value).
+CREATE TABLE float4nan (a float4);
+INSERT INTO float4nan VALUES (1.0), (2.0), ('NaN'), (3.0);
+CREATE INDEX float4nan_idx ON float4nan USING gist (a);
+
+SET enable_seqscan = on;
+SET enable_indexscan = off;
+SET enable_bitmapscan = off;
+SELECT count(*) FROM float4nan WHERE a = 'NaN'::float4;
+SELECT count(*) FROM float4nan WHERE a > 1.0::float4;
+
+SET enable_seqscan = off;
+SET enable_indexscan = on;
+SET enable_bitmapscan = on;
+SELECT count(*) FROM float4nan WHERE a = 'NaN'::float4;
+SELECT count(*) FROM float4nan WHERE a > 1.0::float4;
+
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float4excl (a float4, EXCLUDE USING gist (a WITH =));
+INSERT INTO float4excl VALUES ('NaN'::float4);
+INSERT INTO float4excl VALUES ('NaN'::float4); -- expect: violates EXCLUDE
+SELECT count(*) FROM float4excl;
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/contrib/btree_gist/sql/float8.sql b/contrib/btree_gist/sql/float8.sql
index e1e819b..eeed36b 100644
--- a/contrib/btree_gist/sql/float8.sql
+++ b/contrib/btree_gist/sql/float8.sql
@@ -35,3 +35,32 @@ SELECT count(*) FROM float8tmp WHERE a > -1890.0::float8;
EXPLAIN (COSTS OFF)
SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
+
+-- NaN handling: GiST should agree with the regular btree opclass
+-- (NaN equals NaN, NaN sorts after any non-NaN value).
+CREATE TABLE float8nan (a float8);
+INSERT INTO float8nan VALUES (1.0), (2.0), ('NaN'), (3.0);
+CREATE INDEX float8nan_idx ON float8nan USING gist (a);
+
+SET enable_seqscan = on;
+SET enable_indexscan = off;
+SET enable_bitmapscan = off;
+SELECT count(*) FROM float8nan WHERE a = 'NaN'::float8;
+SELECT count(*) FROM float8nan WHERE a > 1.0::float8;
+
+SET enable_seqscan = off;
+SET enable_indexscan = on;
+SET enable_bitmapscan = on;
+SELECT count(*) FROM float8nan WHERE a = 'NaN'::float8;
+SELECT count(*) FROM float8nan WHERE a > 1.0::float8;
+
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float8excl (a float8, EXCLUDE USING gist (a WITH =));
+INSERT INTO float8excl VALUES ('NaN'::float8);
+INSERT INTO float8excl VALUES ('NaN'::float8); -- expect: violates EXCLUDE
+SELECT count(*) FROM float8excl;
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
--
2.52.0
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:27 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
@ 2026-06-29 15:35 ` Tom Lane <[email protected]>
2026-06-29 15:50 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2026-06-29 15:35 UTC (permalink / raw)
To: Bill Kim <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
Bill Kim <[email protected]> writes:
> missed the file and please refer to attached.
OK, thanks. I didn't go over this in detail yet, but at first
glance it looks reasonable.
What I am wondering right now is whether it's better to back-patch
this or leave well enough alone. Given that we've now had two
independent complaints, maybe there are enough people using btree_gist
with NaNs to justify a back-patch, even though it'd force them to
reindex.
In any case, sneaking it into v19 seems reasonable.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:27 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:35 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
@ 2026-06-29 15:50 ` Bill Kim <[email protected]>
2026-06-29 23:08 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Kim @ 2026-06-29 15:50 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
Symptoms are silent wrong answers + EXCLUDE bypass leans me
toward back-patching despite the reindex cost.
Happy with whatever call you make.
2026년 6월 30일 (화) 오전 12:35, Tom Lane <[email protected]>님이 작성:
> Bill Kim <[email protected]> writes:
> > missed the file and please refer to attached.
>
> OK, thanks. I didn't go over this in detail yet, but at first
> glance it looks reasonable.
>
> What I am wondering right now is whether it's better to back-patch
> this or leave well enough alone. Given that we've now had two
> independent complaints, maybe there are enough people using btree_gist
> with NaNs to justify a back-patch, even though it'd force them to
> reindex.
>
> In any case, sneaking it into v19 seems reasonable.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:27 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:35 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:50 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
@ 2026-06-29 23:08 ` Tom Lane <[email protected]>
2026-07-01 13:38 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2026-06-29 23:08 UTC (permalink / raw)
To: Bill Kim <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
I studied this and decided that it only partially fixes the problem.
Yes, we've got to fix the comparison functions, but we also have to
fix the penalty and distance functions. As things stand, the penalty
functions will probably return NaN for any input including a NaN,
which gistpenalty will clamp to zero, which we do not want because
it'll basically break all decisions about where to put things,
leading to a seriously inefficient index. (Maybe gistpenalty should
do something else with a NaN, but I'm hesitant to touch that right
now.) The penalty logic is also under the misapprehension that
multiplying everything by 0.49 keeps it from having to worry about
overflows; that won't fix things for infinities. The distance
functions will do the wrong things for NaN as well, probably breaking
any KNN search that happens across a NaN index entry.
So I fixed all that, and then was dismayed to find that the new
penalty logic wasn't reached at all in the regression tests, per code
coverage testing. We don't compute any penalties during GiST index
build unless it's a multicolumn index, so most of the per-datatype
tests aren't reaching that. It didn't seem like adding more index
entries partway through the test would fit very well into the
structure of float[48].sql, so instead I added simple tests of
two-column float indexes to improve the coverage. Also, I thought
we should test this logic by adding NaN (and infinities for good
measure) to the initial data load, rather than creating a new small
table which would only exercise the logic very minimally.
At the moment I'm leaning to back-patching this. We'd have to
release-note the need to reindex any btree_gist indexes containing
NaNs, but we've done similar things many times before.
regards, tom lane
Attachments:
[text/x-diff] v2-0001-btree_gist-fix-NaN-handling-in-float4-float8-opcl.patch (26.0K, ../../[email protected]/2-v2-0001-btree_gist-fix-NaN-handling-in-float4-float8-opcl.patch)
download | inline diff:
From 50ce44820077a5a25c9d04c2f39a0c8479b1dff5 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Mon, 29 Jun 2026 18:35:23 -0400
Subject: [PATCH v2] btree_gist: fix NaN handling in float4/float8 opclasses.
The float4 and float8 btree_gist opclasses compared keys with raw C
operators (==, <, >). IEEE 754 makes every comparison involving NaN
false, so GiST disagreed with the regular float comparison operators
and with the btree opclass, which uses float[4|8]_cmp_internal()
(so that all NaNs are equal and NaN sorts after every non-NaN value).
In addition, the penalty and distance functions were not careful
about NaNs, and the penalty functions could also misbehave for IEEE
infinities. Wrong answers from the penalty functions would probably
do no more than make the index non-optimal, but the distance mistakes
were visible from SQL.
To fix, make the comparison functions rely on the same NaN-aware
comparison functions the core code uses, and rewrite the penalty
and distance functions to follow the rules that NaNs are equal
but maximally far away from non-NaNs. The penalty_num() code was
formerly shared between integral and float cases, but I chose to make
two copies so that the integral cases are not saddled with the extra
logic for NaNs and infinities/overflows. I also rewrote it as static
inline functions instead of an unreadable and uncommented macro.
The float penalty functions were previously unreached by the
regression tests, so add new test cases to exercise them.
There's no on-disk format change, but users who have NaN entries
in a btree_gist index would be well advised to reindex it.
Bug: #19501
Bug: #19524
Reported-by: Man Zeng <[email protected]>
Reported-by: Yuelin Wang <[email protected]>
Author: Bill Kim <[email protected]>
Co-authored-by: Tom Lane <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Discussion: https://postgr.es/m/[email protected]
Discussion: https://postgr.es/m/CAMQXxcgbtD2LXfX0tpgvOizxP-XxrCHV2ZDy4By_TZnJMsxXWQ@mail.gmail.com
Backpatch-through: 14
---
contrib/btree_gist/btree_float4.c | 59 ++++++++---
contrib/btree_gist/btree_float8.c | 51 ++++++---
contrib/btree_gist/btree_utils_num.h | 131 +++++++++++++++++++++---
contrib/btree_gist/data/float4.data | 3 +
contrib/btree_gist/data/float8.data | 3 +
contrib/btree_gist/expected/float4.out | 51 +++++++--
contrib/btree_gist/expected/float8.out | 51 +++++++--
contrib/btree_gist/expected/numeric.out | 48 ++++-----
contrib/btree_gist/sql/float4.sql | 17 +++
contrib/btree_gist/sql/float8.sql | 17 +++
10 files changed, 345 insertions(+), 86 deletions(-)
diff --git a/contrib/btree_gist/btree_float4.c b/contrib/btree_gist/btree_float4.c
index c076918fd48..04868e81a0e 100644
--- a/contrib/btree_gist/btree_float4.c
+++ b/contrib/btree_gist/btree_float4.c
@@ -26,30 +26,36 @@ PG_FUNCTION_INFO_V1(gbt_float4_penalty);
PG_FUNCTION_INFO_V1(gbt_float4_same);
PG_FUNCTION_INFO_V1(gbt_float4_sortsupport);
+/*
+ * Use the NaN-aware comparators from utils/float.h, so that our results
+ * will agree with standard btree indexes. Note that penalty and distance
+ * functions below must also cope with NaNs, in particular with the policy
+ * that all NaNs are equal.
+ */
static bool
gbt_float4gt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) > *((const float4 *) b));
+ return float4_gt(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4ge(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) >= *((const float4 *) b));
+ return float4_ge(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4eq(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) == *((const float4 *) b));
+ return float4_eq(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4le(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) <= *((const float4 *) b));
+ return float4_le(*((const float4 *) a), *((const float4 *) b));
}
static bool
gbt_float4lt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float4 *) a) < *((const float4 *) b));
+ return float4_lt(*((const float4 *) a), *((const float4 *) b));
}
static int
@@ -57,22 +63,33 @@ gbt_float4key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
{
float4KEY *ia = (float4KEY *) (((const Nsrt *) a)->t);
float4KEY *ib = (float4KEY *) (((const Nsrt *) b)->t);
+ int res;
- if (ia->lower == ib->lower)
- {
- if (ia->upper == ib->upper)
- return 0;
-
- return (ia->upper > ib->upper) ? 1 : -1;
- }
-
- return (ia->lower > ib->lower) ? 1 : -1;
+ res = float4_cmp_internal(ia->lower, ib->lower);
+ if (res != 0)
+ return res;
+ return float4_cmp_internal(ia->upper, ib->upper);
}
static float8
gbt_float4_dist(const void *a, const void *b, FmgrInfo *flinfo)
{
- return GET_FLOAT_DISTANCE(float4, a, b);
+ float8 arg1 = *(const float4 *) a;
+ float8 arg2 = *(const float4 *) b;
+ float8 r;
+
+ r = arg1 - arg2;
+ /* needn't consider isinf case here, must be due to input infinity */
+ if (unlikely(isnan(r)))
+ {
+ if (isnan(arg1) && isnan(arg2))
+ r = 0.0; /* treat NaNs as equal */
+ else if (isnan(arg1) || isnan(arg2))
+ r = get_float8_infinity(); /* max dist for NaN vs non-NaN */
+ else
+ r = 0.0; /* must be Inf - Inf case */
+ }
+ return fabs(r);
}
@@ -102,7 +119,15 @@ float4_dist(PG_FUNCTION_ARGS)
r = a - b;
if (unlikely(isinf(r)) && !isinf(a) && !isinf(b))
float_overflow_error();
-
+ if (unlikely(isnan(r)))
+ {
+ if (isnan(a) && isnan(b))
+ r = 0.0; /* treat NaNs as equal */
+ else if (isnan(a) || isnan(b))
+ r = get_float4_infinity(); /* max dist for NaN vs non-NaN */
+ else
+ r = 0.0; /* must be Inf - Inf case */
+ }
PG_RETURN_FLOAT4(fabsf(r));
}
@@ -186,7 +211,7 @@ gbt_float4_penalty(PG_FUNCTION_ARGS)
float4KEY *newentry = (float4KEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(1))->key);
float *result = (float *) PG_GETARG_POINTER(2);
- penalty_num(result, origentry->lower, origentry->upper, newentry->lower, newentry->upper);
+ float_penalty_num(result, origentry->lower, origentry->upper, newentry->lower, newentry->upper);
PG_RETURN_POINTER(result);
}
diff --git a/contrib/btree_gist/btree_float8.c b/contrib/btree_gist/btree_float8.c
index d7386e885a2..2771e8e0f7e 100644
--- a/contrib/btree_gist/btree_float8.c
+++ b/contrib/btree_gist/btree_float8.c
@@ -27,30 +27,36 @@ PG_FUNCTION_INFO_V1(gbt_float8_same);
PG_FUNCTION_INFO_V1(gbt_float8_sortsupport);
+/*
+ * Use the NaN-aware comparators from utils/float.h, so that our results
+ * will agree with standard btree indexes. Note that penalty and distance
+ * functions below must also cope with NaNs, in particular with the policy
+ * that all NaNs are equal.
+ */
static bool
gbt_float8gt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) > *((const float8 *) b));
+ return float8_gt(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8ge(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) >= *((const float8 *) b));
+ return float8_ge(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8eq(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) == *((const float8 *) b));
+ return float8_eq(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8le(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) <= *((const float8 *) b));
+ return float8_le(*((const float8 *) a), *((const float8 *) b));
}
static bool
gbt_float8lt(const void *a, const void *b, FmgrInfo *flinfo)
{
- return (*((const float8 *) a) < *((const float8 *) b));
+ return float8_lt(*((const float8 *) a), *((const float8 *) b));
}
static int
@@ -58,16 +64,12 @@ gbt_float8key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
{
float8KEY *ia = (float8KEY *) (((const Nsrt *) a)->t);
float8KEY *ib = (float8KEY *) (((const Nsrt *) b)->t);
+ int res;
- if (ia->lower == ib->lower)
- {
- if (ia->upper == ib->upper)
- return 0;
-
- return (ia->upper > ib->upper) ? 1 : -1;
- }
-
- return (ia->lower > ib->lower) ? 1 : -1;
+ res = float8_cmp_internal(ia->lower, ib->lower);
+ if (res != 0)
+ return res;
+ return float8_cmp_internal(ia->upper, ib->upper);
}
static float8
@@ -80,6 +82,15 @@ gbt_float8_dist(const void *a, const void *b, FmgrInfo *flinfo)
r = arg1 - arg2;
if (unlikely(isinf(r)) && !isinf(arg1) && !isinf(arg2))
float_overflow_error();
+ if (unlikely(isnan(r)))
+ {
+ if (isnan(arg1) && isnan(arg2))
+ r = 0.0; /* treat NaNs as equal */
+ else if (isnan(arg1) || isnan(arg2))
+ r = get_float8_infinity(); /* max dist for NaN vs non-NaN */
+ else
+ r = 0.0; /* must be Inf - Inf case */
+ }
return fabs(r);
}
@@ -110,7 +121,15 @@ float8_dist(PG_FUNCTION_ARGS)
r = a - b;
if (unlikely(isinf(r)) && !isinf(a) && !isinf(b))
float_overflow_error();
-
+ if (unlikely(isnan(r)))
+ {
+ if (isnan(a) && isnan(b))
+ r = 0.0; /* treat NaNs as equal */
+ else if (isnan(a) || isnan(b))
+ r = get_float8_infinity(); /* max dist for NaN vs non-NaN */
+ else
+ r = 0.0; /* must be Inf - Inf case */
+ }
PG_RETURN_FLOAT8(fabs(r));
}
@@ -194,7 +213,7 @@ gbt_float8_penalty(PG_FUNCTION_ARGS)
float8KEY *newentry = (float8KEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(1))->key);
float *result = (float *) PG_GETARG_POINTER(2);
- penalty_num(result, origentry->lower, origentry->upper, newentry->lower, newentry->upper);
+ float_penalty_num(result, origentry->lower, origentry->upper, newentry->lower, newentry->upper);
PG_RETURN_POINTER(result);
}
diff --git a/contrib/btree_gist/btree_utils_num.h b/contrib/btree_gist/btree_utils_num.h
index 53e477d8b1e..d42d1e61585 100644
--- a/contrib/btree_gist/btree_utils_num.h
+++ b/contrib/btree_gist/btree_utils_num.h
@@ -9,6 +9,7 @@
#include "access/gist.h"
#include "btree_gist.h"
+#include "utils/float.h"
typedef char GBT_NUMKEY;
@@ -58,21 +59,124 @@ typedef struct
/*
- * Note: The factor 0.49 in following macro avoids floating point overflows
+ * Compute penalty for expanding a range olower..oupper to nlower..nupper.
+ *
+ * Although the arguments are declared double, they must not be NaN nor
+ * large enough to risk overflows in the calculations herein. We only
+ * actually use this for integral data types, so there's no hazard.
+ */
+static inline float
+penalty_num_impl(double olower, double oupper,
+ double nlower, double nupper,
+ int natts)
+{
+ float result = 0.0F;
+ double tmp = 0.0;
+
+ /* Add penalty for expanding upper bound */
+ if (nupper > oupper)
+ tmp += nupper - oupper;
+ /* Add penalty for expanding lower bound */
+ if (olower > nlower)
+ tmp += olower - nlower;
+ if (tmp > 0.0)
+ {
+ /* Ensure result is non-zero, even if next step underflows to zero */
+ result += FLT_MIN;
+ /* Scale penalty to 0 .. 1 */
+ result += (float) (tmp / (tmp + (oupper - olower)));
+ /* Scale to 0 .. FLT_MAX / (natts + 1) */
+ result *= FLT_MAX / (natts + 1);
+ }
+ return result;
+}
+
+/*
+ * As above, but the input values are float4 or float8, so we must cope
+ * with NaNs, infinities, and overflows.
+ */
+static inline float
+float_penalty_num_impl(double olower, double oupper,
+ double nlower, double nupper,
+ int natts)
+{
+ float result = 0.0F;
+ double tmp = 0.0;
+
+ /* Add penalty for expanding upper bound */
+ if (float8_gt(nupper, oupper))
+ {
+ double delta = nupper - oupper;
+
+ if (unlikely(isnan(delta)))
+ {
+ /* oupper couldn't be NaN here, see float8_gt */
+ if (isnan(nupper))
+ delta = FLT_MAX; /* max penalty for NaN vs non-NaN */
+ else
+ delta = 0.0; /* must be Inf - Inf case */
+ }
+ else if (delta > FLT_MAX)
+ delta = FLT_MAX; /* clamp to FLT_MAX, esp for infinity */
+ tmp += delta;
+ }
+ /* Add penalty for expanding lower bound */
+ if (float8_gt(olower, nlower))
+ {
+ double delta = olower - nlower;
+
+ if (unlikely(isnan(delta)))
+ {
+ /* nlower couldn't be NaN here, see float8_gt */
+ if (isnan(olower))
+ delta = FLT_MAX; /* max penalty for NaN vs non-NaN */
+ else
+ delta = 0.0; /* must be Inf - Inf case */
+ }
+ else if (delta > FLT_MAX)
+ delta = FLT_MAX; /* clamp to FLT_MAX, esp for infinity */
+ tmp += delta;
+ }
+ if (tmp > 0.0)
+ {
+ double delta = oupper - olower;
+
+ /* Clamp delta (the original range size) to 0 .. FLT_MAX */
+ if (unlikely(isnan(delta)))
+ {
+ /* here, we must deal with olower possibly being NaN */
+ if (isnan(oupper) && isnan(olower))
+ delta = 0.0; /* treat NaNs as equal */
+ else if (isnan(oupper) || isnan(olower))
+ delta = FLT_MAX; /* max penalty for NaN vs non-NaN */
+ else
+ delta = 0.0; /* must be Inf - Inf case */
+ }
+ else if (delta > FLT_MAX)
+ delta = FLT_MAX; /* clamp to FLT_MAX, esp for infinity */
+ /* Ensure result is non-zero, even if next step underflows to zero */
+ result += FLT_MIN;
+ /* Scale penalty to 0 .. 1 */
+ result += (float) (tmp / (tmp + delta));
+ /* Scale to 0 .. FLT_MAX / (natts + 1) */
+ result *= FLT_MAX / (natts + 1);
+ }
+ return result;
+}
+
+/*
+ * These macros provide backwards-compatible notation for callers.
*/
#define penalty_num(result,olower,oupper,nlower,nupper) do { \
- double tmp = 0.0F; \
- (*(result)) = 0.0F; \
- if ( (nupper) > (oupper) ) \
- tmp += ( ((double)nupper)*0.49F - ((double)oupper)*0.49F ); \
- if ( (olower) > (nlower) ) \
- tmp += ( ((double)olower)*0.49F - ((double)nlower)*0.49F ); \
- if (tmp > 0.0F) \
- { \
- (*(result)) += FLT_MIN; \
- (*(result)) += (float) ( ((double)(tmp)) / ( (double)(tmp) + ( ((double)(oupper))*0.49F - ((double)(olower))*0.49F ) ) ); \
- (*(result)) *= (FLT_MAX / (((GISTENTRY *) PG_GETARG_POINTER(0))->rel->rd_att->natts + 1)); \
- } \
+ GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0); \
+ *(result) = penalty_num_impl(olower, oupper, nlower, nupper, \
+ entry->rel->rd_att->natts); \
+} while (0)
+
+#define float_penalty_num(result,olower,oupper,nlower,nupper) do { \
+ GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0); \
+ *(result) = float_penalty_num_impl(olower, oupper, nlower, nupper, \
+ entry->rel->rd_att->natts); \
} while (0)
@@ -86,6 +190,7 @@ typedef struct
(ivp)->day * (24.0 * SECS_PER_HOUR) + \
(ivp)->month * (30.0 * SECS_PER_DAY))
+/* This macro is not safe to use with actual float inputs, only integers */
#define GET_FLOAT_DISTANCE(t, arg1, arg2) fabs( ((float8) *((const t *) (arg1))) - ((float8) *((const t *) (arg2))) )
diff --git a/contrib/btree_gist/data/float4.data b/contrib/btree_gist/data/float4.data
index 947955e4680..af7d09f00d6 100644
--- a/contrib/btree_gist/data/float4.data
+++ b/contrib/btree_gist/data/float4.data
@@ -298,6 +298,9 @@
\N
2972.381398
220.199877
+Infinity
+-Infinity
+NaN
3542.561032
-2168.024176
-3305.714558
diff --git a/contrib/btree_gist/data/float8.data b/contrib/btree_gist/data/float8.data
index ff21226e066..b60e22f957f 100644
--- a/contrib/btree_gist/data/float8.data
+++ b/contrib/btree_gist/data/float8.data
@@ -298,6 +298,9 @@
27770.539968
13275.355549
-4267.695804
+Infinity
+-Infinity
+NaN
\N
\N
38915.525185
diff --git a/contrib/btree_gist/expected/float4.out b/contrib/btree_gist/expected/float4.out
index dfe732049e6..a917b79a63b 100644
--- a/contrib/btree_gist/expected/float4.out
+++ b/contrib/btree_gist/expected/float4.out
@@ -5,13 +5,13 @@ SET enable_seqscan=on;
SELECT count(*) FROM float4tmp WHERE a < -179.0;
count
-------
- 244
+ 245
(1 row)
SELECT count(*) FROM float4tmp WHERE a <= -179.0;
count
-------
- 245
+ 246
(1 row)
SELECT count(*) FROM float4tmp WHERE a = -179.0;
@@ -23,13 +23,13 @@ SELECT count(*) FROM float4tmp WHERE a = -179.0;
SELECT count(*) FROM float4tmp WHERE a >= -179.0;
count
-------
- 303
+ 305
(1 row)
SELECT count(*) FROM float4tmp WHERE a > -179.0;
count
-------
- 302
+ 304
(1 row)
SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
@@ -45,13 +45,13 @@ SET enable_seqscan=off;
SELECT count(*) FROM float4tmp WHERE a < -179.0::float4;
count
-------
- 244
+ 245
(1 row)
SELECT count(*) FROM float4tmp WHERE a <= -179.0::float4;
count
-------
- 245
+ 246
(1 row)
SELECT count(*) FROM float4tmp WHERE a = -179.0::float4;
@@ -63,13 +63,13 @@ SELECT count(*) FROM float4tmp WHERE a = -179.0::float4;
SELECT count(*) FROM float4tmp WHERE a >= -179.0::float4;
count
-------
- 303
+ 305
(1 row)
SELECT count(*) FROM float4tmp WHERE a > -179.0::float4;
count
-------
- 302
+ 304
(1 row)
EXPLAIN (COSTS OFF)
@@ -89,3 +89,38 @@ SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
-158.17741 | 20.822586
(3 rows)
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float4excl (a float4, EXCLUDE USING gist (a WITH =));
+INSERT INTO float4excl VALUES ('NaN'::float4);
+INSERT INTO float4excl VALUES ('NaN'::float4); -- expect: violates EXCLUDE
+ERROR: conflicting key value violates exclusion constraint "float4excl_a_excl"
+DETAIL: Key (a)=(NaN) conflicts with existing key (a)=(NaN).
+SELECT count(*) FROM float4excl;
+ count
+-------
+ 1
+(1 row)
+
+-- Test double-column index
+CREATE INDEX float4idx2 ON float4tmp USING gist ( a, abs(a) );
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM float4tmp WHERE abs(a) = 179.0::float4;
+ QUERY PLAN
+--------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on float4tmp
+ Recheck Cond: (abs(a) = '179'::real)
+ -> Bitmap Index Scan on float4idx2
+ Index Cond: (abs(a) = '179'::real)
+(5 rows)
+
+SELECT count(*) FROM float4tmp WHERE abs(a) = 179.0::float4;
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/contrib/btree_gist/expected/float8.out b/contrib/btree_gist/expected/float8.out
index ebd0ef3d689..194bd210ac6 100644
--- a/contrib/btree_gist/expected/float8.out
+++ b/contrib/btree_gist/expected/float8.out
@@ -5,13 +5,13 @@ SET enable_seqscan=on;
SELECT count(*) FROM float8tmp WHERE a < -1890.0;
count
-------
- 237
+ 238
(1 row)
SELECT count(*) FROM float8tmp WHERE a <= -1890.0;
count
-------
- 238
+ 239
(1 row)
SELECT count(*) FROM float8tmp WHERE a = -1890.0;
@@ -23,13 +23,13 @@ SELECT count(*) FROM float8tmp WHERE a = -1890.0;
SELECT count(*) FROM float8tmp WHERE a >= -1890.0;
count
-------
- 307
+ 309
(1 row)
SELECT count(*) FROM float8tmp WHERE a > -1890.0;
count
-------
- 306
+ 308
(1 row)
SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
@@ -45,13 +45,13 @@ SET enable_seqscan=off;
SELECT count(*) FROM float8tmp WHERE a < -1890.0::float8;
count
-------
- 237
+ 238
(1 row)
SELECT count(*) FROM float8tmp WHERE a <= -1890.0::float8;
count
-------
- 238
+ 239
(1 row)
SELECT count(*) FROM float8tmp WHERE a = -1890.0::float8;
@@ -63,13 +63,13 @@ SELECT count(*) FROM float8tmp WHERE a = -1890.0::float8;
SELECT count(*) FROM float8tmp WHERE a >= -1890.0::float8;
count
-------
- 307
+ 309
(1 row)
SELECT count(*) FROM float8tmp WHERE a > -1890.0::float8;
count
-------
- 306
+ 308
(1 row)
EXPLAIN (COSTS OFF)
@@ -89,3 +89,38 @@ SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
-1769.73634 | 120.26366000000007
(3 rows)
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float8excl (a float8, EXCLUDE USING gist (a WITH =));
+INSERT INTO float8excl VALUES ('NaN'::float8);
+INSERT INTO float8excl VALUES ('NaN'::float8); -- expect: violates EXCLUDE
+ERROR: conflicting key value violates exclusion constraint "float8excl_a_excl"
+DETAIL: Key (a)=(NaN) conflicts with existing key (a)=(NaN).
+SELECT count(*) FROM float8excl;
+ count
+-------
+ 1
+(1 row)
+
+-- Test double-column index
+CREATE INDEX float8idx2 ON float8tmp USING gist ( a, abs(a) );
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM float8tmp WHERE abs(a) = 1890.0::float8;
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on float8tmp
+ Recheck Cond: (abs(a) = '1890'::double precision)
+ -> Bitmap Index Scan on float8idx2
+ Index Cond: (abs(a) = '1890'::double precision)
+(5 rows)
+
+SELECT count(*) FROM float8tmp WHERE abs(a) = 1890.0::float8;
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/contrib/btree_gist/expected/numeric.out b/contrib/btree_gist/expected/numeric.out
index ae839b8ec83..34c1e568063 100644
--- a/contrib/btree_gist/expected/numeric.out
+++ b/contrib/btree_gist/expected/numeric.out
@@ -7,13 +7,13 @@ SET enable_seqscan=on;
SELECT count(*) FROM numerictmp WHERE a < -1890.0;
count
-------
- 505
+ 506
(1 row)
SELECT count(*) FROM numerictmp WHERE a <= -1890.0;
count
-------
- 506
+ 507
(1 row)
SELECT count(*) FROM numerictmp WHERE a = -1890.0;
@@ -25,37 +25,37 @@ SELECT count(*) FROM numerictmp WHERE a = -1890.0;
SELECT count(*) FROM numerictmp WHERE a >= -1890.0;
count
-------
- 597
+ 599
(1 row)
SELECT count(*) FROM numerictmp WHERE a > -1890.0;
count
-------
- 596
+ 598
(1 row)
SELECT count(*) FROM numerictmp WHERE a < 'NaN' ;
count
-------
- 1100
+ 1102
(1 row)
SELECT count(*) FROM numerictmp WHERE a <= 'NaN' ;
count
-------
- 1102
+ 1105
(1 row)
SELECT count(*) FROM numerictmp WHERE a = 'NaN' ;
count
-------
- 2
+ 3
(1 row)
SELECT count(*) FROM numerictmp WHERE a >= 'NaN' ;
count
-------
- 2
+ 3
(1 row)
SELECT count(*) FROM numerictmp WHERE a > 'NaN' ;
@@ -67,13 +67,13 @@ SELECT count(*) FROM numerictmp WHERE a > 'NaN' ;
SELECT count(*) FROM numerictmp WHERE a < 0 ;
count
-------
- 523
+ 524
(1 row)
SELECT count(*) FROM numerictmp WHERE a <= 0 ;
count
-------
- 526
+ 527
(1 row)
SELECT count(*) FROM numerictmp WHERE a = 0 ;
@@ -85,13 +85,13 @@ SELECT count(*) FROM numerictmp WHERE a = 0 ;
SELECT count(*) FROM numerictmp WHERE a >= 0 ;
count
-------
- 579
+ 581
(1 row)
SELECT count(*) FROM numerictmp WHERE a > 0 ;
count
-------
- 576
+ 578
(1 row)
CREATE INDEX numericidx ON numerictmp USING gist ( a );
@@ -99,13 +99,13 @@ SET enable_seqscan=off;
SELECT count(*) FROM numerictmp WHERE a < -1890.0;
count
-------
- 505
+ 506
(1 row)
SELECT count(*) FROM numerictmp WHERE a <= -1890.0;
count
-------
- 506
+ 507
(1 row)
SELECT count(*) FROM numerictmp WHERE a = -1890.0;
@@ -117,37 +117,37 @@ SELECT count(*) FROM numerictmp WHERE a = -1890.0;
SELECT count(*) FROM numerictmp WHERE a >= -1890.0;
count
-------
- 597
+ 599
(1 row)
SELECT count(*) FROM numerictmp WHERE a > -1890.0;
count
-------
- 596
+ 598
(1 row)
SELECT count(*) FROM numerictmp WHERE a < 'NaN' ;
count
-------
- 1100
+ 1102
(1 row)
SELECT count(*) FROM numerictmp WHERE a <= 'NaN' ;
count
-------
- 1102
+ 1105
(1 row)
SELECT count(*) FROM numerictmp WHERE a = 'NaN' ;
count
-------
- 2
+ 3
(1 row)
SELECT count(*) FROM numerictmp WHERE a >= 'NaN' ;
count
-------
- 2
+ 3
(1 row)
SELECT count(*) FROM numerictmp WHERE a > 'NaN' ;
@@ -159,13 +159,13 @@ SELECT count(*) FROM numerictmp WHERE a > 'NaN' ;
SELECT count(*) FROM numerictmp WHERE a < 0 ;
count
-------
- 523
+ 524
(1 row)
SELECT count(*) FROM numerictmp WHERE a <= 0 ;
count
-------
- 526
+ 527
(1 row)
SELECT count(*) FROM numerictmp WHERE a = 0 ;
@@ -177,13 +177,13 @@ SELECT count(*) FROM numerictmp WHERE a = 0 ;
SELECT count(*) FROM numerictmp WHERE a >= 0 ;
count
-------
- 579
+ 581
(1 row)
SELECT count(*) FROM numerictmp WHERE a > 0 ;
count
-------
- 576
+ 578
(1 row)
-- Test index-only scans
diff --git a/contrib/btree_gist/sql/float4.sql b/contrib/btree_gist/sql/float4.sql
index 3da1ce953c8..71de5d5cf49 100644
--- a/contrib/btree_gist/sql/float4.sql
+++ b/contrib/btree_gist/sql/float4.sql
@@ -35,3 +35,20 @@ SELECT count(*) FROM float4tmp WHERE a > -179.0::float4;
EXPLAIN (COSTS OFF)
SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
+
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float4excl (a float4, EXCLUDE USING gist (a WITH =));
+INSERT INTO float4excl VALUES ('NaN'::float4);
+INSERT INTO float4excl VALUES ('NaN'::float4); -- expect: violates EXCLUDE
+SELECT count(*) FROM float4excl;
+
+-- Test double-column index
+CREATE INDEX float4idx2 ON float4tmp USING gist ( a, abs(a) );
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM float4tmp WHERE abs(a) = 179.0::float4;
+SELECT count(*) FROM float4tmp WHERE abs(a) = 179.0::float4;
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/contrib/btree_gist/sql/float8.sql b/contrib/btree_gist/sql/float8.sql
index e1e819b37f9..a0fc84f94bb 100644
--- a/contrib/btree_gist/sql/float8.sql
+++ b/contrib/btree_gist/sql/float8.sql
@@ -35,3 +35,20 @@ SELECT count(*) FROM float8tmp WHERE a > -1890.0::float8;
EXPLAIN (COSTS OFF)
SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
+
+-- EXCLUDE constraint must block a duplicate NaN, same as it does for finite
+-- values.
+CREATE TABLE float8excl (a float8, EXCLUDE USING gist (a WITH =));
+INSERT INTO float8excl VALUES ('NaN'::float8);
+INSERT INTO float8excl VALUES ('NaN'::float8); -- expect: violates EXCLUDE
+SELECT count(*) FROM float8excl;
+
+-- Test double-column index
+CREATE INDEX float8idx2 ON float8tmp USING gist ( a, abs(a) );
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM float8tmp WHERE abs(a) = 1890.0::float8;
+SELECT count(*) FROM float8tmp WHERE abs(a) = 1890.0::float8;
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
--
2.52.0
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:27 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:35 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:50 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 23:08 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
@ 2026-07-01 13:38 ` Bill Kim <[email protected]>
2026-07-01 17:33 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Bill Kim @ 2026-07-01 13:38 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
Thanks.
v2 applies cleanly on master and contrib/btree_gist regression passes here.
Nothing to add.
regards,
Bill
2026년 6월 30일 (화) 오전 8:08, Tom Lane <[email protected]>님이 작성:
> I studied this and decided that it only partially fixes the problem.
> Yes, we've got to fix the comparison functions, but we also have to
> fix the penalty and distance functions. As things stand, the penalty
> functions will probably return NaN for any input including a NaN,
> which gistpenalty will clamp to zero, which we do not want because
> it'll basically break all decisions about where to put things,
> leading to a seriously inefficient index. (Maybe gistpenalty should
> do something else with a NaN, but I'm hesitant to touch that right
> now.) The penalty logic is also under the misapprehension that
> multiplying everything by 0.49 keeps it from having to worry about
> overflows; that won't fix things for infinities. The distance
> functions will do the wrong things for NaN as well, probably breaking
> any KNN search that happens across a NaN index entry.
>
> So I fixed all that, and then was dismayed to find that the new
> penalty logic wasn't reached at all in the regression tests, per code
> coverage testing. We don't compute any penalties during GiST index
> build unless it's a multicolumn index, so most of the per-datatype
> tests aren't reaching that. It didn't seem like adding more index
> entries partway through the test would fit very well into the
> structure of float[48].sql, so instead I added simple tests of
> two-column float indexes to improve the coverage. Also, I thought
> we should test this logic by adding NaN (and infinities for good
> measure) to the initial data load, rather than creating a new small
> table which would only exercise the logic very minimally.
>
> At the moment I'm leaning to back-patching this. We'd have to
> release-note the need to reindex any btree_gist indexes containing
> NaNs, but we've done similar things many times before.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:04 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:27 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 15:35 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-06-29 15:50 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-29 23:08 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Tom Lane <[email protected]>
2026-07-01 13:38 ` Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
@ 2026-07-01 17:33 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2026-07-01 17:33 UTC (permalink / raw)
To: Bill Kim <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; [email protected]
Bill Kim <[email protected]> writes:
> v2 applies cleanly on master and contrib/btree_gist regression passes here.
> Nothing to add.
Pushed.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-07-01 17:33 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-28 02:14 BUG #19524: NaN handling in btree_gist's float4/float8 opclasses Bill Kim <[email protected]>
2026-06-28 18:54 ` Daniel Gustafsson <[email protected]>
2026-06-29 12:04 ` Bill Kim <[email protected]>
2026-06-29 15:04 ` Tom Lane <[email protected]>
2026-06-29 15:27 ` Bill Kim <[email protected]>
2026-06-29 15:35 ` Tom Lane <[email protected]>
2026-06-29 15:50 ` Bill Kim <[email protected]>
2026-06-29 23:08 ` Tom Lane <[email protected]>
2026-07-01 13:38 ` Bill Kim <[email protected]>
2026-07-01 17:33 ` Tom Lane <[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