Improper processing of random values in sub-queries
PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)
Operating Sysem: Fedora Core 1
GIVEN
CREATE TABLE data (i integer);
AND
SELECT count(*), min(i), max(i) FROM data;
count | min | max
-------+-----+-----
1340 | 3 | 20
(1 row)
(Actual data attached as data.dmp)
NOW CONSIDER the following query, executed using psql:
SELECT i, j from (
SELECT i, ((i + 18 * random())::integer % 20 + 1) AS j FROM data
) foo WHERE j = 15;
Column j should be constant (15) for all rows. In fact I get the
following output:
i | j
----+----
8 | 2
8 | 1
8 | 13
8 | 5
8 | 5
9 | 19
9 | 1
10 | 8
10 | 4
11 | 5
12 | 9
19 | 10
3 | 15
3 | 16
3 | 5
3 | 18
3 | 11
3 | 10
3 | 7
3 | 13
3 | 10
3 | 18
3 | 2
.
.
.
(74 rows)
The same problem occurs for any constraint of j in the range 1..20
Attachments:
data.dmptext/plain; charset=; name=data.dmpDownload
David Newall <davidn-postgres@rebel.net.au> writes:
NOW CONSIDER the following query, executed using psql:
SELECT i, j from (
SELECT i, ((i + 18 * random())::integer % 20 + 1) AS j FROM data
) foo WHERE j = 15;
Hmph. Evidently the planner should refuse to flatten subselects that
have volatile functions in their output list (and perhaps anywhere
else??). This will probably make some people unhappy, but I see no
other fix.
I assume though that this is a made-up example and is not the case
that's really troubling you. What is the actual problem you are looking
at?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote:
I assume though that this is a made-up example and is not the case
that's really troubling you. What is the actual problem you are looking
at?
I was generating random test data and naively assumed that ()::integer
truncated its value, therefore I was getting duplicate values. Once I
I realised that random()::integer was rounded I had no further problem,
but figured should report the bug that I had already noticed.