Thread: BUG #6607: Strange select behavior

The following bug has been logged on the website:

Bug reference:      6607
Logged by:          Sheplyakov Alexander
Email address:      suvisor.root@gmail.com
PostgreSQL version: 9.1.3
Operating system:   Debian GNU/Linux Wheezy kernel ver 3.3.1
Description:=20=20=20=20=20=20=20=20

Create test table in such manner:
create table testt (id serial PRIMARY KEY, val text not null);
Populate table with test data:
 insert into testt (val) values ('1'), ('2'), ('3'), ('4'), ('5'), ('6'),
('7'), ('8'), ('9');
(repeat next line until table doesn't contain approx 1000000 rows)
insert into testt (id, val) select nextval('testt_id_seq'::regclass),
currval('testt_id_seq'::regclass) + val::integer from testt;

And then, repeat some times such query:
select * from testt where id =3D (random()* 100000)::integer;

And sometimes it comes out something like this:
id | val
-------+--------
11894 | 15051
29233 | 42198
80725 | 90213
85688 | 100992
88017 | 108075
(5 rows)
Here can be 2, 3 or other rows amount in result... But must be only one!

<suvisor.root@gmail.com> wrote:

> select * from testt where id = (random()* 100000)::integer;
>
> And sometimes it comes out something like this:
> id | val
> -------+--------
> 11894 | 15051
> 29233 | 42198
> 80725 | 90213
> 85688 | 100992
> 88017 | 108075
> (5 rows)
> Here can be 2, 3 or other rows amount in result... But must be
> only one!

No, what you have written will scan the entire table and give each
row a 1 in 100000 chance of being selected.  Maybe something like
this would give you what you want:

select t.* from (select (random()* 100000)::integer) n(r)
  join testt t on (t.id = n.r);

By the way, you might want to tweak that random number before
casting it to int, or you might not get *any* rows back:

test=# select ('0.0000000001'::float * 100000)::int;
 int4
------
    0
(1 row)

Maybe something like:

(select floor(random() * 100000)::int + 1)

-Kevin

********* ********<suvisor.root@gmail.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> *******:
=20
>> what you have written will scan the entire table and give each
>> row a 1 in 100000 chance of being selected.
>>
> Hmm, is this because random() marked as volatile (and would be
> called for every row)? Ups, I not taken this into account...
=20
The VOLATILE marker *guarantees* that random() will be run for every
row, but even with an immutable function you would be taking your
chances on how it might run, with the way it was coded.  The
semantics are made unambiguous (I think) by making it a subquery in
the FROM clause.
=20
-Kevin

23 апреля 2012 г. 17:53 пользователь Kevin Grittner
<Kevin.Grittner@wicourts.gov> написал:
> <suvisor.root@gmail.com> wrote:
>
>> select * from testt where id = (random()* 100000)::integer;
>>
>> And sometimes it comes out something like this:
>> id | val
>> -------+--------
>> 11894 | 15051
>> 29233 | 42198
>> 80725 | 90213
>> 85688 | 100992
>> 88017 | 108075
>> (5 rows)
>> Here can be 2, 3 or other rows amount in result... But must be
>> only one!
>
> No, what you have written will scan the entire table and give each
> row a 1 in 100000 chance of being selected.  Maybe something like
> this would give you what you want:
>
Hmm, is this because random() marked as volatile (and would be called
for every row)? Ups, I not taken this into account... Sorry for bothering
and thanks for reply!