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(at)candle(dot)pha(dot)pa(dot)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. |