Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group