YQL-17615: Fixed floats & numerics formatting in pgrun (#1230)

12 files changed, 621 insertions, 12 deletions

diff --git a/ydb/library/yql/tests/postgresql/cases/aggregates.out b/ydb/library/yql/tests/postgresql/cases/aggregates.out
index be38f5ac49..d167da5cf6 100644
--- a/ydb/library/yql/tests/postgresql/cases/aggregates.out
+++ b/ydb/library/yql/tests/postgresql/cases/aggregates.out
@@ -17,6 +17,30 @@ SELECT stddev_pop(3.0::float8), stddev_samp(4.0::float8);
           0 |            
 (1 row)
 
+SELECT var_pop('inf'::float8), var_samp('inf'::float8);
+ var_pop | var_samp 
+---------+----------
+     NaN |         
+(1 row)
+
+SELECT stddev_pop('inf'::float8), stddev_samp('inf'::float8);
+ stddev_pop | stddev_samp 
+------------+-------------
+        NaN |            
+(1 row)
+
+SELECT var_pop('nan'::float8), var_samp('nan'::float8);
+ var_pop | var_samp 
+---------+----------
+     NaN |         
+(1 row)
+
+SELECT stddev_pop('nan'::float8), stddev_samp('nan'::float8);
+ stddev_pop | stddev_samp 
+------------+-------------
+        NaN |            
+(1 row)
+
 SELECT var_pop(1.0::float4), var_samp(2.0::float4);
  var_pop | var_samp 
 ---------+----------
@@ -29,6 +53,30 @@ SELECT stddev_pop(3.0::float4), stddev_samp(4.0::float4);
           0 |            
 (1 row)
 
+SELECT var_pop('inf'::float4), var_samp('inf'::float4);
+ var_pop | var_samp 
+---------+----------
+     NaN |         
+(1 row)
+
+SELECT stddev_pop('inf'::float4), stddev_samp('inf'::float4);
+ stddev_pop | stddev_samp 
+------------+-------------
+        NaN |            
+(1 row)
+
+SELECT var_pop('nan'::float4), var_samp('nan'::float4);
+ var_pop | var_samp 
+---------+----------
+     NaN |         
+(1 row)
+
+SELECT stddev_pop('nan'::float4), stddev_samp('nan'::float4);
+ stddev_pop | stddev_samp 
+------------+-------------
+        NaN |            
+(1 row)
+
 SELECT var_pop('inf'::numeric), var_samp('inf'::numeric);
  var_pop | var_samp 
 ---------+----------
@@ -114,6 +162,42 @@ select avg('NaN'::numeric) from generate_series(1,3);
  NaN
 (1 row)
 
+-- verify correct results for infinite inputs
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('1'), ('infinity')) v(x);
+   sum    |   avg    | var_pop 
+----------+----------+---------
+ Infinity | Infinity |     NaN
+(1 row)
+
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('infinity'), ('1')) v(x);
+   sum    |   avg    | var_pop 
+----------+----------+---------
+ Infinity | Infinity |     NaN
+(1 row)
+
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('infinity'), ('infinity')) v(x);
+   sum    |   avg    | var_pop 
+----------+----------+---------
+ Infinity | Infinity |     NaN
+(1 row)
+
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('infinity')) v(x);
+ sum | avg | var_pop 
+-----+-----+---------
+ NaN | NaN |     NaN
+(1 row)
+
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('-infinity')) v(x);
+    sum    |    avg    | var_pop 
+-----------+-----------+---------
+ -Infinity | -Infinity |     NaN
+(1 row)
+
 SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
 FROM (VALUES ('1'), ('infinity')) v(x);
    sum    |   avg    | var_pop 
@@ -171,6 +255,18 @@ SELECT covar_pop(1::float8,2::float8), covar_samp(3::float8,4::float8);
          0 |           
 (1 row)
 
+SELECT covar_pop(1::float8,'inf'::float8), covar_samp(3::float8,'inf'::float8);
+ covar_pop | covar_samp 
+-----------+------------
+       NaN |           
+(1 row)
+
+SELECT covar_pop(1::float8,'nan'::float8), covar_samp(3::float8,'nan'::float8);
+ covar_pop | covar_samp 
+-----------+------------
+       NaN |           
+(1 row)
+
 -- test accum and combine functions directly
 CREATE TABLE regr_test (x float8, y float8);
 INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200);
diff --git a/ydb/library/yql/tests/postgresql/cases/aggregates.sql b/ydb/library/yql/tests/postgresql/cases/aggregates.sql
index 10ef58550b..5d544657bb 100644
--- a/ydb/library/yql/tests/postgresql/cases/aggregates.sql
+++ b/ydb/library/yql/tests/postgresql/cases/aggregates.sql
@@ -7,8 +7,16 @@ SET extra_float_digits = 0;
 -- is not
 SELECT var_pop(1.0::float8), var_samp(2.0::float8);
 SELECT stddev_pop(3.0::float8), stddev_samp(4.0::float8);
+SELECT var_pop('inf'::float8), var_samp('inf'::float8);
+SELECT stddev_pop('inf'::float8), stddev_samp('inf'::float8);
+SELECT var_pop('nan'::float8), var_samp('nan'::float8);
+SELECT stddev_pop('nan'::float8), stddev_samp('nan'::float8);
 SELECT var_pop(1.0::float4), var_samp(2.0::float4);
 SELECT stddev_pop(3.0::float4), stddev_samp(4.0::float4);
+SELECT var_pop('inf'::float4), var_samp('inf'::float4);
+SELECT stddev_pop('inf'::float4), stddev_samp('inf'::float4);
+SELECT var_pop('nan'::float4), var_samp('nan'::float4);
+SELECT stddev_pop('nan'::float4), stddev_samp('nan'::float4);
 SELECT var_pop('inf'::numeric), var_samp('inf'::numeric);
 SELECT stddev_pop('inf'::numeric), stddev_samp('inf'::numeric);
 SELECT var_pop('nan'::numeric), var_samp('nan'::numeric);
@@ -24,6 +32,17 @@ select avg(null::numeric) from generate_series(1,3);
 select avg(null::float8) from generate_series(1,3);
 select sum('NaN'::numeric) from generate_series(1,3);
 select avg('NaN'::numeric) from generate_series(1,3);
+-- verify correct results for infinite inputs
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('1'), ('infinity')) v(x);
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('infinity'), ('1')) v(x);
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('infinity'), ('infinity')) v(x);
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('infinity')) v(x);
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('-infinity')) v(x);
 SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
 FROM (VALUES ('1'), ('infinity')) v(x);
 SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
@@ -41,6 +60,8 @@ SELECT avg(x::float8), var_pop(x::float8)
 FROM (VALUES (7000000000005), (7000000000007)) v(x);
 -- check single-tuple behavior
 SELECT covar_pop(1::float8,2::float8), covar_samp(3::float8,4::float8);
+SELECT covar_pop(1::float8,'inf'::float8), covar_samp(3::float8,'inf'::float8);
+SELECT covar_pop(1::float8,'nan'::float8), covar_samp(3::float8,'nan'::float8);
 -- test accum and combine functions directly
 CREATE TABLE regr_test (x float8, y float8);
 INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200);
diff --git a/ydb/library/yql/tests/postgresql/cases/float4.out b/ydb/library/yql/tests/postgresql/cases/float4.out
index fd954860f4..7a2e4e9cd5 100644
--- a/ydb/library/yql/tests/postgresql/cases/float4.out
+++ b/ydb/library/yql/tests/postgresql/cases/float4.out
@@ -81,6 +81,37 @@ INSERT INTO FLOAT4_TBL(f1) VALUES ('123            5');
 ERROR:  invalid input syntax for type real: "123            5"
 LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('123            5');
                                            ^
+-- special inputs
+SELECT 'NaN'::float4;
+ float4 
+--------
+    NaN
+(1 row)
+
+SELECT 'nan'::float4;
+ float4 
+--------
+    NaN
+(1 row)
+
+SELECT '   NAN  '::float4;
+ float4 
+--------
+    NaN
+(1 row)
+
+SELECT 'infinity'::float4;
+  float4  
+----------
+ Infinity
+(1 row)
+
+SELECT '          -INFINiTY   '::float4;
+  float4   
+-----------
+ -Infinity
+(1 row)
+
 -- bad special inputs
 SELECT 'N A N'::float4;
 ERROR:  invalid input syntax for type real: "N A N"
@@ -94,12 +125,61 @@ SELECT ' INFINITY    x'::float4;
 ERROR:  invalid input syntax for type real: " INFINITY    x"
 LINE 1: SELECT ' INFINITY    x'::float4;
                ^
+SELECT 'Infinity'::float4 + 100.0;
+ ?column? 
+----------
+ Infinity
+(1 row)
+
+SELECT 'Infinity'::float4 / 'Infinity'::float4;
+ ?column? 
+----------
+      NaN
+(1 row)
+
 SELECT '42'::float4 / 'Infinity'::float4;
  ?column? 
 ----------
         0
 (1 row)
 
+SELECT 'nan'::float4 / 'nan'::float4;
+ ?column? 
+----------
+      NaN
+(1 row)
+
+SELECT 'nan'::float4 / '0'::float4;
+ ?column? 
+----------
+      NaN
+(1 row)
+
+SELECT 'nan'::numeric::float4;
+ float4 
+--------
+    NaN
+(1 row)
+
+SELECT * FROM FLOAT4_TBL;
+      f1       
+---------------
+             0
+        1004.3
+        -34.84
+ 1.2345679e+20
+ 1.2345679e-20
+(5 rows)
+
+SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
+      f1       
+---------------
+             0
+        -34.84
+ 1.2345679e+20
+ 1.2345679e-20
+(4 rows)
+
 SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
    f1   
 --------
@@ -140,9 +220,66 @@ SELECT f.* FROM FLOAT4_TBL f WHERE  f.f1 <= '1004.3';
  1.2345679e-20
 (4 rows)
 
+SELECT f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+      f1       |       x        
+---------------+----------------
+        1004.3 |         -10043
+ 1.2345679e+20 | -1.2345678e+21
+ 1.2345679e-20 | -1.2345678e-19
+(3 rows)
+
+SELECT f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+      f1       |       x       
+---------------+---------------
+        1004.3 |         994.3
+ 1.2345679e+20 | 1.2345679e+20
+ 1.2345679e-20 |           -10
+(3 rows)
+
+SELECT f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+      f1       |       x        
+---------------+----------------
+        1004.3 |        -100.43
+ 1.2345679e+20 | -1.2345679e+19
+ 1.2345679e-20 | -1.2345679e-21
+(3 rows)
+
+SELECT f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+      f1       |       x       
+---------------+---------------
+        1004.3 |        1014.3
+ 1.2345679e+20 | 1.2345679e+20
+ 1.2345679e-20 |            10
+(3 rows)
+
 -- test divide by zero
 SELECT f.f1 / '0.0' from FLOAT4_TBL f;
 ERROR:  division by zero
+SELECT * FROM FLOAT4_TBL;
+      f1       
+---------------
+             0
+        1004.3
+        -34.84
+ 1.2345679e+20
+ 1.2345679e-20
+(5 rows)
+
+-- test the unary float4abs operator
+SELECT f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
+      f1       |    abs_f1     
+---------------+---------------
+             0 |             0
+        1004.3 |        1004.3
+        -34.84 |         34.84
+ 1.2345679e+20 | 1.2345679e+20
+ 1.2345679e-20 | 1.2345679e-20
+(5 rows)
+
 -- test edge-case coercions to integer
 SELECT '32767.4'::float4::int2;
  int2  
diff --git a/ydb/library/yql/tests/postgresql/cases/float4.sql b/ydb/library/yql/tests/postgresql/cases/float4.sql
index 12d1da606d..9389ded5b5 100644
--- a/ydb/library/yql/tests/postgresql/cases/float4.sql
+++ b/ydb/library/yql/tests/postgresql/cases/float4.sql
@@ -29,18 +29,42 @@ INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
 INSERT INTO FLOAT4_TBL(f1) VALUES ('5.   0');
 INSERT INTO FLOAT4_TBL(f1) VALUES ('     - 3.0');
 INSERT INTO FLOAT4_TBL(f1) VALUES ('123            5');
+-- special inputs
+SELECT 'NaN'::float4;
+SELECT 'nan'::float4;
+SELECT '   NAN  '::float4;
+SELECT 'infinity'::float4;
+SELECT '          -INFINiTY   '::float4;
 -- bad special inputs
 SELECT 'N A N'::float4;
 SELECT 'NaN x'::float4;
 SELECT ' INFINITY    x'::float4;
+SELECT 'Infinity'::float4 + 100.0;
+SELECT 'Infinity'::float4 / 'Infinity'::float4;
 SELECT '42'::float4 / 'Infinity'::float4;
+SELECT 'nan'::float4 / 'nan'::float4;
+SELECT 'nan'::float4 / '0'::float4;
+SELECT 'nan'::numeric::float4;
+SELECT * FROM FLOAT4_TBL;
+SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
 SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
 SELECT f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
 SELECT f.* FROM FLOAT4_TBL f WHERE  f.f1 < '1004.3';
 SELECT f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
 SELECT f.* FROM FLOAT4_TBL f WHERE  f.f1 <= '1004.3';
+SELECT f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+SELECT f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+SELECT f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
+SELECT f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0';
 -- test divide by zero
 SELECT f.f1 / '0.0' from FLOAT4_TBL f;
+SELECT * FROM FLOAT4_TBL;
+-- test the unary float4abs operator
+SELECT f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
 -- test edge-case coercions to integer
 SELECT '32767.4'::float4::int2;
 SELECT '32767.6'::float4::int2;
diff --git a/ydb/library/yql/tests/postgresql/cases/float8.out b/ydb/library/yql/tests/postgresql/cases/float8.out
index 18127b354e..69d701a206 100644
--- a/ydb/library/yql/tests/postgresql/cases/float8.out
+++ b/ydb/library/yql/tests/postgresql/cases/float8.out
@@ -57,6 +57,37 @@ INSERT INTO FLOAT8_TBL(f1) VALUES ('123           5');
 ERROR:  invalid input syntax for type double precision: "123           5"
 LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('123           5');
                                            ^
+-- special inputs
+SELECT 'NaN'::float8;
+ float8 
+--------
+    NaN
+(1 row)
+
+SELECT 'nan'::float8;
+ float8 
+--------
+    NaN
+(1 row)
+
+SELECT '   NAN  '::float8;
+ float8 
+--------
+    NaN
+(1 row)
+
+SELECT 'infinity'::float8;
+  float8  
+----------
+ Infinity
+(1 row)
+
+SELECT '          -INFINiTY   '::float8;
+  float8   
+-----------
+ -Infinity
+(1 row)
+
 -- bad special inputs
 SELECT 'N A N'::float8;
 ERROR:  invalid input syntax for type double precision: "N A N"
@@ -70,12 +101,42 @@ SELECT ' INFINITY    x'::float8;
 ERROR:  invalid input syntax for type double precision: " INFINITY    x"
 LINE 1: SELECT ' INFINITY    x'::float8;
                ^
+SELECT 'Infinity'::float8 + 100.0;
+ ?column? 
+----------
+ Infinity
+(1 row)
+
+SELECT 'Infinity'::float8 / 'Infinity'::float8;
+ ?column? 
+----------
+      NaN
+(1 row)
+
 SELECT '42'::float8 / 'Infinity'::float8;
  ?column? 
 ----------
         0
 (1 row)
 
+SELECT 'nan'::float8 / 'nan'::float8;
+ ?column? 
+----------
+      NaN
+(1 row)
+
+SELECT 'nan'::float8 / '0'::float8;
+ ?column? 
+----------
+      NaN
+(1 row)
+
+SELECT 'nan'::numeric::float8;
+ float8 
+--------
+    NaN
+(1 row)
+
 SELECT * FROM FLOAT8_TBL;
           f1          
 ----------------------
@@ -240,6 +301,30 @@ SELECT power(float8 '144', float8 '0.5');
     12
 (1 row)
 
+SELECT power(float8 'NaN', float8 '0.5');
+ power 
+-------
+   NaN
+(1 row)
+
+SELECT power(float8 '144', float8 'NaN');
+ power 
+-------
+   NaN
+(1 row)
+
+SELECT power(float8 'NaN', float8 'NaN');
+ power 
+-------
+   NaN
+(1 row)
+
+SELECT power(float8 '-1', float8 'NaN');
+ power 
+-------
+   NaN
+(1 row)
+
 SELECT power(float8 '1', float8 'NaN');
  power 
 -------
@@ -308,6 +393,30 @@ SELECT power(float8 '-0.1', float8 'inf');
      0
 (1 row)
 
+SELECT power(float8 '1.1', float8 'inf');
+  power   
+----------
+ Infinity
+(1 row)
+
+SELECT power(float8 '-1.1', float8 'inf');
+  power   
+----------
+ Infinity
+(1 row)
+
+SELECT power(float8 '0.1', float8 '-inf');
+  power   
+----------
+ Infinity
+(1 row)
+
+SELECT power(float8 '-0.1', float8 '-inf');
+  power   
+----------
+ Infinity
+(1 row)
+
 SELECT power(float8 '1.1', float8 '-inf');
  power 
 -------
@@ -326,6 +435,18 @@ SELECT power(float8 'inf', float8 '-2');
      0
 (1 row)
 
+SELECT power(float8 'inf', float8 '2');
+  power   
+----------
+ Infinity
+(1 row)
+
+SELECT power(float8 'inf', float8 'inf');
+  power   
+----------
+ Infinity
+(1 row)
+
 SELECT power(float8 'inf', float8 '-inf');
  power 
 -------
@@ -346,14 +467,39 @@ SELECT power(float8 '-inf', float8 '-3');
     -0
 (1 row)
 
+SELECT power(float8 '-inf', float8 '2');
+  power   
+----------
+ Infinity
+(1 row)
+
+SELECT power(float8 '-inf', float8 '3');
+   power   
+-----------
+ -Infinity
+(1 row)
+
 SELECT power(float8 '-inf', float8 '3.5');
 ERROR:  a negative number raised to a non-integer power yields a complex result
+SELECT power(float8 '-inf', float8 'inf');
+  power   
+----------
+ Infinity
+(1 row)
+
 SELECT power(float8 '-inf', float8 '-inf');
  power 
 -------
      0
 (1 row)
 
+-- check edge cases for exp
+SELECT exp('inf'::float8), exp('-inf'::float8), exp('nan'::float8);
+   exp    | exp | exp 
+----------+-----+-----
+ Infinity |   0 | NaN
+(1 row)
+
 SELECT * FROM FLOAT8_TBL;
           f1          
 ----------------------
@@ -386,6 +532,43 @@ SELECT asinh(float8 '1');
  0.881373587019543
 (1 row)
 
+-- test Inf/NaN cases for hyperbolic functions
+SELECT sinh(float8 'infinity');
+   sinh   
+----------
+ Infinity
+(1 row)
+
+SELECT sinh(float8 '-infinity');
+   sinh    
+-----------
+ -Infinity
+(1 row)
+
+SELECT sinh(float8 'nan');
+ sinh 
+------
+  NaN
+(1 row)
+
+SELECT cosh(float8 'infinity');
+   cosh   
+----------
+ Infinity
+(1 row)
+
+SELECT cosh(float8 '-infinity');
+   cosh   
+----------
+ Infinity
+(1 row)
+
+SELECT cosh(float8 'nan');
+ cosh 
+------
+  NaN
+(1 row)
+
 SELECT tanh(float8 'infinity');
  tanh 
 ------
@@ -398,14 +581,50 @@ SELECT tanh(float8 '-infinity');
    -1
 (1 row)
 
+SELECT tanh(float8 'nan');
+ tanh 
+------
+  NaN
+(1 row)
+
+SELECT asinh(float8 'infinity');
+  asinh   
+----------
+ Infinity
+(1 row)
+
+SELECT asinh(float8 '-infinity');
+   asinh   
+-----------
+ -Infinity
+(1 row)
+
+SELECT asinh(float8 'nan');
+ asinh 
+-------
+   NaN
+(1 row)
+
 -- acosh(Inf) should be Inf, but some mingw versions produce NaN, so skip test
 -- SELECT acosh(float8 'infinity');
 SELECT acosh(float8 '-infinity');
 ERROR:  input is out of range
+SELECT acosh(float8 'nan');
+ acosh 
+-------
+   NaN
+(1 row)
+
 SELECT atanh(float8 'infinity');
 ERROR:  input is out of range
 SELECT atanh(float8 '-infinity');
 ERROR:  input is out of range
+SELECT atanh(float8 'nan');
+ atanh 
+-------
+   NaN
+(1 row)
+
 RESET extra_float_digits;
 -- test for over- and underflow
 INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
diff --git a/ydb/library/yql/tests/postgresql/cases/float8.sql b/ydb/library/yql/tests/postgresql/cases/float8.sql
index 334a80f800..2ae7768bf8 100644
--- a/ydb/library/yql/tests/postgresql/cases/float8.sql
+++ b/ydb/library/yql/tests/postgresql/cases/float8.sql
@@ -21,11 +21,22 @@ INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
 INSERT INTO FLOAT8_TBL(f1) VALUES ('5.   0');
 INSERT INTO FLOAT8_TBL(f1) VALUES ('    - 3');
 INSERT INTO FLOAT8_TBL(f1) VALUES ('123           5');
+-- special inputs
+SELECT 'NaN'::float8;
+SELECT 'nan'::float8;
+SELECT '   NAN  '::float8;
+SELECT 'infinity'::float8;
+SELECT '          -INFINiTY   '::float8;
 -- bad special inputs
 SELECT 'N A N'::float8;
 SELECT 'NaN x'::float8;
 SELECT ' INFINITY    x'::float8;
+SELECT 'Infinity'::float8 + 100.0;
+SELECT 'Infinity'::float8 / 'Infinity'::float8;
 SELECT '42'::float8 / 'Infinity'::float8;
+SELECT 'nan'::float8 / 'nan'::float8;
+SELECT 'nan'::float8 / '0'::float8;
+SELECT 'nan'::numeric::float8;
 SELECT * FROM FLOAT8_TBL;
 SELECT f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
 SELECT f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
@@ -63,6 +74,10 @@ SELECT sqrt(float8 '64') AS eight;
 SELECT |/ float8 '64' AS eight;
 -- power
 SELECT power(float8 '144', float8 '0.5');
+SELECT power(float8 'NaN', float8 '0.5');
+SELECT power(float8 '144', float8 'NaN');
+SELECT power(float8 'NaN', float8 'NaN');
+SELECT power(float8 '-1', float8 'NaN');
 SELECT power(float8 '1', float8 'NaN');
 SELECT power(float8 'NaN', float8 '0');
 SELECT power(float8 'inf', float8 '0');
@@ -75,16 +90,27 @@ SELECT power(float8 '-1', float8 'inf');
 SELECT power(float8 '-1', float8 '-inf');
 SELECT power(float8 '0.1', float8 'inf');
 SELECT power(float8 '-0.1', float8 'inf');
+SELECT power(float8 '1.1', float8 'inf');
+SELECT power(float8 '-1.1', float8 'inf');
+SELECT power(float8 '0.1', float8 '-inf');
+SELECT power(float8 '-0.1', float8 '-inf');
 SELECT power(float8 '1.1', float8 '-inf');
 SELECT power(float8 '-1.1', float8 '-inf');
 SELECT power(float8 'inf', float8 '-2');
+SELECT power(float8 'inf', float8 '2');
+SELECT power(float8 'inf', float8 'inf');
 SELECT power(float8 'inf', float8 '-inf');
 -- Intel's icc misoptimizes the code that controls the sign of this result,
 -- even with -mp1.  Pending a fix for that, only test for "is it zero".
 SELECT power(float8 '-inf', float8 '-2') = '0';
 SELECT power(float8 '-inf', float8 '-3');
+SELECT power(float8 '-inf', float8 '2');
+SELECT power(float8 '-inf', float8 '3');
 SELECT power(float8 '-inf', float8 '3.5');
+SELECT power(float8 '-inf', float8 'inf');
 SELECT power(float8 '-inf', float8 '-inf');
+-- check edge cases for exp
+SELECT exp('inf'::float8), exp('-inf'::float8), exp('nan'::float8);
 SELECT * FROM FLOAT8_TBL;
 SELECT f.f1 * '1e200' from FLOAT8_TBL f;
 SELECT f.f1 ^ '1e200' from FLOAT8_TBL f;
@@ -93,13 +119,26 @@ SELECT ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ;
 SELECT ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ;
 SELECT f.f1 / '0.0' from FLOAT8_TBL f;
 SELECT asinh(float8 '1');
+-- test Inf/NaN cases for hyperbolic functions
+SELECT sinh(float8 'infinity');
+SELECT sinh(float8 '-infinity');
+SELECT sinh(float8 'nan');
+SELECT cosh(float8 'infinity');
+SELECT cosh(float8 '-infinity');
+SELECT cosh(float8 'nan');
 SELECT tanh(float8 'infinity');
 SELECT tanh(float8 '-infinity');
+SELECT tanh(float8 'nan');
+SELECT asinh(float8 'infinity');
+SELECT asinh(float8 '-infinity');
+SELECT asinh(float8 'nan');
 -- acosh(Inf) should be Inf, but some mingw versions produce NaN, so skip test
 -- SELECT acosh(float8 'infinity');
 SELECT acosh(float8 '-infinity');
+SELECT acosh(float8 'nan');
 SELECT atanh(float8 'infinity');
 SELECT atanh(float8 '-infinity');
+SELECT atanh(float8 'nan');
 RESET extra_float_digits;
 -- test for over- and underflow
 INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
diff --git a/ydb/library/yql/tests/postgresql/cases/numeric.out b/ydb/library/yql/tests/postgresql/cases/numeric.out
index a4abce3d26..747a4a3593 100644
--- a/ydb/library/yql/tests/postgresql/cases/numeric.out
+++ b/ydb/library/yql/tests/postgresql/cases/numeric.out
@@ -804,6 +804,24 @@ SELECT '-Infinity'::float8::numeric;
  -Infinity
 (1 row)
 
+SELECT 'NaN'::numeric::float8;
+ float8 
+--------
+    NaN
+(1 row)
+
+SELECT 'Infinity'::numeric::float8;
+  float8  
+----------
+ Infinity
+(1 row)
+
+SELECT '-Infinity'::numeric::float8;
+  float8   
+-----------
+ -Infinity
+(1 row)
+
 SELECT 'NaN'::float4::numeric;
  numeric 
 ---------
@@ -822,6 +840,24 @@ SELECT '-Infinity'::float4::numeric;
  -Infinity
 (1 row)
 
+SELECT 'NaN'::numeric::float4;
+ float4 
+--------
+    NaN
+(1 row)
+
+SELECT 'Infinity'::numeric::float4;
+  float4  
+----------
+ Infinity
+(1 row)
+
+SELECT '-Infinity'::numeric::float4;
+  float4   
+-----------
+ -Infinity
+(1 row)
+
 SELECT '42'::int2::numeric;
  numeric 
 ---------
diff --git a/ydb/library/yql/tests/postgresql/cases/numeric.sql b/ydb/library/yql/tests/postgresql/cases/numeric.sql
index 97886c17c4..1c74b6e46f 100644
--- a/ydb/library/yql/tests/postgresql/cases/numeric.sql
+++ b/ydb/library/yql/tests/postgresql/cases/numeric.sql
@@ -607,9 +607,15 @@ SELECT 32767.5::int2; -- should fail
 SELECT 'NaN'::float8::numeric;
 SELECT 'Infinity'::float8::numeric;
 SELECT '-Infinity'::float8::numeric;
+SELECT 'NaN'::numeric::float8;
+SELECT 'Infinity'::numeric::float8;
+SELECT '-Infinity'::numeric::float8;
 SELECT 'NaN'::float4::numeric;
 SELECT 'Infinity'::float4::numeric;
 SELECT '-Infinity'::float4::numeric;
+SELECT 'NaN'::numeric::float4;
+SELECT 'Infinity'::numeric::float4;
+SELECT '-Infinity'::numeric::float4;
 SELECT '42'::int2::numeric;
 SELECT 'NaN'::numeric::int2;
 SELECT 'Infinity'::numeric::int2;
diff --git a/ydb/library/yql/tests/postgresql/pg_tests.csv b/ydb/library/yql/tests/postgresql/pg_tests.csv
index a84fdc66b7..0bf4957bec 100644
--- a/ydb/library/yql/tests/postgresql/pg_tests.csv
+++ b/ydb/library/yql/tests/postgresql/pg_tests.csv
@@ -1,5 +1,5 @@
 testcase,statements,successful,ratio
-aggregates,416,51,12.26
+aggregates,416,66,15.87
 alter_table,1679,11,0.66
 arrays,410,119,29.02
 bit,115,84,73.04
@@ -13,8 +13,8 @@ date,264,200,75.76
 dbsize,24,24,100.0
 delete,10,0,0.0
 expressions,63,14,22.22
-float4,96,48,50.0
-float8,168,96,57.14
+float4,96,66,68.75
+float8,168,132,78.57
 functional_deps,40,7,17.5
 horology,306,79,25.82
 insert,357,15,4.2
@@ -31,7 +31,7 @@ jsonpath,169,152,89.94
 jsonpath_encoding,31,31,100.0
 limit,84,5,5.95
 name,40,22,55.0
-numeric,915,715,78.14
+numeric,915,721,78.8
 numerology,24,8,33.33
 oid,27,27,100.0
 select,88,9,10.23
diff --git a/ydb/library/yql/tests/postgresql/status.md b/ydb/library/yql/tests/postgresql/status.md
index c47b7743ae..27a2cd2a0e 100644
--- a/ydb/library/yql/tests/postgresql/status.md
+++ b/ydb/library/yql/tests/postgresql/status.md
@@ -11,10 +11,10 @@
 || 7 | int4 | 70 | 70 | 100.0 | 29.09.2023 | ||
 || 8 | int8 | 142 | 113 (+65) | 79.58 | 22.01.2024 | YQL-17614 ||
 || 9 | oid | 27 | 27 (+6) | 100.0 | 22.01.2024 | YQL-17623 ||
-|| 10 | float4 | 96 | 48 | 50.0 | 29.09.2023 | CREATE TYPE, CREATE FUNCTION, WITH, форматирование NaN и Infinity, float4send ||
-|| 11 | float8 | 168 | 96 | 57.14 | 25.10.2023 | CREATE CAST, форматирование NaN и Infinity, extra_float_digits, implicit casts, float8send ||
+|| 10 | float4 | 96 | 66 (+18) | 68.75 | 23.01.2024 | YQL-17586 ||
+|| 11 | float8 | 168 | 132 (+36) | 78.57 | 23.01.2024 | YQL-17628 ||
 || 12 | bit | 115 | 84 | 73.04 | 12.12.2023 | substring, COPY  FROM stdin, битовые константы ||
-|| 13 | numeric | 915 | 715 | 78.14 | 12.12.2023 | CREATE UNIQUE INDEX, VACUUM ANALYZE, implicit casts, ошибочно проходит cast в int2 и int8, форматирование NaN и Infinity, COPY FROM stdin, SET lc_numeric, умножение больших целых чисел не дает в результате число с плавающей точкой, sum(), округление, nullif, форматирование чисел ||
+|| 13 | numeric | 915 | 721 (+6) | 78.8 | 23.01.2024 | YQL-17629 ||
 || 14 | uuid | 36 | 0 | 0.0 | 02.05.2023 | ||·
 || 15 | strings | 390 | 31 | 7.95 | 25.08.2023 | SET, RESET, standard_conforming_strings, bytea_output, неинициализированная поддержка регулярок, pg_class  ||
 || 16 | numerology | 24 | 8 | 33.33 | 26.07.2023 |  ||
@@ -41,7 +41,7 @@
 || 37 | union | 186 | 0 | 0.0 | 25.05.2023 | ||
 || 38 | case | 63 | 29 | 46.03 | 12.12.2023 | implicit casts, create function volatile ||
 || 39 | join | 591 | 106 | 17.94 | 12.12.2023 | ||
-|| 40 | aggregates | 416 | 51 | 12.26 | 12.12.2023 | ||
+|| 40 | aggregates | 416 | 66 (+15) | 15.87 | 23.01.2024 | ||
 || 41 | arrays | 410 | 119 | 29.02 | 12.12.2023 | ||
 || 42 | update | 288 | 22 | 7.64 | 12.12.2023 | :-переменные ||
 || 43 | delete | 10 | 0 | 0.0 | 25.05.2023 | ||
diff --git a/ydb/library/yql/tests/postgresql/status.old b/ydb/library/yql/tests/postgresql/status.old
index d7ced1fd1e..e065b9e647 100644
--- a/ydb/library/yql/tests/postgresql/status.old
+++ b/ydb/library/yql/tests/postgresql/status.old
@@ -10,11 +10,11 @@
 || 6 | int2 | 49 | 47 | 95.92 | 29.09.2023 | YQL-17612 ||
 || 7 | int4 | 70 | 70 | 100.0 | 29.09.2023 | ||
 || 8 | int8 | 142 | 113 (+65) | 79.58 | 22.01.2024 | YQL-17614 ||
-|| 9 | oid | 27 | 21 | 77.78 | 29.09.2023 | YQL-17623 ||
-|| 10 | float4 | 96 | 48 | 50.0 | 29.09.2023 | CREATE TYPE, CREATE FUNCTION, WITH, форматирование NaN и Infinity, float4send ||
-|| 11 | float8 | 168 | 96 | 57.14 | 25.10.2023 | CREATE CAST, форматирование NaN и Infinity, extra_float_digits, implicit casts, float8send ||
+|| 9 | oid | 27 | 27 (+6) | 100.0 | 22.01.2024 | YQL-17623 ||
+|| 10 | float4 | 96 | 48 | 50.0 | 29.09.2023 | YQL-17586 ||
+|| 11 | float8 | 168 | 96 | 57.14 | 25.10.2023 | YQL-17628 ||
 || 12 | bit | 115 | 84 | 73.04 | 12.12.2023 | substring, COPY  FROM stdin, битовые константы ||
-|| 13 | numeric | 915 | 715 | 78.14 | 12.12.2023 | CREATE UNIQUE INDEX, VACUUM ANALYZE, implicit casts, ошибочно проходит cast в int2 и int8, форматирование NaN и Infinity, COPY FROM stdin, SET lc_numeric, умножение больших целых чисел не дает в результате число с плавающей точкой, sum(), округление, nullif, форматирование чисел ||
+|| 13 | numeric | 915 | 715 | 78.14 | 12.12.2023 | YQL-17629 ||
 || 14 | uuid | 36 | 0 | 0.0 | 02.05.2023 | ||·
 || 15 | strings | 390 | 31 | 7.95 | 25.08.2023 | SET, RESET, standard_conforming_strings, bytea_output, неинициализированная поддержка регулярок, pg_class  ||
 || 16 | numerology | 24 | 8 | 33.33 | 26.07.2023 |  ||
diff --git a/ydb/library/yql/tools/pgrun/pgrun.cpp b/ydb/library/yql/tools/pgrun/pgrun.cpp
index f5a54d15d1..89c735ac96 100644
--- a/ydb/library/yql/tools/pgrun/pgrun.cpp
+++ b/ydb/library/yql/tools/pgrun/pgrun.cpp
@@ -734,6 +734,35 @@ inline const TString FormatNumeric(const TString& value)
     return (value == "0") ? Zero : value;
 }
 
+const TString FormatFloat(const TString& value, std::function<TString(const TString&)> formatter) {
+    static const TString nan = "NaN";
+    static const TString inf = "Infinity";
+    static const TString minf = "-Infinity";
+
+    try {
+        return (value == "") ? ""
+             : (value == "nan") ? nan
+             : (value == "inf") ? inf
+             : (value == "-inf") ? minf
+             : formatter(value);
+    } catch (const std::exception& e) {
+        Cerr << "Unexpected float value '" << value << "'\n";
+        return "";
+    }
+}
+
+inline const TString FormatFloat4(const TString& value)
+{
+    return FormatFloat(value,
+        [] (const TString& val) { return TString(fmt::format("{0}", std::stof(val))); });
+}
+
+inline const TString FormatFloat8(const TString& value)
+{
+    return FormatFloat(value,
+        [] (const TString& val) { return TString(fmt::format("{0}", std::stod(val))); });
+}
+
 inline const TString FormatTransparent(const TString& value)
 {
     return value;
@@ -742,6 +771,8 @@ inline const TString FormatTransparent(const TString& value)
 static const THashMap<TColumnType, CellFormatter> ColumnFormatters {
     { "bool", FormatBool },
     { "numeric", FormatNumeric },
+    { "float4", FormatFloat4 },
+    { "float8", FormatFloat8 },
 };
 
 static const THashSet<TColumnType> RightAlignedTypes {