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 ----
======================================================================