Strange random() Correlation

From: Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Strange random() Correlation
Date: 2006-05-27 08:03:45
Message-ID: 20060527080345.GB25675@alamut
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Euler Taveira de Oliveira 2006-05-27 12:28:16 Re: BUG #2456: How to write user defined functions in Postgress sql
Previous Message Cstdenis 2006-05-27 03:26:06 Re: BUG #2458: Postgresql crash