Thread: 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. 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
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
> > 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
> 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. |
> > 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
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
> 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
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
> 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
> 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
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;
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. |