Query producing the wrong results? - Mailing list pgsql-bugs

From Nicholas Howell
Subject Query producing the wrong results?
Date
Msg-id 1083076479.3585.36.camel@corba
Whole thread Raw
Responses Re: Query producing the wrong results?
List pgsql-bugs
ebatcher=> select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

Just create a table with any int column and put in a bit of data:

ebatcher=> create table test (id int);
CREATE
ebatcher=> insert into test values ( 0 );
INSERT 43522 1
ebatcher=> insert into test values ( 1 );
INSERT 43523 1
ebatcher=> insert into test values ( 2 );
INSERT 43524 1
ebatcher=> insert into test values ( 3 );
INSERT 43525 1
ebatcher=> insert into test values ( 4 );
INSERT 43526 1
ebatcher=> select * from test;
 id
----
  0
  1
  2
  3
  4
(5 rows)

Ok so far so good, now when you run this query you get this result:

ebatcher=> select * from test where id = ((select min(id) from test) +
round(random() * 4));
 id
----
  0
  3
  4
(3 rows)

what I would expect is to get a single row returned not 3 rows.

ebatcher=> select * from test where id = ((select min(id) from test));
 id
----
  0
(1 row)

as expected the min is 0

ebatcher=> select round(random() * 4);
 round
-------
     1
(1 row)

ebatcher=> select round(random() * 4);
 round
-------
     3
(1 row)

as expected this always returns a random number between 0 and 4

put these together and I would expect to get a random single row not
multiple rows.  5 more runs of the query yield these results:

ebatcher=> select * from test where id = ((select min(id) from test) +
round(random() * 4));
 id
----
  0
  3
(2 rows)

ebatcher=> select * from test where id = ((select min(id) from test) +
round(random() * 4));
 id
----
  1
  3
(2 rows)

ebatcher=> select * from test where id = ((select min(id) from test) +
round(random() * 4));
 id
----
  2
  4
(2 rows)

ebatcher=> select * from test where id = ((select min(id) from test) +
round(random() * 4));
 id
----
(0 rows)

ebatcher=> select * from test where id = ((select min(id) from test) +
round(random() * 4));
 id
----
  1
(1 row)

Just tried something further and:

ebatcher=> select * from test where id = (0 + round(random() * 4));
 id
----
  0
(1 row)

ebatcher=> select * from test where id = (0 + round(random() * 4));
 id
----
  3
  4
(2 rows)

and even:

ebatcher=> select * from test where id = round(random() * 4);
 id
----
  0
  2
(2 rows)

Again I would expect to get just a single row.  Is this a bug?

pgsql-bugs by date:

Previous
From: joel-garry@home.com (Joel Garry)
Date:
Subject: Re: How configure Oracle 9i not case sensitive...
Next
From: "Jim C. Nasby"
Date:
Subject: pg_autovacuum reltuples bug