Improve random regression tests to fail less frequently.

2 files changed, 55 insertions, 22 deletions

diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out
index 583be02be09..302c3d61c7a 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -2,34 +2,51 @@
 -- RANDOM
 -- Test the random function
 --
--- count the number of tuples originally
+-- count the number of tuples originally, should be 1000
 SELECT count(*) FROM onek;
  count 
 -------
   1000
 (1 row)
 
--- select roughly 1/10 of the tuples
--- Assume that the "onek" table has 1000 tuples
---  and try to bracket the correct number so we
---  have a regression test which can pass/fail
--- - thomas 1998-08-17
+-- pick three random rows, they shouldn't match
+(SELECT unique1 AS random
+  FROM onek ORDER BY random() LIMIT 1)
+INTERSECT
+(SELECT unique1 AS random
+  FROM onek ORDER BY random() LIMIT 1)
+INTERSECT
+(SELECT unique1 AS random
+  FROM onek ORDER BY random() LIMIT 1);
+ random 
+--------
+(0 rows)
+
+-- count roughly 1/10 of the tuples
 SELECT count(*) AS random INTO RANDOM_TBL
   FROM onek WHERE random() < 1.0/10;
 -- select again, the count should be different
 INSERT INTO RANDOM_TBL (random)
   SELECT count(*)
   FROM onek WHERE random() < 1.0/10;
--- now test the results for randomness in the correct range
+-- select again, the count should be different
+INSERT INTO RANDOM_TBL (random)
+  SELECT count(*)
+  FROM onek WHERE random() < 1.0/10;
+-- select again, the count should be different
+INSERT INTO RANDOM_TBL (random)
+  SELECT count(*)
+  FROM onek WHERE random() < 1.0/10;
+-- now test that they are different counts
 SELECT random, count(random) FROM RANDOM_TBL
-  GROUP BY random HAVING count(random) > 1;
+  GROUP BY random HAVING count(random) > 3;
  random | count 
 --------+-------
 (0 rows)
 
-SELECT random FROM RANDOM_TBL
-  WHERE random NOT BETWEEN 80 AND 120;
- random 
---------
+SELECT AVG(random) FROM RANDOM_TBL
+  HAVING AVG(random) NOT BETWEEN 80 AND 120;
+ avg 
+-----
 (0 rows)
 
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index e2333b985f8..6ad1ff18660 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -3,14 +3,20 @@
 -- Test the random function
 --
 
--- count the number of tuples originally
+-- count the number of tuples originally, should be 1000
 SELECT count(*) FROM onek;
 
--- select roughly 1/10 of the tuples
--- Assume that the "onek" table has 1000 tuples
---  and try to bracket the correct number so we
---  have a regression test which can pass/fail
--- - thomas 1998-08-17
+-- pick three random rows, they shouldn't match
+(SELECT unique1 AS random
+  FROM onek ORDER BY random() LIMIT 1)
+INTERSECT
+(SELECT unique1 AS random
+  FROM onek ORDER BY random() LIMIT 1)
+INTERSECT
+(SELECT unique1 AS random
+  FROM onek ORDER BY random() LIMIT 1);
+
+-- count roughly 1/10 of the tuples
 SELECT count(*) AS random INTO RANDOM_TBL
   FROM onek WHERE random() < 1.0/10;
 
@@ -19,10 +25,20 @@ INSERT INTO RANDOM_TBL (random)
   SELECT count(*)
   FROM onek WHERE random() < 1.0/10;
 
--- now test the results for randomness in the correct range
+-- select again, the count should be different
+INSERT INTO RANDOM_TBL (random)
+  SELECT count(*)
+  FROM onek WHERE random() < 1.0/10;
+
+-- select again, the count should be different
+INSERT INTO RANDOM_TBL (random)
+  SELECT count(*)
+  FROM onek WHERE random() < 1.0/10;
+
+-- now test that they are different counts
 SELECT random, count(random) FROM RANDOM_TBL
-  GROUP BY random HAVING count(random) > 1;
+  GROUP BY random HAVING count(random) > 3;
 
-SELECT random FROM RANDOM_TBL
-  WHERE random NOT BETWEEN 80 AND 120;
+SELECT AVG(random) FROM RANDOM_TBL
+  HAVING AVG(random) NOT BETWEEN 80 AND 120;