YQL-19206 Move contrib/ydb/library/yql/tests/postgresql -> yql/essentials/tests/postgresql

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 434 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/float4.out b/yql/essentials/tests/postgresql/cases/float4.out
new file mode 100644
index 0000000000..c4128d4416
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/float4.out
@@ -0,0 +1,434 @@
+--
+-- FLOAT4
+--
+CREATE TABLE FLOAT4_TBL (f1  float4);
+INSERT INTO FLOAT4_TBL(f1) VALUES ('    0.0');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30   ');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('     -34.84    ');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
+-- test for over and under flow
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70');
+ERROR:  "10e70" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70');
+ERROR:  "-10e70" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70');
+ERROR:  "10e-70" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70');
+ERROR:  "-10e-70" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70'::float8);
+ERROR:  value out of range: overflow
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70'::float8);
+ERROR:  value out of range: overflow
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70'::float8);
+ERROR:  value out of range: underflow
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70'::float8);
+ERROR:  value out of range: underflow
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e400');
+ERROR:  "10e400" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e400');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e400');
+ERROR:  "-10e400" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e400');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-400');
+ERROR:  "10e-400" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-400');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-400');
+ERROR:  "-10e-400" is out of range for type real
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-400');
+                                           ^
+-- bad input
+INSERT INTO FLOAT4_TBL(f1) VALUES ('');
+ERROR:  invalid input syntax for type real: ""
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('       ');
+ERROR:  invalid input syntax for type real: "       "
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('       ');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+ERROR:  invalid input syntax for type real: "xyz"
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+ERROR:  invalid input syntax for type real: "5.0.0"
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+ERROR:  invalid input syntax for type real: "5 . 0"
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('5.   0');
+ERROR:  invalid input syntax for type real: "5.   0"
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5.   0');
+                                           ^
+INSERT INTO FLOAT4_TBL(f1) VALUES ('     - 3.0');
+ERROR:  invalid input syntax for type real: "     - 3.0"
+LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('     - 3.0');
+                                           ^
+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"
+LINE 1: SELECT 'N A N'::float4;
+               ^
+SELECT 'NaN x'::float4;
+ERROR:  invalid input syntax for type real: "NaN x"
+LINE 1: SELECT 'NaN x'::float4;
+               ^
+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   
+--------
+ 1004.3
+(1 row)
+
+SELECT f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
+      f1       
+---------------
+             0
+        -34.84
+ 1.2345679e-20
+(3 rows)
+
+SELECT f.* FROM FLOAT4_TBL f WHERE  f.f1 < '1004.3';
+      f1       
+---------------
+             0
+        -34.84
+ 1.2345679e-20
+(3 rows)
+
+SELECT f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
+      f1       
+---------------
+             0
+        1004.3
+        -34.84
+ 1.2345679e-20
+(4 rows)
+
+SELECT f.* FROM FLOAT4_TBL f WHERE  f.f1 <= '1004.3';
+      f1       
+---------------
+             0
+        1004.3
+        -34.84
+ 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 |        -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  
+-------
+ 32767
+(1 row)
+
+SELECT '32767.6'::float4::int2;
+ERROR:  smallint out of range
+SELECT '-32768.4'::float4::int2;
+  int2  
+--------
+ -32768
+(1 row)
+
+SELECT '-32768.6'::float4::int2;
+ERROR:  smallint out of range
+SELECT '2147483520'::float4::int4;
+    int4    
+------------
+ 2147483520
+(1 row)
+
+SELECT '2147483647'::float4::int4;
+ERROR:  integer out of range
+SELECT '-2147483648.5'::float4::int4;
+    int4     
+-------------
+ -2147483648
+(1 row)
+
+SELECT '-2147483900'::float4::int4;
+ERROR:  integer out of range
+SELECT '9223369837831520256'::float4::int8;
+        int8         
+---------------------
+ 9223369837831520256
+(1 row)
+
+SELECT '9223372036854775807'::float4::int8;
+ERROR:  bigint out of range
+SELECT '-9223372036854775808.5'::float4::int8;
+         int8         
+----------------------
+ -9223372036854775808
+(1 row)
+
+SELECT '-9223380000000000000'::float4::int8;
+ERROR:  bigint out of range
+-- Test for correct input rounding in edge cases.
+-- These lists are from Paxson 1991, excluding subnormals and
+-- inputs of over 9 sig. digits.
+SELECT float4send('5e-20'::float4);
+ float4send 
+------------
+ \x1f6c1e4a
+(1 row)
+
+SELECT float4send('67e14'::float4);
+ float4send 
+------------
+ \x59be6cea
+(1 row)
+
+SELECT float4send('985e15'::float4);
+ float4send 
+------------
+ \x5d5ab6c4
+(1 row)
+
+SELECT float4send('55895e-16'::float4);
+ float4send 
+------------
+ \x2cc4a9bd
+(1 row)
+
+SELECT float4send('7038531e-32'::float4);
+ float4send 
+------------
+ \x15ae43fd
+(1 row)
+
+SELECT float4send('702990899e-20'::float4);
+ float4send 
+------------
+ \x2cf757ca
+(1 row)
+
+SELECT float4send('3e-23'::float4);
+ float4send 
+------------
+ \x1a111234
+(1 row)
+
+SELECT float4send('57e18'::float4);
+ float4send 
+------------
+ \x6045c22c
+(1 row)
+
+SELECT float4send('789e-35'::float4);
+ float4send 
+------------
+ \x0a23de70
+(1 row)
+
+SELECT float4send('2539e-18'::float4);
+ float4send 
+------------
+ \x2736f449
+(1 row)
+
+SELECT float4send('76173e28'::float4);
+ float4send 
+------------
+ \x7616398a
+(1 row)
+
+SELECT float4send('887745e-11'::float4);
+ float4send 
+------------
+ \x3714f05c
+(1 row)
+
+SELECT float4send('5382571e-37'::float4);
+ float4send 
+------------
+ \x0d2eaca7
+(1 row)
+
+SELECT float4send('82381273e-35'::float4);
+ float4send 
+------------
+ \x128289d1
+(1 row)
+
+SELECT float4send('750486563e-38'::float4);
+ float4send 
+------------
+ \x0f18377e
+(1 row)
+
+-- Test that the smallest possible normalized input value inputs
+-- correctly, either in 9-significant-digit or shortest-decimal
+-- format.
+--
+-- exact val is             1.1754943508...
+-- shortest val is          1.1754944000
+-- midpoint to next val is  1.1754944208...
+SELECT float4send('1.17549435e-38'::float4);
+ float4send 
+------------
+ \x00800000
+(1 row)
+
+SELECT float4send('1.1754944e-38'::float4);
+ float4send 
+------------
+ \x00800000
+(1 row)
+