Re: v7.4 Beta 1 fails regression on Digital Unix/Tru64 4.0 - Mailing list pgsql-hackers
| From | Alessio Bragadini |
|---|---|
| Subject | Re: v7.4 Beta 1 fails regression on Digital Unix/Tru64 4.0 |
| Date | |
| Msg-id | 1060185437.18944.643.camel@iris Whole thread Raw |
| Responses |
Re: v7.4 Beta 1 fails regression on Digital Unix/Tru64 4.0
|
| List | pgsql-hackers |
The first beta fails two regression tests on alphaev67-dec-osf4.0g, compiled by cc -std -std i.e. Compaq/HP Digital Unix/Tru64/name-of-the-day They are "join" (FAILED) and "random" (failed ignored). Attached is the regression diff. During configuration a warning stated that our version of Bison was outdated, can it be related? -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-22755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 *** ./expected/join.out Sat Jul 19 23:20:52 2003 --- ./results/join.out Wed Aug 6 16:45:25 2003 *************** *** 1,43 **** - -- - -- JOIN - -- Test JOIN clauses - -- - CREATE TABLE J1_TBL ( - i integer, - j integer, - t text - ); - CREATE TABLE J2_TBL ( - i integer, - k integer - ); - INSERT INTO J1_TBL VALUES (1, 4, 'one'); - INSERT INTO J1_TBL VALUES (2, 3, 'two'); - INSERT INTO J1_TBL VALUES (3, 2, 'three'); - INSERT INTO J1_TBL VALUES (4, 1, 'four'); - INSERT INTO J1_TBL VALUES (5, 0, 'five'); - INSERT INTO J1_TBL VALUES (6, 6, 'six'); - INSERT INTO J1_TBL VALUES (7, 7, 'seven'); - INSERT INTO J1_TBL VALUES (8, 8, 'eight'); - INSERT INTO J1_TBL VALUES (0, NULL, 'zero'); - INSERT INTO J1_TBL VALUES (NULL, NULL, 'null'); - INSERT INTO J1_TBL VALUES (NULL, 0, 'zero'); - INSERT INTO J2_TBL VALUES (1, -1); - INSERT INTO J2_TBL VALUES (2, 2); - INSERT INTO J2_TBL VALUES (3, -3); - INSERT INTO J2_TBL VALUES (2, 4); - INSERT INTO J2_TBL VALUES (5, -5); - INSERT INTO J2_TBL VALUES (5, -5); - INSERT INTO J2_TBL VALUES (0, NULL); - INSERT INTO J2_TBL VALUES (NULL, NULL); - INSERT INTO J2_TBL VALUES (NULL, 0); - -- - -- CORRELATION NAMES - -- Make sure that table/column aliases are supported - -- before diving into more complex join syntax. - -- - SELECT '' AS "xxx", * - FROM J1_TBL AS tx; xxx | i | j | t -----+---+---+------- | 1 | 4 | one --- 1,3 ---- *************** *** 53,60 **** | | 0 | zero (11 rows) - SELECT '' AS "xxx", * - FROM J1_TBL tx; xxx | i | j | t -----+---+---+------- | 1 | 4 | one --- 13,18 ---- *************** *** 70,77 **** | | 0 | zero (11 rows) - SELECT '' AS "xxx", * - FROM J1_TBL AS t1 (a, b, c); xxx | a | b | c -----+---+---+------- | 1 | 4 | one --- 28,33 ---- *************** *** 87,94 **** | | 0 | zero (11 rows) - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b, c); xxx | a | b | c -----+---+---+------- | 1 | 4 | one --- 43,48 ---- *************** *** 104,111 **** | | 0 | zero (11 rows) - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e); xxx | a | b | c | d | e -----+---+---+-------+---+---- | 1 | 4 | one | 1 | -1 --- 58,63 ---- *************** *** 209,217 **** | | 0 | zero | | 0 (99 rows) - SELECT '' AS "xxx", t1.a, t2.e - FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) - WHERE t1.a = t2.d; xxx | a | e -----+---+---- | 0 | --- 161,166 ---- *************** *** 223,235 **** | 5 | -5 (7 rows) - -- - -- CROSS JOIN - -- Qualifications are not allowed on cross joins, - -- which degenerate into a standard unqualified inner join. - -- - SELECT '' AS "xxx", * - FROM J1_TBL CROSS JOIN J2_TBL; xxx | i | j | t | i | k -----+---+---+-------+---+---- | 1 | 4 | one | 1 | -1 --- 172,177 ---- *************** *** 333,345 **** | | 0 | zero | | 0 (99 rows) - -- ambiguous column - SELECT '' AS "xxx", i, k, t - FROM J1_TBL CROSS JOIN J2_TBL; ERROR: column reference "i" is ambiguous - -- resolve previous ambiguity by specifying the table name - SELECT '' AS "xxx", t1.i, k, t - FROM J1_TBL t1 CROSS JOIN J2_TBL t2; xxx | i | k | t -----+---+----+------- | 1 | -1 | one --- 275,281 ---- *************** *** 443,451 **** | | 0 | zero (99 rows) - SELECT '' AS "xxx", ii, tt, kk - FROM (J1_TBL CROSS JOIN J2_TBL) - AS tx (ii, jj, tt, ii2, kk); xxx | ii | tt | kk -----+----+-------+---- | 1 | one | -1 --- 379,384 ---- *************** *** 549,557 **** | | zero | 0 (99 rows) - SELECT '' AS "xxx", tx.ii, tx.jj, tx.kk - FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e)) - AS tx (ii, jj, tt, ii2, kk); xxx | ii | jj | kk -----+----+----+---- | 1 | 4 | -1 --- 482,487 ---- *************** *** 655,662 **** | | 0 | 0 (99 rows) - SELECT '' AS "xxx", * - FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; xxx | i | j | t | i | k | i | k -----+---+---+-------+---+----+---+---- | 1 | 4 | one | 1 | -1 | 1 | -1 --- 585,590 ---- *************** *** 1552,1570 **** | | 0 | zero | | 0 | | 0 (891 rows) - -- - -- - -- Inner joins (equi-joins) - -- - -- - -- - -- Inner joins (equi-joins) with USING clause - -- The USING syntax changes the shape of the resulting table - -- by including a column in the USING clause only once in the result. - -- - -- Inner equi-join on specified column - SELECT '' AS "xxx", * - FROM J1_TBL INNER JOIN J2_TBL USING (i); xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1480,1485 ---- *************** *** 1576,1584 **** | 5 | 0 | five | -5 (7 rows) - -- Same as above, slightly different syntax - SELECT '' AS "xxx", * - FROM J1_TBL JOIN J2_TBL USING (i); xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1491,1496 ---- *************** *** 1590,1598 **** | 5 | 0 | five | -5 (7 rows) - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) - ORDER BY a, d; xxx | a | b | c | d -----+---+---+-------+---- | 0 | | zero | --- 1502,1507 ---- *************** *** 1604,1612 **** | 5 | 0 | five | -5 (7 rows) - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) - ORDER BY b, t1.a; xxx | b | a | c | a -----+---+---+-------+--- | 0 | 5 | five | --- 1513,1518 ---- *************** *** 1615,1626 **** | 4 | 1 | one | 2 (4 rows) - -- - -- NATURAL JOIN - -- Inner equi-join on all columns with the same name - -- - SELECT '' AS "xxx", * - FROM J1_TBL NATURAL JOIN J2_TBL; xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1521,1526 ---- *************** *** 1632,1639 **** | 5 | 0 | five | -5 (7 rows) - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); xxx | a | b | c | d -----+---+---+-------+---- | 0 | | zero | --- 1532,1537 ---- *************** *** 1645,1652 **** | 5 | 0 | five | -5 (7 rows) - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); xxx | a | b | c | d -----+---+---+------+--- | 0 | | zero | --- 1543,1548 ---- *************** *** 1654,1663 **** | 4 | 1 | four | 2 (3 rows) - -- mismatch number of columns - -- currently, Postgres will fill in with underlying names - SELECT '' AS "xxx", * - FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a); xxx | a | b | t | k -----+---+---+-------+---- | 0 | | zero | --- 1550,1555 ---- *************** *** 1669,1679 **** | 5 | 0 | five | -5 (7 rows) - -- - -- Inner joins (equi-joins) - -- - SELECT '' AS "xxx", * - FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i); xxx | i | j | t | i | k -----+---+---+-------+---+---- | 0 | | zero | 0 | --- 1561,1566 ---- *************** *** 1685,1692 **** | 5 | 0 | five | 5 | -5 (7 rows) - SELECT '' AS "xxx", * - FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k); xxx | i | j | t | i | k -----+---+---+------+---+--- | 0 | | zero | | 0 --- 1572,1577 ---- *************** *** 1694,1704 **** | 4 | 1 | four | 2 | 4 (3 rows) - -- - -- Non-equi-joins - -- - SELECT '' AS "xxx", * - FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k); xxx | i | j | t | i | k -----+---+---+-------+---+--- | 1 | 4 | one | 2 | 2 --- 1579,1584 ---- *************** *** 1712,1724 **** | 0 | | zero | | 0 (9 rows) - -- - -- Outer joins - -- Note that OUTER is a noise word - -- - SELECT '' AS "xxx", * - FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) - ORDER BY i; xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1592,1597 ---- *************** *** 1736,1744 **** | | 0 | zero | (13 rows) - SELECT '' AS "xxx", * - FROM J1_TBL LEFT JOIN J2_TBL USING (i) - ORDER BY i; xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1609,1614 ---- *************** *** 1756,1763 **** | | 0 | zero | (13 rows) - SELECT '' AS "xxx", * - FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1626,1631 ---- *************** *** 1771,1778 **** | | | | 0 (9 rows) - SELECT '' AS "xxx", * - FROM J1_TBL RIGHT JOIN J2_TBL USING (i); xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1639,1644 ---- *************** *** 1786,1794 **** | | | | 0 (9 rows) - SELECT '' AS "xxx", * - FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) - ORDER BY i, t; xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1652,1657 ---- *************** *** 1808,1816 **** | | | | 0 (15 rows) - SELECT '' AS "xxx", * - FROM J1_TBL FULL JOIN J2_TBL USING (i) - ORDER BY i, t; xxx | i | j | t | k -----+---+---+-------+---- | 0 | | zero | --- 1671,1676 ---- *************** *** 1830,1869 **** | | | | 0 (15 rows) - SELECT '' AS "xxx", * - FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); xxx | i | j | t | k -----+---+---+---+--- (0 rows) - SELECT '' AS "xxx", * - FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); xxx | i | j | t | k -----+---+---+-----+---- | 1 | 4 | one | -1 (1 row) - -- - -- More complicated constructs - -- - -- UNION JOIN isn't implemented yet - SELECT '' AS "xxx", * - FROM J1_TBL UNION JOIN J2_TBL; ERROR: UNION JOIN is not implemented yet - -- - -- Multiway full join - -- - CREATE TABLE t1 (name TEXT, n INTEGER); - CREATE TABLE t2 (name TEXT, n INTEGER); - CREATE TABLE t3 (name TEXT, n INTEGER); - INSERT INTO t1 VALUES ( 'aa', 11 ); - INSERT INTO t2 VALUES ( 'aa', 12 ); - INSERT INTO t2 VALUES ( 'bb', 22 ); - INSERT INTO t2 VALUES ( 'dd', 42 ); - INSERT INTO t3 VALUES ( 'aa', 13 ); - INSERT INTO t3 VALUES ( 'bb', 23 ); - INSERT INTO t3 VALUES ( 'cc', 33 ); - SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); name | n | n | n ------+----+----+---- aa | 11 | 12 | 13 --- 1690,1705 ---- *************** *** 1872,1897 **** dd | | 42 | (4 rows) - -- - -- Test interactions of join syntax and subqueries - -- - -- Basic cases (we expect planner to pull up the subquery here) - SELECT * FROM - (SELECT * FROM t2) as s2 - INNER JOIN - (SELECT * FROM t3) s3 - USING (name); name | n | n ------+----+---- aa | 12 | 13 bb | 22 | 23 (2 rows) - SELECT * FROM - (SELECT * FROM t2) as s2 - LEFT JOIN - (SELECT * FROM t3) s3 - USING (name); name | n | n ------+----+---- aa | 12 | 13 --- 1708,1719 ---- *************** *** 1899,1909 **** dd | 42 | (3 rows) - SELECT * FROM - (SELECT * FROM t2) as s2 - FULL JOIN - (SELECT * FROM t3) s3 - USING (name); name | n | n ------+----+---- aa | 12 | 13 --- 1721,1726 ---- *************** *** 1912,1933 **** dd | 42 | (4 rows) - -- Cases with non-nullable expressions in subquery results; - -- make sure these go to null as expected - SELECT * FROM - (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 - NATURAL INNER JOIN - (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; name | s2_n | s2_2 | s3_n | s3_2 ------+------+------+------+------ aa | 12 | 2 | 13 | 3 bb | 22 | 2 | 23 | 3 (2 rows) - SELECT * FROM - (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 - NATURAL LEFT JOIN - (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; name | s2_n | s2_2 | s3_n | s3_2 ------+------+------+------+------ aa | 12 | 2 | 13 | 3 --- 1729,1740 ---- *************** *** 1935,1944 **** dd | 42 | 2 | | (3 rows) - SELECT * FROM - (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 - NATURAL FULL JOIN - (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; name | s2_n | s2_2 | s3_n | s3_2 ------+------+------+------+------ aa | 12 | 2 | 13 | 3 --- 1742,1747 ---- *************** *** 1947,1969 **** dd | 42 | 2 | | (4 rows) - SELECT * FROM - (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 - NATURAL INNER JOIN - (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 - NATURAL INNER JOIN - (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 ------+------+------+------+------+------+------ aa | 11 | 1 | 12 | 2 | 13 | 3 (1 row) - SELECT * FROM - (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 - NATURAL FULL JOIN - (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 - NATURAL FULL JOIN - (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 ------+------+------+------+------+------+------ aa | 11 | 1 | 12 | 2 | 13 | 3 --- 1750,1760 ---- *************** *** 1972,1985 **** dd | | | 42 | 2 | | (4 rows) - SELECT * FROM - (SELECT name, n as s1_n FROM t1) as s1 - NATURAL FULL JOIN - (SELECT * FROM - (SELECT name, n as s2_n FROM t2) as s2 - NATURAL FULL JOIN - (SELECT name, n as s3_n FROM t3) as s3 - ) ss2; name | s1_n | s2_n | s3_n ------+------+------+------ aa | 11 | 12 | 13 --- 1763,1768 ---- *************** *** 1988,2001 **** dd | | 42 | (4 rows) - SELECT * FROM - (SELECT name, n as s1_n FROM t1) as s1 - NATURAL FULL JOIN - (SELECT * FROM - (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 - NATURAL FULL JOIN - (SELECT name, n as s3_n FROM t3) as s3 - ) ss2; name | s1_n | s2_n | s2_2 | s3_n ------+------+------+------+------ aa | 11 | 12 | 2 | 13 --- 1771,1776 ---- *************** *** 2004,2022 **** dd | | 42 | 2 | (4 rows) - -- Test for propagation of nullability constraints into sub-joins - create temp table x (x1 int, x2 int); - insert into x values (1,11); - insert into x values (2,22); - insert into x values (3,null); - insert into x values (4,44); - insert into x values (5,null); - create temp table y (y1 int, y2 int); - insert into y values (1,111); - insert into y values (2,222); - insert into y values (3,333); - insert into y values (4,null); - select * from x; x1 | x2 ----+---- 1 | 11 --- 1779,1784 ---- *************** *** 2026,2032 **** 5 | (5 rows) - select * from y; y1 | y2 ----+----- 1 | 111 --- 1788,1793 ---- *************** *** 2035,2041 **** 4 | (4 rows) - select * from x left join y on (x1 = y1 and x2 is not null); x1 | x2 | y1 | y2 ----+----+----+----- 1 | 11 | 1 | 111 --- 1796,1801 ---- *************** *** 2045,2051 **** 5 | | | (5 rows) - select * from x left join y on (x1 = y1 and y2 is not null); x1 | x2 | y1 | y2 ----+----+----+----- 1 | 11 | 1 | 111 --- 1805,1810 ---- *************** *** 2055,2062 **** 5 | | | (5 rows) - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1814,1819 ---- *************** *** 2066,2073 **** 5 | | | | 5 | (5 rows) - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1 and x2 is not null); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1823,1828 ---- *************** *** 2077,2084 **** 5 | | | | | (5 rows) - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1 and y2 is not null); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1832,1837 ---- *************** *** 2088,2095 **** 5 | | | | | (5 rows) - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1 and xx2 is not null); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1841,1846 ---- *************** *** 2099,2107 **** 5 | | | | | (5 rows) - -- these should NOT give the same answers as above - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1) where (x2 is not null); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1850,1855 ---- *************** *** 2109,2116 **** 4 | 44 | 4 | | 4 | 44 (3 rows) - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1) where (y2 is not null); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1857,1862 ---- *************** *** 2118,2125 **** 3 | | 3 | 333 | 3 | (3 rows) - select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) - on (x1 = xx1) where (xx2 is not null); x1 | x2 | y1 | y2 | xx1 | xx2 ----+----+----+-----+-----+----- 1 | 11 | 1 | 111 | 1 | 11 --- 1864,1869 ---- *************** *** 2127,2137 **** 4 | 44 | 4 | | 4 | 44 (3 rows) - -- - -- Clean up - -- - DROP TABLE t1; - DROP TABLE t2; - DROP TABLE t3; - DROP TABLE J1_TBL; - DROP TABLE J2_TBL; --- 1871,1873 ---- ====================================================================== *** ./expected/random.out Thu Feb 13 07:24:04 2003 --- ./results/random.out Wed Aug 6 16:45:22 2003 *************** *** 1,34 **** - -- - -- RANDOM - -- Test the random function - -- - -- count the number of tuples originally - 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 - 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 random, count(random) FROM RANDOM_TBL - GROUP BY random HAVING count(random) > 1; random | count --------+------- (0 rows) - SELECT random FROM RANDOM_TBL - WHERE random NOT BETWEEN 80 AND 120; random -------- (0 rows) --- 1,12 ---- ======================================================================
pgsql-hackers by date: