v5-0004-Add-cross-type-comparisons-for-float-types.patch

From 1784bf4c688642491cd486740201ce046bb721c9 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 2 Jul 2025 13:31:01 -0400
Subject: [PATCH v5 4/6] Add cross-type comparisons for float types.

---
 contrib/btree_gin/btree_gin--1.3--1.4.sql |  20 ++
 contrib/btree_gin/btree_gin.c             |  44 ++-
 contrib/btree_gin/expected/float4.out     | 321 ++++++++++++++++++++++
 contrib/btree_gin/expected/float8.out     |  50 ++++
 contrib/btree_gin/sql/float4.sql          |  53 ++++
 contrib/btree_gin/sql/float8.sql          |   9 +
 6 files changed, 491 insertions(+), 6 deletions(-)

diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
index 4c77138fabe..c68fe2b0da2 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -61,3 +61,23 @@ ADD
     OPERATOR        0x24    >= (int8, int4),
     OPERATOR        0x25    > (int8, int4)
 ;
+
+ALTER OPERATOR FAMILY float4_ops USING gin
+ADD
+    -- Code 1: RHS is float8
+    OPERATOR        0x11    < (float4, float8),
+    OPERATOR        0x12    <= (float4, float8),
+    OPERATOR        0x13    = (float4, float8),
+    OPERATOR        0x14    >= (float4, float8),
+    OPERATOR        0x15    > (float4, float8)
+;
+
+ALTER OPERATOR FAMILY float8_ops USING gin
+ADD
+    -- Code 1: RHS is float4
+    OPERATOR        0x11    < (float8, float4),
+    OPERATOR        0x12    <= (float8, float4),
+    OPERATOR        0x13    = (float8, float4),
+    OPERATOR        0x14    >= (float8, float4),
+    OPERATOR        0x15    > (float8, float4)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 818a33af97f..be5b89a3081 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -401,13 +401,34 @@ leftmostvalue_float4(void)
 	return Float4GetDatum(-get_float4_infinity());
 }
 
+static Datum
+cvt_float8_float4(Datum input)
+{
+	float8		val = DatumGetFloat8(input);
+	float4		result;
+
+	/*
+	 * Assume that ordinary C conversion will produce a usable result.
+	 * (Compare dtof(), which raises error conditions that we don't need.)
+	 * Note that for inputs that aren't exactly representable as float4, it
+	 * doesn't matter whether the conversion rounds up or down.  That might
+	 * cause us to scan a few index entries that we'll reject as not matching,
+	 * but we won't miss any that should match.
+	 */
+	result = (float4) val;
+	return Float4GetDatum(result);
+}
+
 static const bool float4_rhs_is_varlena[] =
-{false};
+{false, false};
+
+static const btree_gin_convert_function float4_cvt_fns[] =
+{NULL, cvt_float8_float4};
 
 static const PGFunction float4_cmp_fns[] =
-{btfloat4cmp};
+{btfloat4cmp, btfloat84cmp};
 
-GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, NULL, float4_cmp_fns)
+GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, float4_cvt_fns, float4_cmp_fns)
 
 static Datum
 leftmostvalue_float8(void)
@@ -415,13 +436,24 @@ leftmostvalue_float8(void)
 	return Float8GetDatum(-get_float8_infinity());
 }
 
+static Datum
+cvt_float4_float8(Datum input)
+{
+	float4		val = DatumGetFloat4(input);
+
+	return Float8GetDatum((float8) val);
+}
+
 static const bool float8_rhs_is_varlena[] =
-{false};
+{false, false};
+
+static const btree_gin_convert_function float8_cvt_fns[] =
+{NULL, cvt_float4_float8};
 
 static const PGFunction float8_cmp_fns[] =
-{btfloat8cmp};
+{btfloat8cmp, btfloat48cmp};
 
-GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, NULL, float8_cmp_fns)
+GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, float8_cvt_fns, float8_cmp_fns)
 
 static Datum
 leftmostvalue_money(void)
diff --git a/contrib/btree_gin/expected/float4.out b/contrib/btree_gin/expected/float4.out
index 7b9134fcd4b..c8bb04e59be 100644
--- a/contrib/btree_gin/expected/float4.out
+++ b/contrib/btree_gin/expected/float4.out
@@ -42,3 +42,324 @@ SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i;
  3
 (2 rows)
 
+explain (costs off)
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Sort
+   Sort Key: i
+   ->  Bitmap Heap Scan on test_float4
+         Recheck Cond: (i < '1'::double precision)
+         ->  Bitmap Index Scan on idx_float4
+               Index Cond: (i < '1'::double precision)
+(6 rows)
+
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+ i  
+----
+ -2
+ -1
+  0
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i;
+ i  
+----
+ -2
+ -1
+  0
+  1
+(4 rows)
+
+SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i;
+ i 
+---
+ 1
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i;
+ i 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i;
+ i 
+---
+ 2
+ 3
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf');
+SELECT gin_clean_pending_list('idx_float4');
+ gin_clean_pending_list 
+------------------------
+                      1
+(1 row)
+
+SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i;
+ i 
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+  Infinity
+       NaN
+(9 rows)
+
+SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i;
+    i     
+----------
+       -2
+       -1
+        0
+        1
+        2
+        3
+ Infinity
+      NaN
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+  Infinity
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i;
+    i     
+----------
+ Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i;
+    i     
+----------
+ Infinity
+      NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i;
+  i  
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i;
+ i 
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i;
+    i     
+----------
+ Infinity
+      NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i;
+    i     
+----------
+ Infinity
+      NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+  Infinity
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+         1
+         2
+         3
+  Infinity
+       NaN
+(9 rows)
+
+SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i;
+  i  
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i;
+  i  
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i;
+ i 
+---
+(0 rows)
+
+-- Check rounding cases
+-- 1e-300 rounds to 0 for float4 but not for float8
+SELECT * FROM test_float4 WHERE i < -1e-300::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i <= -1e-300::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i = -1e-300::float8 ORDER BY i;
+ i 
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i > -1e-300::float8 ORDER BY i;
+    i     
+----------
+        0
+        1
+        2
+        3
+ Infinity
+      NaN
+(6 rows)
+
+SELECT * FROM test_float4 WHERE i >= -1e-300::float8 ORDER BY i;
+    i     
+----------
+        0
+        1
+        2
+        3
+ Infinity
+      NaN
+(6 rows)
+
+SELECT * FROM test_float4 WHERE i < 1e-300::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+(4 rows)
+
+SELECT * FROM test_float4 WHERE i <= 1e-300::float8 ORDER BY i;
+     i     
+-----------
+ -Infinity
+        -2
+        -1
+         0
+(4 rows)
+
+SELECT * FROM test_float4 WHERE i = 1e-300::float8 ORDER BY i;
+ i 
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i > 1e-300::float8 ORDER BY i;
+    i     
+----------
+        1
+        2
+        3
+ Infinity
+      NaN
+(5 rows)
+
+SELECT * FROM test_float4 WHERE i >= 1e-300::float8 ORDER BY i;
+    i     
+----------
+        1
+        2
+        3
+ Infinity
+      NaN
+(5 rows)
+
diff --git a/contrib/btree_gin/expected/float8.out b/contrib/btree_gin/expected/float8.out
index a41d4f9f6bb..b2877dfa3c1 100644
--- a/contrib/btree_gin/expected/float8.out
+++ b/contrib/btree_gin/expected/float8.out
@@ -42,3 +42,53 @@ SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i;
  3
 (2 rows)
 
+explain (costs off)
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+                 QUERY PLAN                  
+---------------------------------------------
+ Sort
+   Sort Key: i
+   ->  Bitmap Heap Scan on test_float8
+         Recheck Cond: (i < '1'::real)
+         ->  Bitmap Index Scan on idx_float8
+               Index Cond: (i < '1'::real)
+(6 rows)
+
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+ i  
+----
+ -2
+ -1
+  0
+(3 rows)
+
+SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i;
+ i  
+----
+ -2
+ -1
+  0
+  1
+(4 rows)
+
+SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i;
+ i 
+---
+ 1
+(1 row)
+
+SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i;
+ i 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i;
+ i 
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/sql/float4.sql b/contrib/btree_gin/sql/float4.sql
index 759778ad3c3..0707ed6518f 100644
--- a/contrib/btree_gin/sql/float4.sql
+++ b/contrib/btree_gin/sql/float4.sql
@@ -13,3 +13,56 @@ SELECT * FROM test_float4 WHERE i<=1::float4 ORDER BY i;
 SELECT * FROM test_float4 WHERE i=1::float4 ORDER BY i;
 SELECT * FROM test_float4 WHERE i>=1::float4 ORDER BY i;
 SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf');
+SELECT gin_clean_pending_list('idx_float4');
+
+SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i;
+
+-- Check rounding cases
+-- 1e-300 rounds to 0 for float4 but not for float8
+
+SELECT * FROM test_float4 WHERE i < -1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i <= -1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i = -1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i > -1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i >= -1e-300::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i < 1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i <= 1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i = 1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i > 1e-300::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i >= 1e-300::float8 ORDER BY i;
diff --git a/contrib/btree_gin/sql/float8.sql b/contrib/btree_gin/sql/float8.sql
index b046ac4e6c4..5f393147082 100644
--- a/contrib/btree_gin/sql/float8.sql
+++ b/contrib/btree_gin/sql/float8.sql
@@ -13,3 +13,12 @@ SELECT * FROM test_float8 WHERE i<=1::float8 ORDER BY i;
 SELECT * FROM test_float8 WHERE i=1::float8 ORDER BY i;
 SELECT * FROM test_float8 WHERE i>=1::float8 ORDER BY i;
 SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i;
-- 
2.43.5