Improper processing of random values in sub-queries - Mailing list pgsql-bugs

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