Обсуждение: 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

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.