Re: [BUGS] select where id=random()*something returns two results

#1

On Fri, 19 Sep 2003, Ulrich Meis wrote:

Quote:

select * from quotes where id=1+round(random()* cast ((select max(id)
from quotes) as double precision));
id | quote |
author
-----+-----------------------------------------------------------+------
-----------
187 | Vergib Deinen Feinden, aber vergiss niemals ihre Namen. | John
F. Kennedy
377 | Die Wirklichkeit ist nicht so oder so, sondern so und so. | Harry
Mulisch
(2 rows)

I'm not really into databases, but this sounds wrong. Most of the time,
I actually get 0 results.

The problem is that random() is evaluated on each line giving a different
result.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#2

--=-x+TdHj5Oq+BhU0Q5XYem
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Quote:

select * from quotes where id=3D1+round(random()* cast ((select max(id)
from quotes) as double precision));
id | quote |
author=20=20=20=20=20=20
-----+-----------------------------------------------------------+------
-----------
187 | Vergib Deinen Feinden, aber vergiss niemals ihre Namen. | John
F. Kennedy
377 | Die Wirklichkeit ist nicht so oder so, sondern so und so. | Harry
Mulisch
(2 rows)
=20
I'm not really into databases, but this sounds wrong. Most of the time,
I actually get 0 results.

Random is calculated per call (in this case per comparison). So, the
value you compare against for 187 is not the same as 377.

UPDATE table SET column =3D random(); will show the effect.

If you wrap randon() in a subselect, it will cause it to be evaluated
once:

SELECT * from quotes where id =3D 1+round((SELECT random()) * cast(....).

However, a much faster query for your purposes would be:

SELECT * FROM quotes ORDER BY random() LIMIT 1;

--=-x+TdHj5Oq+BhU0Q5XYem
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/alr46DETLow6vwwRAlZCAJ0cUDW0VazSmrltUL9ZWKwpCHFsQg CfezTm
3J1RCgi/NcGJTQAoXU+r2+0=
=n57g
-----END PGP SIGNATURE-----

--=-x+TdHj5Oq+BhU0Q5XYem--

#3

Sorry for buggering you, I get the point

And thanks for the order by limit 1 hint. That will do.

Ulrich Meis

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#4

Quote:

Sorry for buggering you, I get the point

I think you mean 'bugging.' Buggering is something quite different.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#5

Quote:

-----Original Message-----
From: pgsql-bugs-owner (AT) postgresql (DOT) org [mailto

gsql-bugs-
owner (AT) postgresql (DOT) org] On Behalf Of Ian Grant
Sent: Friday, September 19, 2003 1:02 PM
To: Ulrich Meis
Cc: Ian.Grant (AT) cl (DOT) cam.ac.uk; pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [BUGS] select where id=random()*something returns two
results

Sorry for buggering you, I get the point

I think you mean 'bugging.' Buggering is something quite different.

Sorry again, surely I meant bugging :-)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#6

Quote:

-----Original Message-----
From: pgsql-bugs-owner (AT) postgresql (DOT) org [mailto

gsql-bugs-
owner (AT) postgresql (DOT) org] On Behalf Of Jean-Luc Lachance
Sent: Friday, September 19, 2003 4:44 PM
To: Rod Taylor
Cc: Ulrich Meis; pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [BUGS] select where id=random()*something returns two
results

Rod,

If the table has 100,000 tupples your query is generating 100,000 new
tupples...
Try:

select * from quotes where id = (
select int8( 1 + random() * (
select id from quotes order by id desc limit 1)));

How about

select * from quotes where id=1+int8((select random())*(select max(id)
from quotes));

It works, but is it more or less efficient?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#7

--=-9QwNsm6Q/xpgHRHtHbq4
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Sun, 2003-09-21 at 08:21, Ulrich Meis wrote:

Quote:

-----Original Message-----
From: pgsql-bugs-owner (AT) postgresql (DOT) org [mailto

gsql-bugs-
owner (AT) postgresql (DOT) org] On Behalf Of Jean-Luc Lachance
Sent: Friday, September 19, 2003 4:44 PM
To: Rod Taylor
Cc: Ulrich Meis; pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [BUGS] select where id=3Drandom()*something returns two
results
=20
Rod,
=20
If the table has 100,000 tupples your query is generating 100,000 new
tupples...
Try:
=20
select * from quotes where id =3D (
select int8( 1 + random() * (
select id from quotes order by id desc limit 1)));
=20
=20
How about
=20
select * from quotes where id=3D1+int8((select random())*(select max(id)
from quotes));
=20
It works, but is it more or less efficient?

Run EXPLAIN ANALYZE on them both and you tell me which is more
efficient.

Efficiency of a query tends to change with the data that it is being
executed on.

--=-9QwNsm6Q/xpgHRHtHbq4
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/bvFN6DETLow6vwwRAutCAJ4hvJt4ZhsRn62RQO8My8HNFTDiCA CfXTTd
Acqy7178xVjqpUtBlSfVO1U=
=PP4f
-----END PGP SIGNATURE-----

--=-9QwNsm6Q/xpgHRHtHbq4--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] select where id=random()*something returns two results

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

**Re: [BUGS] select where id=random()*something returns two results -** 09-18-2003 , 08:17 PM

**Re: [BUGS] select where id=random()*something returns two results -** 09-18-2003 , 08:26 PM

**Re: [BUGS] select where id=random()*something returns two results -** 09-19-2003 , 05:41 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-19-2003 , 06:05 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-21-2003 , 07:21 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-21-2003 , 07:25 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-22-2003 , 07:58 AM

Re: [BUGS] select where id=random()*something returns two results

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] select where id=random()*something returns two results - 09-18-2003 , 08:17 PM

Re: [BUGS] select where id=random()*something returns two results - 09-18-2003 , 08:26 PM

Re: [BUGS] select where id=random()*something returns two results - 09-19-2003 , 05:41 AM

Re: [BUGS] select where id=random()*something returns two results - 09-19-2003 , 06:05 AM

Re: [BUGS] select where id=random()*something returns two results - 09-21-2003 , 07:21 AM

Re: [BUGS] select where id=random()*something returns two results - 09-21-2003 , 07:25 AM

Re: [BUGS] select where id=random()*something returns two results - 09-22-2003 , 07:58 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-18-2003 , 08:17 PM

**Re: [BUGS] select where id=random()*something returns two results -** 09-18-2003 , 08:26 PM

**Re: [BUGS] select where id=random()*something returns two results -** 09-19-2003 , 05:41 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-19-2003 , 06:05 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-21-2003 , 07:21 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-21-2003 , 07:25 AM

**Re: [BUGS] select where id=random()*something returns two results -** 09-22-2003 , 07:58 AM