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;