Hi,
ISTM, there's a problem in the correlation of random() to outer JOINs.
Here's a test case:
BEGIN;
CREATE TEMP TABLE nuc_codes (id serial, code char(1));
COPY nuc_codes (code) FROM stdin;
A
C
D
G
H
K
M
N
R
S
T
U
V
W
X
Y
\.
SELECT id, code FROM nuc_codes;
SELECT T1.r1, T1.r2, T2.code, T3.code
FROM (SELECT ((random() * 100)::int4 % 17),
((random() * 100)::int4 % 17)
FROM generate_series(1, 10)
) AS T1 (r1, r2)
LEFT OUTER JOIN nuc_codes T2 ON (T2.id = T1.r1)
LEFT OUTER JOIN nuc_codes T3 ON (T3.id = T1.r2);
ROLLBACK;
If you run above query, you'll realize the inconsistency in the output.
Furthermore, if you'd append an "OFFSET 0" to subselect, output becomes
more stable but still has some inconsistencies.
Regards.
P.S. Query tested on 8.1.4 and a 2-3 weeks old cvs tip.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
