I have finally resolved the differences in the union regression test.
The problem is that the test uses the float8 table as part of the
test, and this table turns out to be different among platforms because
of different behavior with small numbers.  Since the float8 part of
testing unions seemed entirely irrelevant I have changed it to use
float4, which is not different among platforms.  This will prevent the
proliferation of special case files, while still properly testing the
union code (correct me if there really is something about float8 that
is important).

This raises an issue I had never considered before concerning the
development of regression tests.  Perhaps something along the lines of
the following can be added to the developers FAQ.

     When developing regression tests there is often a choice about
     what tables in the regression database to obtain data from.  In
     most cases which table is used does not matter.  However, some
     tables contain different data depending on the platform and must
     be special-cased for verifying the regression tests.  These
     tables can be recognized by looking for the
     test/regress/expected/*-PLATFORM.out files, where PLATFORM is a
     particular supported platform.  Whenever possible, other
     regression tests should avoid using data from these tables, or
     special cases will inevitably proliferate and the regression
     tests will be unnecessarily difficult to verify.

Cheers,
Brook

===========================================================================
--- test/regress/sql/union.sql.orig	Fri May 29 07:23:02 1998
+++ test/regress/sql/union.sql	Wed Sep 16 08:26:01 1998
@@ -42,23 +42,23 @@
 -- Try testing from tables...
 --
 
-SELECT f1 AS five FROM FLOAT8_TBL
+SELECT f1 AS five FROM FLOAT4_TBL
 UNION
-SELECT f1 FROM FLOAT8_TBL;
+SELECT f1 FROM FLOAT4_TBL;
 
-SELECT f1 AS ten FROM FLOAT8_TBL
+SELECT f1 AS ten FROM FLOAT4_TBL
 UNION ALL
-SELECT f1 FROM FLOAT8_TBL;
+SELECT f1 FROM FLOAT4_TBL;
 
-SELECT f1 AS nine FROM FLOAT8_TBL
+SELECT f1 AS nine FROM FLOAT4_TBL
 UNION
 SELECT f1 FROM INT4_TBL;
 
-SELECT f1 AS ten FROM FLOAT8_TBL
+SELECT f1 AS ten FROM FLOAT4_TBL
 UNION ALL
 SELECT f1 FROM INT4_TBL;
 
-SELECT f1 AS five FROM FLOAT8_TBL
+SELECT f1 AS five FROM FLOAT4_TBL
   WHERE f1 BETWEEN -1e6 AND 1e6
 UNION
 SELECT f1 FROM INT4_TBL
===========================================================================
--- test/regress/expected/union.out.orig	Fri May 29 07:22:42 1998
+++ test/regress/expected/union.out	Wed Sep 16 10:03:17 1998
@@ -112,80 +112,80 @@
     2
 (3 rows)
 
-QUERY: SELECT f1 AS five FROM FLOAT8_TBL
+QUERY: SELECT f1 AS five FROM FLOAT4_TBL
 UNION
-SELECT f1 FROM FLOAT8_TBL;
-five                 
----------------------
--1.2345678901234e+200
--1004.3              
--34.84               
--1.2345678901234e-200
-0                    
+SELECT f1 FROM FLOAT4_TBL;
+five        
+------------
+-1.23457e+20
+-1004.3     
+-34.84      
+-1.23457e-20
+0           
 (5 rows)
 
-QUERY: SELECT f1 AS ten FROM FLOAT8_TBL
+QUERY: SELECT f1 AS ten FROM FLOAT4_TBL
 UNION ALL
-SELECT f1 FROM FLOAT8_TBL;
-ten                  
----------------------
-0                    
--34.84               
--1004.3              
--1.2345678901234e+200
--1.2345678901234e-200
-0                    
--34.84               
--1004.3              
--1.2345678901234e+200
--1.2345678901234e-200
+SELECT f1 FROM FLOAT4_TBL;
+ten         
+------------
+0           
+-34.84      
+-1004.3     
+-1.23457e+20
+-1.23457e-20
+0           
+-34.84      
+-1004.3     
+-1.23457e+20
+-1.23457e-20
 (10 rows)
 
-QUERY: SELECT f1 AS nine FROM FLOAT8_TBL
+QUERY: SELECT f1 AS nine FROM FLOAT4_TBL
 UNION
 SELECT f1 FROM INT4_TBL;
-nine                 
----------------------
--1.2345678901234e+200
--2147483647          
--123456              
--1004.3              
--34.84               
--1.2345678901234e-200
-0                    
-123456               
-2147483647           
+nine        
+------------
+-1.23457e+20
+-2.14748e+09
+-123456     
+-1004.3     
+-34.84      
+-1.23457e-20
+0           
+123456      
+2.14748e+09 
 (9 rows)
 
-QUERY: SELECT f1 AS ten FROM FLOAT8_TBL
+QUERY: SELECT f1 AS ten FROM FLOAT4_TBL
 UNION ALL
 SELECT f1 FROM INT4_TBL;
-ten                  
----------------------
-0                    
--34.84               
--1004.3              
--1.2345678901234e+200
--1.2345678901234e-200
-0                    
-123456               
--123456              
-2147483647           
--2147483647          
+ten         
+------------
+0           
+-34.84      
+-1004.3     
+-1.23457e+20
+-1.23457e-20
+0           
+123456      
+-123456     
+2.14748e+09 
+-2.14748e+09
 (10 rows)
 
-QUERY: SELECT f1 AS five FROM FLOAT8_TBL
+QUERY: SELECT f1 AS five FROM FLOAT4_TBL
   WHERE f1 BETWEEN -1e6 AND 1e6
 UNION
 SELECT f1 FROM INT4_TBL
   WHERE f1 BETWEEN 0 AND 1000000;
-                 five
----------------------
-              -1004.3
-               -34.84
--1.2345678901234e-200
-                    0
-               123456
+        five
+------------
+     -1004.3
+      -34.84
+-1.23457e-20
+           0
+      123456
 (5 rows)
 
 QUERY: SELECT f1 AS five FROM VARCHAR_TBL

Wed, 16 Sep 1998 14:25:00 -0600 (MDT)
Brook Milligan <brook@trillium.NMSU.Edu>
union regression test
Brook Milligan <brook@trillium.NMSU.Edu> writes:
> I have finally resolved the differences in the union regression test.
> The problem is that the test uses the float8 table as part of the
> test, and this table turns out to be different among platforms because
> of different behavior with small numbers.  Since the float8 part of
> testing unions seemed entirely irrelevant I have changed it to use
> float4, which is not different among platforms.

It's not?  I think you assume far too much about the uniformity of
floating-point hardware.

Is it necessary to use float data at all for union tests?

			regards, tom lane

Wed, 16 Sep 1998 17:05:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] union regression test 
> > I have finally resolved the differences in the union regression 
> > test.
> > The problem is that the test uses the float8 table as part of the
> > test, and this table turns out to be different among platforms 
> > because of different behavior with small numbers.  Since the float8 
> > part of testing unions seemed entirely irrelevant I have changed it 
> > to use float4, which is not different among platforms.
> It's not?  I think you assume far too much about the uniformity of
> floating-point hardware.

I had included float8 in this particular regression test to exercise the
int4->float8 "promotion" since float8 is a "preferred type". Don't know
why float4 should in general have different or better behavior than
float8 wrt conversions and rounding; you may just be lucky on your
platform.

However, the float8_tbl is pretty ugly after the float8 regression test.
How about defining a new table which has better behaved numbers? I would
prefer that to eliminating float8 from the test altogether. I'll bet
that floating numbers like 1.1 are represented OK on Brook's machine,
and that the problems are with the 1e-200 numbers?

(I'm out of town tomorrow through Sunday so we can continue this
discussion next week?)

                       - Tom

Thu, 17 Sep 1998 01:54:06 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] union regression test
> I had included float8 in this particular regression test to exercise the
> int4->float8 "promotion" since float8 is a "preferred type". Don't know
> why float4 should in general have different or better behavior than
> float8 wrt conversions and rounding; you may just be lucky on your
> platform.

In fact, float4 normally has much _worse_ floating point portability
than float8.

float4 can't seem to get even simple number right.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

Wed, 16 Sep 1998 23:59:54 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] union regression test
   Brook Milligan <brook@trillium.NMSU.Edu> writes:
   > I have finally resolved the differences in the union regression test.
   > The problem is that the test uses the float8 table as part of the
   > test, and this table turns out to be different among platforms because
   > of different behavior with small numbers.  Since the float8 part of
   > testing unions seemed entirely irrelevant I have changed it to use
   > float4, which is not different among platforms.

Tom Lane replied:

   It's not?  I think you assume far too much about the uniformity of
   floating-point hardware.

Bruce Momjian replied:

   In fact, float4 normally has much _worse_ floating point portability
   than float8.

   float4 can't seem to get even simple number right.

I was just basing my idea on the fact that there are no
platform-specific special-cases for the float4 regression test.  I
guess no one has submitted them?  If it is really that bad, why aren't
there more special-cases so the regression tests don't fail?  Would
this not help new users?

Thomas Lockhart replied:

   I had included float8 in this particular regression test to exercise the
   int4->float8 "promotion" since float8 is a "preferred type". Don't know
   why float4 should in general have different or better behavior than
   float8 wrt conversions and rounding; you may just be lucky on your
   platform.

   However, the float8_tbl is pretty ugly after the float8 regression test.
   How about defining a new table which has better behaved numbers? I would
   prefer that to eliminating float8 from the test altogether. I'll bet
   that floating numbers like 1.1 are represented OK on Brook's machine,
   and that the problems are with the 1e-200 numbers?

Yes, the small numbers (like 1e-200) are handled as zero and enter the
float8 table, making the later union results different.

Anyway, there is nothing magical about using float4.  I chose it
arbitrarily because I was under the impression that it was better
behaved because of lack of special casing.  Clearly a wrong
assumption.

The important point here, though, is not really about float4 versus
float8; float8 is fine and now I see that it is better in fact.  The
important point is that a table that is not special-cased should be
used whenever possible (and I still recommend including something
along the lines of my earlier paragraph to prevent this in the
future).

If the float8 data type works better, we should make a float8 table
that is uniform across platforms in order to test the union code.
There is really no point in including the boundary cases of float8
into a test of union.

Perhaps a better solution is to insert into the union.sql code a
float8 table creation, fill it with well-behaved data, and then use
that table in the union test.

Cheers,
Brook

Thu, 17 Sep 1998 08:23:24 -0600 (MDT)
Brook Milligan <brook@trillium.NMSU.Edu>
Re: [HACKERS] union regression test
> If the float8 data type works better, we should make a float8 table
> that is uniform across platforms in order to test the union code.
> There is really no point in including the boundary cases of float8
> into a test of union.
> Perhaps a better solution is to insert into the union.sql code a
> float8 table creation, fill it with well-behaved data, and then use
> that table in the union test.

Yes, this is the best solution. Would you like to take a crack at it? I
can consolidate a patch and patch results on my machine (which has been
the nominal "truth" machine for the last few releases).

                   - Tom

Sun, 20 Sep 1998 23:25:26 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] union regression test
Tom,

   > If the float8 data type works better, we should make a float8 table
   > that is uniform across platforms in order to test the union code.
   > There is really no point in including the boundary cases of float8
   > into a test of union.
   > Perhaps a better solution is to insert into the union.sql code a
   > float8 table creation, fill it with well-behaved data, and then use
   > that table in the union test.

   Yes, this is the best solution. Would you like to take a crack at it? I
   can consolidate a patch and patch results on my machine (which has been
   the nominal "truth" machine for the last few releases).

I'm glad to do it.  It seems like there are two approaches, here.

1.  Modify the float8 test so that the table contains exactly the same
    values on all platforms.  In this case, this would mean removing
    the "extra" rows  that creep in on NetBSD because of the different
    handling of small (in absolute value) numbers.  Following this
    strategy generally (i.e., in other similar cases) would mean that
    all tables could be used at will by "derived" tests without
    propagating special cases beyond the direct tests where boundary
    cases are critical to include.

2.  Add a new table with a subset of float8 values that are the same
    on all platforms.  This leaves the current float8 table the same,
    but if this stategy is followed it will propagate special tables
    for derived tests.

My preference is the first of these options, because it seems like a
better long-term strategy for developing new regression tests.  If
platform-specific differences arise in whether or not values enter the
table, primary tests can be modified to remove extra ones so that the
tests always complete with a uniformly consistent set of values.

Unless there is some feeling that the second options is preferable, I
will proceed to fix the float8 table so that the values are the same
across platforms and adjust the expectations for float8 and union to
correspond.

Cheers,
Brook

Mon, 21 Sep 1998 09:07:50 -0600 (MDT)
Brook Milligan <brook@trillium.NMSU.Edu>
Re: [HACKERS] union regression test
> 1.  Modify the float8 test so that the table contains exactly the same
>     values on all platforms.  In this case, this would mean removing
>     the "extra" rows  that creep in on NetBSD because of the different
>     handling of small (in absolute value) numbers.  Following this
>     strategy generally (i.e., in other similar cases) would mean that
>     all tables could be used at will by "derived" tests without
>     propagating special cases beyond the direct tests where boundary
>     cases are critical to include.
> 
> 2.  Add a new table with a subset of float8 values that are the same
>     on all platforms.  This leaves the current float8 table the same,
>     but if this stategy is followed it will propagate special tables
>     for derived tests.
> 
> My preference is the first of these options, because it seems like a
> better long-term strategy for developing new regression tests.  If
> platform-specific differences arise in whether or not values enter the
> table, primary tests can be modified to remove extra ones so that the
> tests always complete with a uniformly consistent set of values.
> 
> Unless there is some feeling that the second options is preferable, I
> will proceed to fix the float8 table so that the values are the same
> across platforms and adjust the expectations for float8 and union to
> correspond.

OK, I agree that the first option is probably preferable. But...

This will ripple across several of the regression tests, and we will
need to carefully check the changed results in those tests to ensure
that we understand the changes. We can't just assume that they are due
to the different input values.

Also, I would strongly prefer that we keep the internals of the float8
test the same, so it continues to test edge-effect operations and
results. Then, at or near the end of the float8 test, empty the table
and re-insert some better-behaved values (in fact, it could be the same
values that the table started with probably; I haven't looked).

                      - Tom

Mon, 21 Sep 1998 15:33:40 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] union regression test
   > 1.  Modify the float8 test so that the table contains exactly the same
   >     values on all platforms.  In this case, this would mean removing
   >     the "extra" rows  that creep in on NetBSD because of the different
   >     handling of small (in absolute value) numbers.  Following this
   >     strategy generally (i.e., in other similar cases) would mean that
   >     all tables could be used at will by "derived" tests without
   >     propagating special cases beyond the direct tests where boundary
   >     cases are critical to include.

   OK, I agree that the first option is probably preferable. But...

   This will ripple across several of the regression tests, and we will
   need to carefully check the changed results in those tests to ensure
   that we understand the changes. We can't just assume that they are due
   to the different input values.

   Also, I would strongly prefer that we keep the internals of the float8
   test the same, so it continues to test edge-effect operations and
   results. Then, at or near the end of the float8 test, empty the table
   and re-insert some better-behaved values (in fact, it could be the same
   values that the table started with probably; I haven't looked).

Absolutely.  That was my intent.  The internal tests should be as
rigorous as possible.  The last thing done would be to leave the table
in a state that is absolutely identical across all platforms.  Yes,
deleting everything and reinserting the well-behaved values seems like
the easiest thing to do.

I don't think anything will ripple much, actually.  The entire point,
though, is to reduce the possible ripple of special cases (after the
correct consistency is guarranteed).  I should have no trouble
recognizing changes, since for me everything passes regression, except
the union test.

Cheers,
Brook

Mon, 21 Sep 1998 10:21:52 -0600 (MDT)
Brook Milligan <brook@trillium.NMSU.Edu>
Re: [HACKERS] union regression test
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.

      > 1.  Modify the float8 test so that the table contains exactly the same
      >     values on all platforms.  In this case, this would mean removing
      >     the "extra" rows  that creep in on NetBSD because of the different
      >     handling of small (in absolute value) numbers.  Following this
      >     strategy generally (i.e., in other similar cases) would mean that
      >     all tables could be used at will by "derived" tests without
      >     propagating special cases beyond the direct tests where boundary
      >     cases are critical to include.

   Absolutely.  That was my intent.  The internal tests should be as
   rigorous as possible.  The last thing done would be to leave the table
   in a state that is absolutely identical across all platforms.  Yes,
   deleting everything and reinserting the well-behaved values seems like
   the easiest thing to do.

Perhaps future regression testers can try to follow the points alluded
to above and outlined specifically below:

1.  Make the tests internal to each test/regress/sql/*.sql file as
    rigorous as possible so that all relevant features are exercised.

2.  Place any tests that lead to platform specific results as late as
    possible within each *.sql file so that later tests within the
    same file will not repeat failures of earlier tests but will
    instead document legitimate differences.

3.  Ensure that the contents of all tables constructed or modified by
    a single *.sql file are identical upon completion across all
    platforms.  This will ensure that derived tests will not propagate
    errors/differences of earlier tests.

These ideas are encapsulated in the patches below for the float8 tests
that resulted in platform-specific differences that then propagated to
both the numerology and union tests.  Also, even within the float8
tests were a bunch of platform-specific differences that really only
propagated a single difference; now that difference is tested at the
end so that almost all results are identical across platforms.

NOTE:  test/regress/expected/numerology-NetBSD.out should be removed.

NOTE:  I can't test anything except NetBSD.  I tried to make the
"default" float8.out correspond to what I expect other systems will
generate, but would appreciate it if someone else would check it over.

NOTE:  with these patches to the most recent snapshot every regression
test passes for NetBSD 1.3.2/i386.

Cheers,
Brook

===========================================================================
--- test/regress/sql/float8.sql.orig	Sun May 11 09:41:51 1997
+++ test/regress/sql/float8.sql	Mon Sep 21 12:14:12 1998
@@ -12,15 +12,6 @@
 
 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.*;
 
@@ -98,6 +89,32 @@
 SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f;
 
 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.*;
 
===========================================================================
--- test/regress/expected/float8.out.orig	Sun Jan  4 20:35:30 1998
+++ test/regress/expected/float8.out	Mon Sep 21 12:07:51 1998
@@ -4,14 +4,6 @@
 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                  
 ----+--------------------
@@ -222,5 +214,29 @@
     |-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');
+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)
 
===========================================================================
--- test/regress/expected/float8-NetBSD.out.orig	Sun Mar 22 12:46:31 1998
+++ test/regress/expected/float8-NetBSD.out	Mon Sep 21 11:47:24 1998
@@ -4,12 +4,6 @@
 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 @@
     |-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 @@
     |-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 @@
      |                   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 @@
      |                   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 @@
     |              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 @@
     |              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 @@
     |-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 @@
     |-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 @@
     |-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 @@
     |-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 @@
     |-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 @@
 ----+---------------------
     |0                    
     |-34.84               
-    |0                    
-    |0                    
     |-1004.3              
     |-1.2345678901234e+200
     |-1.2345678901234e-200
-(7 rows)
+(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                    
+    |-34.84               
+    |-1004.3              
+    |-1.2345678901234e+200
+    |-1.2345678901234e-200
+(5 rows)
 
===========================================================================
--- test/regress/expected/numerology-NetBSD.out.orig	Sun Mar 22 12:46:37 1998
+++ test/regress/expected/numerology-NetBSD.out	Mon Sep 21 12:49:07 1998
@@ -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;

Mon, 21 Sep 1998 14:37:33 -0600 (MDT)
Brook Milligan <brook@trillium.NMSU.Edu>
Re: [HACKERS] union regression test
Applied.

> 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.
> 
>       > 1.  Modify the float8 test so that the table contains exactly the same
>       >     values on all platforms.  In this case, this would mean removing
>       >     the "extra" rows  that creep in on NetBSD because of the different
>       >     handling of small (in absolute value) numbers.  Following this
>       >     strategy generally (i.e., in other similar cases) would mean that
>       >     all tables could be used at will by "derived" tests without
>       >     propagating special cases beyond the direct tests where boundary
>       >     cases are critical to include.
> 
>    Absolutely.  That was my intent.  The internal tests should be as
>    rigorous as possible.  The last thing done would be to leave the table
>    in a state that is absolutely identical across all platforms.  Yes,
>    deleting everything and reinserting the well-behaved values seems like
>    the easiest thing to do.
> 
> Perhaps future regression testers can try to follow the points alluded
> to above and outlined specifically below:
> 
> 1.  Make the tests internal to each test/regress/sql/*.sql file as
>     rigorous as possible so that all relevant features are exercised.
> 
> 2.  Place any tests that lead to platform specific results as late as
>     possible within each *.sql file so that later tests within the
>     same file will not repeat failures of earlier tests but will
>     instead document legitimate differences.
> 
> 3.  Ensure that the contents of all tables constructed or modified by
>     a single *.sql file are identical upon completion across all
>     platforms.  This will ensure that derived tests will not propagate
>     errors/differences of earlier tests.
> 
> These ideas are encapsulated in the patches below for the float8 tests
> that resulted in platform-specific differences that then propagated to
> both the numerology and union tests.  Also, even within the float8
> tests were a bunch of platform-specific differences that really only
> propagated a single difference; now that difference is tested at the
> end so that almost all results are identical across platforms.
> 
> NOTE:  test/regress/expected/numerology-NetBSD.out should be removed.
> 
> NOTE:  I can't test anything except NetBSD.  I tried to make the
> "default" float8.out correspond to what I expect other systems will
> generate, but would appreciate it if someone else would check it over.
> 
> NOTE:  with these patches to the most recent snapshot every regression
> test passes for NetBSD 1.3.2/i386.
> 
> Cheers,
> Brook
> 
> ===========================================================================
> --- test/regress/sql/float8.sql.orig	Sun May 11 09:41:51 1997
> +++ test/regress/sql/float8.sql	Mon Sep 21 12:14:12 1998
> @@ -12,15 +12,6 @@
>  
>  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.*;
>  
> @@ -98,6 +89,32 @@
>  SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f;
>  
>  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.*;
>  
> ===========================================================================
> --- test/regress/expected/float8.out.orig	Sun Jan  4 20:35:30 1998
> +++ test/regress/expected/float8.out	Mon Sep 21 12:07:51 1998
> @@ -4,14 +4,6 @@
>  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                  
>  ----+--------------------
> @@ -222,5 +214,29 @@
>      |-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');
> +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)
>  
> ===========================================================================
> --- test/regress/expected/float8-NetBSD.out.orig	Sun Mar 22 12:46:31 1998
> +++ test/regress/expected/float8-NetBSD.out	Mon Sep 21 11:47:24 1998
> @@ -4,12 +4,6 @@
>  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 @@
>      |-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 @@
>      |-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 @@
>       |                   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 @@
>       |                   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 @@
>      |              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 @@
>      |              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 @@
>      |-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 @@
>      |-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 @@
>      |-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 @@
>      |-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 @@
>      |-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 @@
>  ----+---------------------
>      |0                    
>      |-34.84               
> -    |0                    
> -    |0                    
>      |-1004.3              
>      |-1.2345678901234e+200
>      |-1.2345678901234e-200
> -(7 rows)
> +(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                    
> +    |-34.84               
> +    |-1004.3              
> +    |-1.2345678901234e+200
> +    |-1.2345678901234e-200
> +(5 rows)
>  
> ===========================================================================
> --- test/regress/expected/numerology-NetBSD.out.orig	Sun Mar 22 12:46:37 1998
> +++ test/regress/expected/numerology-NetBSD.out	Mon Sep 21 12:49:07 1998
> @@ -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;
> 
> 


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

Tue, 22 Sep 1998 12:50:51 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] union regression test