Re: Query producing the wrong results?

On Tue, 27 Apr 2004, Nicholas Howell wrote:

> 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.

The expression is evaluated for each row. That can be optimized out in
the cases that the expression is believed to be a stable (or immutable)
value, but random() is not which means you get different random numbers
for each row.

You can get the effect of single evaluation by hiding the random() call
inside a stable function.