Failures in 'rules' regression test

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)

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