union regression test - Mailing list pgsql-hackers

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