From: | Nicholas Howell <nhowell(at)intellect(dot)co(dot)uk> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Query producing the wrong results? |
Date: | 2004-04-27 14:34:38 |
Message-ID: | 1083076479.3585.36.camel@corba |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2004-04-27 18:57:14 | Re: [BUGS] BUG #1134: ALTER USER ... RENAME breaks md5 |
Previous Message | Aaron Hillegass | 2004-04-27 13:07:11 | Large object API problems |