Обсуждение: 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.6sl8 | 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) ----------------------
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: INSERTINTO 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 queryusing 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 usingan 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
> > 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) #