public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Bill Kim <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: [email protected]
Subject: Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
Date: Mon, 29 Jun 2026 19:08:42 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMQXxcj=HaLUir91J7K_V7mTwehK0nj62tdhCiH8YwSLtp-A3Q@mail.gmail.com>
References: <CAMQXxcgbtD2LXfX0tpgvOizxP-XxrCHV2ZDy4By_TZnJMsxXWQ@mail.gmail.com>
<[email protected]>
<CAMQXxch1N=sZ1fsfx57VhTe=8qpmb1K1vnaB5WVyDbAs8bqExg@mail.gmail.com>
<[email protected]>
<CAMQXxch6yrJQdeHQK9Mypa9cmDKjQOc5DJ=SL72pom1djL_OLg@mail.gmail.com>
<[email protected]>
<CAMQXxcj=HaLUir91J7K_V7mTwehK0nj62tdhCiH8YwSLtp-A3Q@mail.gmail.com>
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
view thread (10+ 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], [email protected], [email protected], [email protected]
Subject: Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
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