Query producing the wrong results?
От | Nicholas Howell |
---|---|
Тема | Query producing the wrong results? |
Дата | |
Msg-id | 1083076479.3585.36.camel@corba обсуждение исходный текст |
Ответы |
Re: Query producing the wrong results?
|
Список | 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 по дате отправления: