Here are some patches to fix up the regression tests so that the union

test passes.  Interestingly, the fix involves no changes or special
cases in the union test and actually removes a special case for the
numerology test.  Thus, following the strategy outlined below is a
definite improvement over the previous situation.

Cheers,
Brook

4 files changed, 83 insertions, 180 deletions

diff --git a/src/test/regress/expected/float8-NetBSD.out b/src/test/regress/expected/float8-NetBSD.out
index e0d1a0c74b9..9d923ed8e0b 100644
--- a/src/test/regress/expected/float8-NetBSD.out
+++ b/src/test/regress/expected/float8-NetBSD.out
@@ -4,12 +4,6 @@ QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
-ERROR:  Bad float8 input format '10e400'
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
-ERROR:  Bad float8 input format '-10e400'
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
 QUERY: SELECT '' AS five, FLOAT8_TBL.*;
 five|f1                  
 ----+--------------------
@@ -18,9 +12,7 @@ five|f1
     |-34.84              
     |1.2345678901234e+200
     |1.2345678901234e-200
-    |0                   
-    |0                   
-(7 rows)
+(5 rows)
 
 QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
 four|f1                  
@@ -29,9 +21,7 @@ four|f1
     |-34.84              
     |1.2345678901234e+200
     |1.2345678901234e-200
-    |0                   
-    |0                   
-(6 rows)
+(4 rows)
 
 QUERY: SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
 one|    f1
@@ -45,9 +35,7 @@ three|                  f1
      |                   0
      |              -34.84
      |1.2345678901234e-200
-     |                   0
-     |                   0
-(5 rows)
+(3 rows)
 
 QUERY: SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE  f.f1 < '1004.3';
 three|                  f1
@@ -55,9 +43,7 @@ three|                  f1
      |                   0
      |              -34.84
      |1.2345678901234e-200
-     |                   0
-     |                   0
-(5 rows)
+(3 rows)
 
 QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
 four|                  f1
@@ -66,9 +52,7 @@ four|                  f1
     |              1004.3
     |              -34.84
     |1.2345678901234e-200
-    |                   0
-    |                   0
-(6 rows)
+(4 rows)
 
 QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE  f.f1 <= '1004.3';
 four|                  f1
@@ -77,9 +61,7 @@ four|                  f1
     |              1004.3
     |              -34.84
     |1.2345678901234e-200
-    |                   0
-    |                   0
-(6 rows)
+(4 rows)
 
 QUERY: SELECT '' AS three, f.f1, f.f1 * '-10' AS x
    FROM FLOAT8_TBL f
@@ -137,9 +119,7 @@ five|f1                  |abs_f1
     |-34.84              |34.84               
     |1.2345678901234e+200|1.2345678901234e+200
     |1.2345678901234e-200|1.2345678901234e-200
-    |0                   |0                   
-    |0                   |0                   
-(7 rows)
+(5 rows)
 
 QUERY: SELECT '' AS five, f.f1, %f.f1 AS trunc_f1
    FROM FLOAT8_TBL f;
@@ -150,9 +130,7 @@ five|f1                  |trunc_f1
     |-34.84              |-34                 
     |1.2345678901234e+200|1.2345678901234e+200
     |1.2345678901234e-200|0                   
-    |0                   |0                   
-    |0                   |0                   
-(7 rows)
+(5 rows)
 
 QUERY: SELECT '' AS five, f.f1, f.f1 % AS round_f1
    FROM FLOAT8_TBL f;
@@ -163,9 +141,7 @@ five|f1                  |round_f1
     |-34.84              |-35                 
     |1.2345678901234e+200|1.2345678901234e+200
     |1.2345678901234e-200|0                   
-    |0                   |0                   
-    |0                   |0                   
-(7 rows)
+(5 rows)
 
 QUERY: SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
    FROM FLOAT8_TBL f
@@ -195,9 +171,7 @@ five|f1                  |cbrt_f1
     |-34.84              |-3.26607421344208   
     |1.2345678901234e+200|4.97933859234765e+66
     |1.2345678901234e-200|2.3112042409018e-67 
-    |0                   |0                   
-    |0                   |0                   
-(7 rows)
+(5 rows)
 
 QUERY: SELECT '' AS five, FLOAT8_TBL.*;
 five|f1                  
@@ -207,9 +181,7 @@ five|f1
     |-34.84              
     |1.2345678901234e+200
     |1.2345678901234e-200
-    |0                   
-    |0                   
-(7 rows)
+(5 rows)
 
 QUERY: UPDATE FLOAT8_TBL
    SET f1 = FLOAT8_TBL.f1 * '-1'
@@ -231,10 +203,30 @@ five|f1
 ----+---------------------
     |0                    
     |-34.84               
+    |-1004.3              
+    |-1.2345678901234e+200
+    |-1.2345678901234e-200
+(5 rows)
+
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ERROR:  Bad float8 input format '10e400'
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ERROR:  Bad float8 input format '-10e400'
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+QUERY: DELETE FROM FLOAT8_TBL;
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
+QUERY: SELECT '' AS five, FLOAT8_TBL.*;
+five|f1                   
+----+---------------------
     |0                    
-    |0                    
+    |-34.84               
     |-1004.3              
     |-1.2345678901234e+200
     |-1.2345678901234e-200
-(7 rows)
+(5 rows)
 
diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out
index facfac9d706..ec9a98371f9 100644
--- a/src/test/regress/expected/float8.out
+++ b/src/test/regress/expected/float8.out
@@ -4,14 +4,6 @@ QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
 QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
-ERROR:  Bad float8 input format '10e400'
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
-ERROR:  Bad float8 input format '-10e400'
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
-ERROR:  Bad float8 input format '10e-400'
-QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
-ERROR:  Bad float8 input format '-10e-400'
 QUERY: SELECT '' AS five, FLOAT8_TBL.*;
 five|f1                  
 ----+--------------------
@@ -224,3 +216,27 @@ five|f1
     |-1.2345678901234e-200
 (5 rows)
 
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ERROR:  Bad float8 input format '10e400'
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ERROR:  Bad float8 input format '-10e400'
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ERROR:  Bad float8 input format '10e-400'
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+ERROR:  Bad float8 input format '-10e-400'
+QUERY: DELETE FROM FLOAT8_TBL;
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
+QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
+QUERY: SELECT '' AS five, FLOAT8_TBL.*;
+five|f1                   
+----+---------------------
+    |0                    
+    |-34.84               
+    |-1004.3              
+    |-1.2345678901234e+200
+    |-1.2345678901234e-200
+(5 rows)
+
diff --git a/src/test/regress/expected/numerology-NetBSD.out b/src/test/regress/expected/numerology-NetBSD.out
index 1f05e0753f3..e69de29bb2d 100644
--- a/src/test/regress/expected/numerology-NetBSD.out
+++ b/src/test/regress/expected/numerology-NetBSD.out
@@ -1,122 +0,0 @@
-QUERY: CREATE TABLE TEMP_FLOAT (f1 FLOAT8);
-QUERY: INSERT INTO TEMP_FLOAT (f1)
-  SELECT float8(f1) FROM INT4_TBL;
-QUERY: INSERT INTO TEMP_FLOAT (f1)
-  SELECT float8(f1) FROM INT2_TBL;
-QUERY: SELECT '' AS ten, f1 FROM TEMP_FLOAT
-  ORDER BY f1;
-ten|         f1
----+-----------
-   |-2147483647
-   |    -123456
-   |     -32767
-   |      -1234
-   |          0
-   |          0
-   |       1234
-   |      32767
-   |     123456
-   | 2147483647
-(10 rows)
-
-QUERY: CREATE TABLE TEMP_INT4 (f1 INT4);
-QUERY: INSERT INTO TEMP_INT4 (f1)
-  SELECT int4(f1) FROM FLOAT8_TBL
-  WHERE (f1 > -2147483647) AND (f1 < 2147483647);
-QUERY: INSERT INTO TEMP_INT4 (f1)
-  SELECT int4(f1) FROM INT2_TBL;
-QUERY: SELECT '' AS nine, f1 FROM TEMP_INT4
-  ORDER BY f1;
-nine|    f1
-----+------
-    |-32767
-    | -1234
-    | -1004
-    |   -35
-    |     0
-    |     0
-    |     0
-    |     0
-    |     0
-    |  1234
-    | 32767
-(11 rows)
-
-QUERY: CREATE TABLE TEMP_INT2 (f1 INT2);
-QUERY: INSERT INTO TEMP_INT2 (f1)
-  SELECT int2(f1) FROM FLOAT8_TBL
-  WHERE (f1 >= -32767) AND (f1 <= 32767);
-QUERY: INSERT INTO TEMP_INT2 (f1)
-  SELECT int2(f1) FROM INT4_TBL
-  WHERE (f1 >= -32767) AND (f1 <= 32767);
-QUERY: SELECT '' AS five, f1 FROM TEMP_INT2
-  ORDER BY f1;
-five|   f1
-----+-----
-    |-1004
-    |  -35
-    |    0
-    |    0
-    |    0
-    |    0
-    |    0
-(7 rows)
-
-QUERY: CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8);
-QUERY: INSERT INTO TEMP_GROUP
-  SELECT 1, (- i.f1), (- f.f1)
-  FROM INT4_TBL i, FLOAT8_TBL f;
-QUERY: INSERT INTO TEMP_GROUP
-  SELECT 2, i.f1, f.f1
-  FROM INT4_TBL i, FLOAT8_TBL f;
-QUERY: SELECT DISTINCT f1 AS two FROM TEMP_GROUP;
-two
----
-  1
-  2
-(2 rows)
-
-QUERY: SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float
-  FROM TEMP_GROUP
-  GROUP BY two
-  ORDER BY two, max_float, min_float;
-two|max_float           |min_float            
----+--------------------+---------------------
-  1|1.2345678901234e+200|0                    
-  2|0                   |-1.2345678901234e+200
-(2 rows)
-
-QUERY: SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float
-  FROM TEMP_GROUP
-  GROUP BY two
-  ORDER BY two, max_float, min_float;
-two|max_float           |min_float            
----+--------------------+---------------------
-  1|1.2345678901234e+200|0                    
-  2|0                   |-1.2345678901234e+200
-(2 rows)
-
-QUERY: SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
-  FROM TEMP_GROUP
-  GROUP BY two
-  ORDER BY two, min_minus_1;
-two|max_plus_1          |min_minus_1          
----+--------------------+---------------------
-  1|1.2345678901234e+200|-1                   
-  2|1                   |-1.2345678901234e+200
-(2 rows)
-
-QUERY: SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
-  FROM TEMP_GROUP
-  GROUP BY two
-  ORDER BY two, min_minus_1;
-two|max_plus_1          |min_minus_1          
----+--------------------+---------------------
-  1|1.2345678901234e+200|-1                   
-  2|1                   |-1.2345678901234e+200
-(2 rows)
-
-QUERY: DROP TABLE TEMP_INT2;
-QUERY: DROP TABLE TEMP_INT4;
-QUERY: DROP TABLE TEMP_FLOAT;
-QUERY: DROP TABLE TEMP_GROUP;
diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql
index 820d36b08a1..bc41a610859 100644
--- a/src/test/regress/sql/float8.sql
+++ b/src/test/regress/sql/float8.sql
@@ -12,15 +12,6 @@ INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
 
 INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
 
--- test for over and under flow 
-INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
-
-INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
-
-INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
-
-INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
-
 
 SELECT '' AS five, FLOAT8_TBL.*;
 
@@ -101,3 +92,29 @@ SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
 
 SELECT '' AS five, FLOAT8_TBL.*;
 
+-- test for over and under flow 
+INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+
+-- maintain external table consistency across platforms
+-- delete all values and reinsert well-behaved ones
+
+DELETE FROM FLOAT8_TBL;
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
+
+SELECT '' AS five, FLOAT8_TBL.*;
+