Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1weL5y-004jbY-1W for pgsql-bugs@arkaria.postgresql.org; Mon, 29 Jun 2026 23:08:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1weL5w-00586q-0q for pgsql-bugs@arkaria.postgresql.org; Mon, 29 Jun 2026 23:08:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1weL5v-00586h-33 for pgsql-bugs@lists.postgresql.org; Mon, 29 Jun 2026 23:08:52 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1weL5r-00000000s5c-1j70 for pgsql-bugs@lists.postgresql.org; Mon, 29 Jun 2026 23:08:51 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 65TN8gfL2231548; Mon, 29 Jun 2026 19:08:42 -0400 From: Tom Lane To: Bill Kim cc: Daniel Gustafsson , pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses In-reply-to: References: <1703741.1782745481@sss.pgh.pa.us> <1717564.1782747310@sss.pgh.pa.us> Comments: In-reply-to Bill Kim message dated "Tue, 30 Jun 2026 00:50:11 +0900" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2231541.1782774516.0@sss.pgh.pa.us> Date: Mon, 29 Jun 2026 19:08:42 -0400 Message-ID: <2231547.1782774522@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2231541.1782774516.1@sss.pgh.pa.us> 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name*0="v2-0001-btree_gist-fix-NaN-handling-in-float4-float8-opcl.p"; name*1="atch"; charset="us-ascii" Content-ID: <2231541.1782774516.2@sss.pgh.pa.us> Content-Description: v2-0001-btree_gist-fix-NaN-handling-in-float4-float8-opcl.patch Content-Transfer-Encoding: quoted-printable =46rom 50ce44820077a5a25c9d04c2f39a0c8479b1dff5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 29 Jun 2026 18:35:23 -0400 Subject: [PATCH v2] btree_gist: fix NaN handling in float4/float8 opclasse= s. The float4 and float8 btree_gist opclasses compared keys with raw C operators (=3D=3D, <, >). 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 Reported-by: Yuelin Wang <3020001251@tju.edu.cn> Author: Bill Kim Co-authored-by: Tom Lane Discussion: https://postgr.es/m/19501-3bff3bbc97f1e7c9@postgresql.org Discussion: https://postgr.es/m/19524-9559d302c8455664@postgresql.org Discussion: https://postgr.es/m/CAMQXxcgbtD2LXfX0tpgvOizxP-XxrCHV2ZDy4By_T= ZnJMsxXWQ@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 distanc= e + * functions below must also cope with NaNs, in particular with the polic= y + * 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) >=3D *((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) =3D=3D *((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) <=3D *((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, FmgrIn= fo *flinfo) { float4KEY *ia =3D (float4KEY *) (((const Nsrt *) a)->t); float4KEY *ib =3D (float4KEY *) (((const Nsrt *) b)->t); + int res; = - if (ia->lower =3D=3D ib->lower) - { - if (ia->upper =3D=3D ib->upper) - return 0; - - return (ia->upper > ib->upper) ? 1 : -1; - } - - return (ia->lower > ib->lower) ? 1 : -1; + res =3D float4_cmp_internal(ia->lower, ib->lower); + if (res !=3D 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 =3D *(const float4 *) a; + float8 arg2 =3D *(const float4 *) b; + float8 r; + + r =3D arg1 - arg2; + /* needn't consider isinf case here, must be due to input infinity */ + if (unlikely(isnan(r))) + { + if (isnan(arg1) && isnan(arg2)) + r =3D 0.0; /* treat NaNs as equal */ + else if (isnan(arg1) || isnan(arg2)) + r =3D get_float8_infinity(); /* max dist for NaN vs non-NaN */ + else + r =3D 0.0; /* must be Inf - Inf case */ + } + return fabs(r); } = = @@ -102,7 +119,15 @@ float4_dist(PG_FUNCTION_ARGS) r =3D a - b; if (unlikely(isinf(r)) && !isinf(a) && !isinf(b)) float_overflow_error(); - + if (unlikely(isnan(r))) + { + if (isnan(a) && isnan(b)) + r =3D 0.0; /* treat NaNs as equal */ + else if (isnan(a) || isnan(b)) + r =3D get_float4_infinity(); /* max dist for NaN vs non-NaN */ + else + r =3D 0.0; /* must be Inf - Inf case */ + } PG_RETURN_FLOAT4(fabsf(r)); } = @@ -186,7 +211,7 @@ gbt_float4_penalty(PG_FUNCTION_ARGS) float4KEY *newentry =3D (float4KEY *) DatumGetPointer(((GISTENTRY *) PG= _GETARG_POINTER(1))->key); float *result =3D (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 distanc= e + * functions below must also cope with NaNs, in particular with the polic= y + * 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) >=3D *((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) =3D=3D *((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) <=3D *((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, FmgrIn= fo *flinfo) { float8KEY *ia =3D (float8KEY *) (((const Nsrt *) a)->t); float8KEY *ib =3D (float8KEY *) (((const Nsrt *) b)->t); + int res; = - if (ia->lower =3D=3D ib->lower) - { - if (ia->upper =3D=3D ib->upper) - return 0; - - return (ia->upper > ib->upper) ? 1 : -1; - } - - return (ia->lower > ib->lower) ? 1 : -1; + res =3D float8_cmp_internal(ia->lower, ib->lower); + if (res !=3D 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 =3D arg1 - arg2; if (unlikely(isinf(r)) && !isinf(arg1) && !isinf(arg2)) float_overflow_error(); + if (unlikely(isnan(r))) + { + if (isnan(arg1) && isnan(arg2)) + r =3D 0.0; /* treat NaNs as equal */ + else if (isnan(arg1) || isnan(arg2)) + r =3D get_float8_infinity(); /* max dist for NaN vs non-NaN */ + else + r =3D 0.0; /* must be Inf - Inf case */ + } return fabs(r); } = @@ -110,7 +121,15 @@ float8_dist(PG_FUNCTION_ARGS) r =3D a - b; if (unlikely(isinf(r)) && !isinf(a) && !isinf(b)) float_overflow_error(); - + if (unlikely(isnan(r))) + { + if (isnan(a) && isnan(b)) + r =3D 0.0; /* treat NaNs as equal */ + else if (isnan(a) || isnan(b)) + r =3D get_float8_infinity(); /* max dist for NaN vs non-NaN */ + else + r =3D 0.0; /* must be Inf - Inf case */ + } PG_RETURN_FLOAT8(fabs(r)); } = @@ -194,7 +213,7 @@ gbt_float8_penalty(PG_FUNCTION_ARGS) float8KEY *newentry =3D (float8KEY *) DatumGetPointer(((GISTENTRY *) PG= _GETARG_POINTER(1))->key); float *result =3D (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/btr= ee_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 overflo= ws + * 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 =3D 0.0F; + double tmp =3D 0.0; + + /* Add penalty for expanding upper bound */ + if (nupper > oupper) + tmp +=3D nupper - oupper; + /* Add penalty for expanding lower bound */ + if (olower > nlower) + tmp +=3D olower - nlower; + if (tmp > 0.0) + { + /* Ensure result is non-zero, even if next step underflows to zero */ + result +=3D FLT_MIN; + /* Scale penalty to 0 .. 1 */ + result +=3D (float) (tmp / (tmp + (oupper - olower))); + /* Scale to 0 .. FLT_MAX / (natts + 1) */ + result *=3D 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 =3D 0.0F; + double tmp =3D 0.0; + + /* Add penalty for expanding upper bound */ + if (float8_gt(nupper, oupper)) + { + double delta =3D nupper - oupper; + + if (unlikely(isnan(delta))) + { + /* oupper couldn't be NaN here, see float8_gt */ + if (isnan(nupper)) + delta =3D FLT_MAX; /* max penalty for NaN vs non-NaN */ + else + delta =3D 0.0; /* must be Inf - Inf case */ + } + else if (delta > FLT_MAX) + delta =3D FLT_MAX; /* clamp to FLT_MAX, esp for infinity */ + tmp +=3D delta; + } + /* Add penalty for expanding lower bound */ + if (float8_gt(olower, nlower)) + { + double delta =3D olower - nlower; + + if (unlikely(isnan(delta))) + { + /* nlower couldn't be NaN here, see float8_gt */ + if (isnan(olower)) + delta =3D FLT_MAX; /* max penalty for NaN vs non-NaN */ + else + delta =3D 0.0; /* must be Inf - Inf case */ + } + else if (delta > FLT_MAX) + delta =3D FLT_MAX; /* clamp to FLT_MAX, esp for infinity */ + tmp +=3D delta; + } + if (tmp > 0.0) + { + double delta =3D 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 =3D 0.0; /* treat NaNs as equal */ + else if (isnan(oupper) || isnan(olower)) + delta =3D FLT_MAX; /* max penalty for NaN vs non-NaN */ + else + delta =3D 0.0; /* must be Inf - Inf case */ + } + else if (delta > FLT_MAX) + delta =3D FLT_MAX; /* clamp to FLT_MAX, esp for infinity */ + /* Ensure result is non-zero, even if next step underflows to zero */ + result +=3D FLT_MIN; + /* Scale penalty to 0 .. 1 */ + result +=3D (float) (tmp / (tmp + delta)); + /* Scale to 0 .. FLT_MAX / (natts + 1) */ + result *=3D 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 =3D 0.0F; \ - (*(result)) =3D 0.0F; \ - if ( (nupper) > (oupper) ) \ - tmp +=3D ( ((double)nupper)*0.49F - ((double)oupper)*0.49F ); \ - if ( (olower) > (nlower) ) \ - tmp +=3D ( ((double)olower)*0.49F - ((double)nlower)*0.49F ); \ - if (tmp > 0.0F) \ - { \ - (*(result)) +=3D FLT_MIN; \ - (*(result)) +=3D (float) ( ((double)(tmp)) / ( (double)(tmp) + ( ((doubl= e)(oupper))*0.49F - ((double)(olower))*0.49F ) ) ); \ - (*(result)) *=3D (FLT_MAX / (((GISTENTRY *) PG_GETARG_POINTER(0))->rel->= rd_att->natts + 1)); \ - } \ + GISTENTRY *entry =3D (GISTENTRY *) PG_GETARG_POINTER(0); \ + *(result) =3D 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 =3D (GISTENTRY *) PG_GETARG_POINTER(0); \ + *(result) =3D 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/e= xpected/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=3Don; SELECT count(*) FROM float4tmp WHERE a < -179.0; count = ------- - 244 + 245 (1 row) = SELECT count(*) FROM float4tmp WHERE a <=3D -179.0; count = ------- - 245 + 246 (1 row) = SELECT count(*) FROM float4tmp WHERE a =3D -179.0; @@ -23,13 +23,13 @@ SELECT count(*) FROM float4tmp WHERE a =3D -179.0; SELECT count(*) FROM float4tmp WHERE a >=3D -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=3Doff; SELECT count(*) FROM float4tmp WHERE a < -179.0::float4; count = ------- - 244 + 245 (1 row) = SELECT count(*) FROM float4tmp WHERE a <=3D -179.0::float4; count = ------- - 245 + 246 (1 row) = SELECT count(*) FROM float4tmp WHERE a =3D -179.0::float4; @@ -63,13 +63,13 @@ SELECT count(*) FROM float4tmp WHERE a =3D -179.0::fl= oat4; SELECT count(*) FROM float4tmp WHERE a >=3D -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 fin= ite +-- values. +CREATE TABLE float4excl (a float4, EXCLUDE USING gist (a WITH =3D)); +INSERT INTO float4excl VALUES ('NaN'::float4); +INSERT INTO float4excl VALUES ('NaN'::float4); -- expect: violates EXCLU= DE +ERROR: conflicting key value violates exclusion constraint "float4excl_a= _excl" +DETAIL: Key (a)=3D(NaN) conflicts with existing key (a)=3D(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) =3D 179.0::float4; + QUERY PLAN = +-------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on float4tmp + Recheck Cond: (abs(a) =3D '179'::real) + -> Bitmap Index Scan on float4idx2 + Index Cond: (abs(a) =3D '179'::real) +(5 rows) + +SELECT count(*) FROM float4tmp WHERE abs(a) =3D 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/e= xpected/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=3Don; SELECT count(*) FROM float8tmp WHERE a < -1890.0; count = ------- - 237 + 238 (1 row) = SELECT count(*) FROM float8tmp WHERE a <=3D -1890.0; count = ------- - 238 + 239 (1 row) = SELECT count(*) FROM float8tmp WHERE a =3D -1890.0; @@ -23,13 +23,13 @@ SELECT count(*) FROM float8tmp WHERE a =3D -1890.0; SELECT count(*) FROM float8tmp WHERE a >=3D -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=3Doff; SELECT count(*) FROM float8tmp WHERE a < -1890.0::float8; count = ------- - 237 + 238 (1 row) = SELECT count(*) FROM float8tmp WHERE a <=3D -1890.0::float8; count = ------- - 238 + 239 (1 row) = SELECT count(*) FROM float8tmp WHERE a =3D -1890.0::float8; @@ -63,13 +63,13 @@ SELECT count(*) FROM float8tmp WHERE a =3D -1890.0::f= loat8; SELECT count(*) FROM float8tmp WHERE a >=3D -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 fin= ite +-- values. +CREATE TABLE float8excl (a float8, EXCLUDE USING gist (a WITH =3D)); +INSERT INTO float8excl VALUES ('NaN'::float8); +INSERT INTO float8excl VALUES ('NaN'::float8); -- expect: violates EXCLU= DE +ERROR: conflicting key value violates exclusion constraint "float8excl_a= _excl" +DETAIL: Key (a)=3D(NaN) conflicts with existing key (a)=3D(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) =3D 1890.0::float8; + QUERY PLAN = +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on float8tmp + Recheck Cond: (abs(a) =3D '1890'::double precision) + -> Bitmap Index Scan on float8idx2 + Index Cond: (abs(a) =3D '1890'::double precision) +(5 rows) + +SELECT count(*) FROM float8tmp WHERE abs(a) =3D 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=3Don; SELECT count(*) FROM numerictmp WHERE a < -1890.0; count = ------- - 505 + 506 (1 row) = SELECT count(*) FROM numerictmp WHERE a <=3D -1890.0; count = ------- - 506 + 507 (1 row) = SELECT count(*) FROM numerictmp WHERE a =3D -1890.0; @@ -25,37 +25,37 @@ SELECT count(*) FROM numerictmp WHERE a =3D -1890.0; SELECT count(*) FROM numerictmp WHERE a >=3D -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 <=3D 'NaN' ; count = ------- - 1102 + 1105 (1 row) = SELECT count(*) FROM numerictmp WHERE a =3D 'NaN' ; count = ------- - 2 + 3 (1 row) = SELECT count(*) FROM numerictmp WHERE a >=3D '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 <=3D 0 ; count = ------- - 526 + 527 (1 row) = SELECT count(*) FROM numerictmp WHERE a =3D 0 ; @@ -85,13 +85,13 @@ SELECT count(*) FROM numerictmp WHERE a =3D 0 ; SELECT count(*) FROM numerictmp WHERE a >=3D 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=3Doff; SELECT count(*) FROM numerictmp WHERE a < -1890.0; count = ------- - 505 + 506 (1 row) = SELECT count(*) FROM numerictmp WHERE a <=3D -1890.0; count = ------- - 506 + 507 (1 row) = SELECT count(*) FROM numerictmp WHERE a =3D -1890.0; @@ -117,37 +117,37 @@ SELECT count(*) FROM numerictmp WHERE a =3D -1890.0= ; SELECT count(*) FROM numerictmp WHERE a >=3D -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 <=3D 'NaN' ; count = ------- - 1102 + 1105 (1 row) = SELECT count(*) FROM numerictmp WHERE a =3D 'NaN' ; count = ------- - 2 + 3 (1 row) = SELECT count(*) FROM numerictmp WHERE a >=3D '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 <=3D 0 ; count = ------- - 526 + 527 (1 row) = SELECT count(*) FROM numerictmp WHERE a =3D 0 ; @@ -177,13 +177,13 @@ SELECT count(*) FROM numerictmp WHERE a =3D 0 ; SELECT count(*) FROM numerictmp WHERE a >=3D 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/fl= oat4.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::float= 4; 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 fin= ite +-- values. +CREATE TABLE float4excl (a float4, EXCLUDE USING gist (a WITH =3D)); +INSERT INTO float4excl VALUES ('NaN'::float4); +INSERT INTO float4excl VALUES ('NaN'::float4); -- expect: violates EXCLU= DE +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) =3D 179.0::float4; +SELECT count(*) FROM float4tmp WHERE abs(a) =3D 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/fl= oat8.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::floa= t8; 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 fin= ite +-- values. +CREATE TABLE float8excl (a float8, EXCLUDE USING gist (a WITH =3D)); +INSERT INTO float8excl VALUES ('NaN'::float8); +INSERT INTO float8excl VALUES ('NaN'::float8); -- expect: violates EXCLU= DE +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) =3D 1890.0::float8; +SELECT count(*) FROM float8tmp WHERE abs(a) =3D 1890.0::float8; + +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; -- = 2.52.0 ------- =_aaaaaaaaaa0--