Re: [HACKERS] union regression test - Mailing list pgsql-hackers

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.        |