How come RAND() is messing up in SQL subquery?

Question

My goal is to select a random business and then with that business' id get all of their advertisements. I am getting unexpected results from my query. The number of advertisement rows returned is always what I assume is the value of "SELECT id FROM Business ORDER BY RAND() LIMIT 1". I have 3 businesses and only 1 business that has advertisement rows (5 of them) yet it always displays between 1-3 of the 5 advertisements for the same business.

SELECT * FROM Advertisement WHERE business_id=(SELECT id FROM Business ORDER BY RAND() LIMIT 1) ORDER BY priority

Business TABLE:

Advertisement TABLE:

Data for Advertisement and Business tables:

INSERT INTO `Advertisement` (`id`, `business_id`, `image_url`, `link_url`, `priority`) VALUES
(1, 1, 'http://i64.tinypic.com/2w4ehqw.png', 'https://www.dennys.com/food/burgers-sandwiches/spicy-sriracha-burger/', 1),
(2, 1, 'http://i65.tinypic.com/zuk1w1.png', 'https://www.dennys.com/food/burgers-sandwiches/prime-rib-philly-melt/', 2),
(3, 1, 'http://i64.tinypic.com/8yul3t.png', 'https://www.dennys.com/food/burgers-sandwiches/cali-club-sandwich/', 3),
(4, 1, 'http://i64.tinypic.com/o8fj9e.png', 'https://www.dennys.com/food/burgers-sandwiches/bacon-slamburger/', 4),
(5, 1, 'http://i68.tinypic.com/mwyuiv.png', 'https://www.dennys.com/food/burgers-sandwiches/the-superbird/', 5);

INSERT INTO `Business` (`id`, `name`) VALUES
(1, 'Test Dennys'),
(2, 'Test Business 2'),
(3, 'Test Business 3');

If I recall correctly, in order to order randomly you need to `SELECT RAND() AS r .... ORDER BY r`; otherwise, `ORDER BY RAND()` orders by a single result from `RAND()` similar to `ORDER BY 1` or `ORDER BY NOW()`. _Of course, that creates a problem with using that subquery._ — Uueerdo, Oct 22 '16 at 00:37
I am not sure I understand. The "SELECT id FROM Business ORDER BY RAND() LIMIT 1" seems to work properly when by itself (returns number between 1 and 3 in my case for the 3 businesses). I don't get why this number isn't properly being assigned to business_id outside of subquery. — Jason Fel, Oct 22 '16 at 00:43
Can you please post data in which you are trying the query, as I have tried its returning 5 of 5 from the advertisement. — Shushil Bohara, Oct 22 '16 at 01:18
Can you post the query you are using? I will try it and see if it works for me. — Jason Fel, Oct 22 '16 at 01:22
you may find this [related](http://stackoverflow.com/questions/14798640/creating-a-random-number-using-mysql) question useful — levelonehuman, Oct 22 '16 at 01:44
@JasonFel if you execute `SELECT id FROM Business ORDER BY RAND() LIMIT 1` repeatedly, each execution will get a separate value from `RAND()`. What happens if you try `SELECT id FROM Business ORDER BY RAND(), id` (with no limit) repeatedly? — Uueerdo, Oct 24 '16 at 16:12

score 3 · Accepted Answer · answered Oct 22 '16 at 02:15

3

You're assuming your query does something it doesn't do.

(SELECT id FROM Business ORDER BY RAND() LIMIT 1) isn't materialized at the beginning of the query. It's evaluated for each row... so for each row, we're testing whether that business_id matches the result of a newly-executed instance of the subquery. More thorough test data (more than one business included) should reveal this.

You need to materialize the result into a derived table, then join to it.

SELECT a.* 
  FROM Advertisement a
  JOIN (
        SELECT (SELECT id 
                  FROM Business 
                 ORDER BY RAND() 
                 LIMIT 1) AS business_id
        ) b ON b.business_id = a.business_id;

The ( SELECT ... ) x construct creates a temporary table that exists only for the duration of the query and uses the alias x. Such tables can be joined just like real tables.

MySQL calls this a Subquery in the FROM Clause.

answered Oct 22 '16 at 02:15

Michael - sqlbot

169,571
25
353
427

Michael, does the `RAND()` use cause this behavior? Because in my experience, normally MySQL only evaluates a subquery for every row of the outer query when the subquery is a correlated one. – Uueerdo Oct 24 '16 at 16:10
@Uueerdo, that's correct... but the normal behavior is what it is because of the fact that the optimizer tries to cache deterministic results of non-correlated subqueries, resolving them to constant values... `RAND()`, of course, isn't deterministic. The query plan in this case will show a `select_type` of UNCACHEABLE SUBQUERY`. Technically, I think it could be argued that you're always asking the server to evaluate a subquery for each row, but the optimizer is smart enough to rarely actually do it because the result of a non-correlated subquery would not typically vary. – Michael - sqlbot Oct 24 '16 at 18:40
This is brilliant. Thank you so much! – Headache Mar 19 '18 at 17:29

score 0 · Answer 2 · answered Oct 22 '16 at 01:55

0

Try following query

SELECT * FROM Advertisement WHERE business_id = (select floor(1 + rand()* (select count(*) from Business)));

answered Oct 22 '16 at 01:55

I tried and it gives me the same problem as my original query :( – Jason Fel Oct 22 '16 at 01:58

score 0 · Answer 3 · answered Oct 22 '16 at 02:25

To retrieve rows in random order use SELECT * Instead Of Id and then query for its id.

SELECT * FROM Advertisement WHERE business_id=(SELECT ID FROM (SELECT * FROM Business ORDER BY RAND() LIMIT 1) as table1)

In this case with your example data, only when rand returns 1 you get results.

How come RAND() is messing up in SQL subquery?

3 Answers3

Linked