Is anyone else seeing failure of the "rules" regression test with
current CVS sources, or is it just me?

Looking at the differences, I see that rules.sql uses getpgusername(),
which means that it is certain to create a "failure" if run under any
unusual user name.  This is bad (and the fact that the committed version
of rules.out was evidently made under the nonstandard name "pgsql"
doesn't help).  I suggest removing that usage.

The other differences seem to be ones where the same tuples are returned
but not in the same order as is obtained on the system where the
expected-output file was made.  I recall a similar complaint back in
late October 98, and I think the root cause now is the same as it was
then.  To produce the "shoelace" view, Postgres is doing a merge join,
which involves qsort()'ing the tuples of the base tables --- and for
equal-keyed items qsort() can return the items in an
implementation-dependent order.  So the regression test will succeed or
fail depending on the vagaries of the local qsort().

I suggest adding "ORDER BY sl_name", or some such, to each of the views
in the rules test that is made from a join.

BTW, it's possible that this system-dependency in the rules test was
previously masked by the optimizer bugs that Bruce has fixed recently;
that would explain why it wasn't seen before.  I know I wasn't seeing
this difference until last week.  But if the optimizer was previously
picking a join method that didn't involve a sort, the problem would
be masked.

			regards, tom lane


*** expected/rules.out	Tue Feb  9 17:44:57 1999
--- results/rules.out	Sat Feb 13 14:31:56 1999
***************
*** 919,929 ****
  sl1       |       5|black     |    80|cm      |       80
  sl2       |       6|black     |   100|cm      |      100
  sl7       |       7|brown     |    60|cm      |       60
- sl3       |       0|black     |    35|inch    |     88.9
  sl4       |       8|black     |    40|inch    |    101.6
  sl8       |       1|brown     |    40|inch    |    101.6
- sl5       |       4|brown     |     1|m       |      100
  sl6       |       0|brown     |   0.9|m       |       90
  (8 rows)
  
  QUERY: SELECT * FROM shoe_ready WHERE total_avail >= 2;
--- 919,929 ----
  sl1       |       5|black     |    80|cm      |       80
  sl2       |       6|black     |   100|cm      |      100
  sl7       |       7|brown     |    60|cm      |       60
  sl4       |       8|black     |    40|inch    |    101.6
+ sl3       |       0|black     |    35|inch    |     88.9
  sl8       |       1|brown     |    40|inch    |    101.6
  sl6       |       0|brown     |   0.9|m       |       90
+ sl5       |       4|brown     |     1|m       |      100
  (8 rows)
  
  QUERY: SELECT * FROM shoe_ready WHERE total_avail >= 2;
***************
*** 950,957 ****
  QUERY: UPDATE shoelace_data SET sl_avail = 6 WHERE  sl_name = 'sl7';
  QUERY: SELECT * FROM shoelace_log;
  sl_name   |sl_avail|log_who|log_when
! ----------+--------+-------+--------
! sl7       |       6|pgsql  |epoch   
  (1 row)
  
  QUERY:     CREATE RULE shoelace_ins AS ON INSERT TO shoelace
--- 950,957 ----
  QUERY: UPDATE shoelace_data SET sl_avail = 6 WHERE  sl_name = 'sl7';
  QUERY: SELECT * FROM shoelace_log;
  sl_name   |sl_avail|log_who |log_when
! ----------+--------+--------+--------
! sl7       |       6|postgres|epoch   
  (1 row)
  
  QUERY:     CREATE RULE shoelace_ins AS ON INSERT TO shoelace
***************
*** 997,1030 ****
  sl1       |       5|black     |    80|cm      |       80
  sl2       |       6|black     |   100|cm      |      100
  sl7       |       6|brown     |    60|cm      |       60
- sl3       |       0|black     |    35|inch    |     88.9
  sl4       |       8|black     |    40|inch    |    101.6
  sl8       |       1|brown     |    40|inch    |    101.6
! sl5       |       4|brown     |     1|m       |      100
  sl6       |       0|brown     |   0.9|m       |       90
  (8 rows)
  
  QUERY: insert into shoelace_ok select * from shoelace_arrive;
  QUERY: SELECT * FROM shoelace;
  sl_name   |sl_avail|sl_color  |sl_len|sl_unit |sl_len_cm
  ----------+--------+----------+------+--------+---------
- sl1       |       5|black     |    80|cm      |       80
  sl2       |       6|black     |   100|cm      |      100
  sl7       |       6|brown     |    60|cm      |       60
  sl4       |       8|black     |    40|inch    |    101.6
  sl3       |      10|black     |    35|inch    |     88.9
- sl8       |      21|brown     |    40|inch    |    101.6
  sl5       |       4|brown     |     1|m       |      100
  sl6       |      20|brown     |   0.9|m       |       90
  (8 rows)
  
  QUERY: SELECT * FROM shoelace_log;
  sl_name   |sl_avail|log_who|log_when
! ----------+--------+-------+--------
! sl7       |       6|pgsql  |epoch   
! sl3       |      10|pgsql  |epoch   
! sl6       |      20|pgsql  |epoch   
! sl8       |      21|pgsql  |epoch   
  (4 rows)
  
  QUERY:     CREATE VIEW shoelace_obsolete AS
--- 997,1030 ----
  sl1       |       5|black     |    80|cm      |       80
  sl2       |       6|black     |   100|cm      |      100
  sl7       |       6|brown     |    60|cm      |       60
  sl4       |       8|black     |    40|inch    |    101.6
  sl8       |       1|brown     |    40|inch    |    101.6
! sl3       |       0|black     |    35|inch    |     88.9
  sl6       |       0|brown     |   0.9|m       |       90
+ sl5       |       4|brown     |     1|m       |      100
  (8 rows)
  
  QUERY: insert into shoelace_ok select * from shoelace_arrive;
  QUERY: SELECT * FROM shoelace;
  sl_name   |sl_avail|sl_color  |sl_len|sl_unit |sl_len_cm
  ----------+--------+----------+------+--------+---------
  sl2       |       6|black     |   100|cm      |      100
+ sl1       |       5|black     |    80|cm      |       80
  sl7       |       6|brown     |    60|cm      |       60
+ sl8       |      21|brown     |    40|inch    |    101.6
  sl4       |       8|black     |    40|inch    |    101.6
  sl3       |      10|black     |    35|inch    |     88.9
  sl5       |       4|brown     |     1|m       |      100
  sl6       |      20|brown     |   0.9|m       |       90
  (8 rows)
  
  QUERY: SELECT * FROM shoelace_log;
  sl_name   |sl_avail|log_who |log_when
! ----------+--------+--------+--------
! sl7       |       6|postgres|epoch   
! sl3       |      10|postgres|epoch   
! sl6       |      20|postgres|epoch   
! sl8       |      21|postgres|epoch   
  (4 rows)
  
  QUERY:     CREATE VIEW shoelace_obsolete AS
***************
*** 1053,1065 ****
  QUERY: SELECT * FROM shoelace;
  sl_name   |sl_avail|sl_color  |sl_len|sl_unit |sl_len_cm
  ----------+--------+----------+------+--------+---------
- sl1       |       5|black     |    80|cm      |       80
  sl2       |       6|black     |   100|cm      |      100
  sl7       |       6|brown     |    60|cm      |       60
- sl4       |       8|black     |    40|inch    |    101.6
  sl3       |      10|black     |    35|inch    |     88.9
! sl8       |      21|brown     |    40|inch    |    101.6
  sl10      |    1000|magenta   |    40|inch    |    101.6
  sl5       |       4|brown     |     1|m       |      100
  sl6       |      20|brown     |   0.9|m       |       90
  (9 rows)
--- 1053,1065 ----
  QUERY: SELECT * FROM shoelace;
  sl_name   |sl_avail|sl_color  |sl_len|sl_unit |sl_len_cm
  ----------+--------+----------+------+--------+---------
  sl2       |       6|black     |   100|cm      |      100
+ sl1       |       5|black     |    80|cm      |       80
  sl7       |       6|brown     |    60|cm      |       60
  sl3       |      10|black     |    35|inch    |     88.9
! sl4       |       8|black     |    40|inch    |    101.6
  sl10      |    1000|magenta   |    40|inch    |    101.6
+ sl8       |      21|brown     |    40|inch    |    101.6
  sl5       |       4|brown     |     1|m       |      100
  sl6       |      20|brown     |   0.9|m       |       90
  (9 rows)

----------------------


Sat, 13 Feb 1999 15:03:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Failures in 'rules' regression test
Tom Lane wrote:
> 
> Is anyone else seeing failure of the "rules" regression test with
> current CVS sources, or is it just me?

"me too"

Though my output is in a slightly different order again, ie., different
system, so your qsort() theory seems good.

BTW, the error messages seem to have changed (running NetBSD-current),
so apart from rules, everything passes.

float8 .. failed
geometry .. failed
misc .. failed
rules .. failed

*** expected/float8-NetBSD.out  Sat Feb  6 19:53:55 1999
--- results/float8.out  Sun Feb 14 14:16:38 1999
***************
*** 209,217 ****
  (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;
--- 209,217 ----
  (5 rows)
  
  QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
! ERROR:  Input '10e400' is out of range for float8
  QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
! ERROR:  Input '-10e400' is out of range for float8
  QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
  QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
  QUERY: DELETE FROM FLOAT8_TBL;

*** expected/geometry-NetBSD.out        Sat Feb  6 19:53:55 1999
--- results/geometry.out        Sun Feb 14 14:16:40 1999
***************
*** 87,93 ****
  
  QUERY: SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection
     FROM LSEG_TBL l, POINT_TBL p;
! ERROR:  There is more than one possible operator '#' for types 'lseg' and 'point'
        You will have to retype this query using an explicit cast
  QUERY: SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest
     FROM LSEG_TBL l, POINT_TBL p;
--- 87,93 ----
  
  QUERY: SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection
     FROM LSEG_TBL l, POINT_TBL p;
! ERROR:  Unable to identify an operator '#' for types 'lseg' and 'point'
        You will have to retype this query using an explicit cast
  QUERY: SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest
     FROM LSEG_TBL l, POINT_TBL p;

*** expected/misc.out   Sun Feb 14 14:16:25 1999
--- results/misc.out    Sun Feb 14 14:18:42 1999
***************
*** 6,19 ****
     SET stringu1 = reverse_name(onek.stringu1)
     WHERE onek.stringu1 = 'JBAAAA' and
          onek.stringu1 = tmp.stringu1;
- NOTICE:  Non-functional update, only first update is performed
- NOTICE:  Non-functional update, only first update is performed
  QUERY: UPDATE tmp
     SET stringu1 = reverse_name(onek2.stringu1)
     WHERE onek2.stringu1 = 'JCAAAA' and
          onek2.stringu1 = tmp.stringu1;
- NOTICE:  Non-functional update, only first update is performed
- NOTICE:  Non-functional update, only first update is performed
  QUERY: DROP TABLE tmp;
  QUERY: COPY onek TO '/home/prlw1/pgsql/src/test/regress/input/../results/onek.data';
  QUERY: DELETE FROM onek;
--- 6,15 ----


Cheers,

Patrick

Sun, 14 Feb 1999 14:34:38 +0000 (GMT)
"Patrick Welche" <prlw1@newn.cam.ac.uk>
Re: [HACKERS] Failures in 'rules' regression test
> 
> Is anyone else seeing failure of the "rules" regression test with
> current CVS sources, or is it just me?

    Must have been me :-(

	I added some more tests recently - will take a look at it.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #


Mon, 15 Feb 1999 12:27:40 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Failures in 'rules' regression test