MySQL derived query with random order

Question

Here I have two tables in MySQL 5.6...

CREATE TABLE Zones (
 ZoneID int,
 ZoneName varchar(10),
 AccountID int,
 ProxyType int
);
CREATE TABLE ProxyServers (
 ProxyID int,
 ProxyType int,
 ProxyIP varchar(10),
 ProxyPort int,
 Enabled tinyint
);

My structure is a lot more complex than the one outlined about but I am trying to keep this as simple as possible to explain.

Basically I want to return 1 row of data consisting of Zones.ID, Zones.ZoneName, and a ProxyID selected at random joined by ProxyType

SELECT * FROM Zones z 
LEFT JOIN ProxyServers ps ON ps.ProxyID = (
 SELECT ProxyID FROM ProxyServers WHERE ProxyType = z.ProxyType ORDER BY RAND() LIMIT 1
 )
ORDER BY ZoneID DESC
LIMIT 1

This is simple enough to do in T-SQL, but can't figure it out in MySQL. Plus what is rather strange, even thought the data set does not change, the results are different ever time the query is run. Sometimes no rows, sometimes 4, sometimes even duplicate rows. I don't get it :/ I think it's the ORDER BY RAND() causing this because when I remove that it's fine.

• user76664: Yeah sorry, fixed it.
• ypercubeᵀᴹ: OK. So, if you have 2 more zones with same ProxyType, do you want them joined with the same random Proxyserver? Or each zone with a (possibly) different proxyserver?
• user76664: Kinda yeah, the outer query will have a LIMIT 1 on it so it'll only ever return 1 zone and a random proxy
• ypercubeᵀᴹ: Kinda yeah to my first question or to my second?
• user76664: There will be multiple zones, some may use the same proxy type, in this case just return a random proxy based on the type for each zone. Although there will only ever be one zone row returned.

Answer 1

If you want the query to return just 1 row, a zone with a random proxy (chosen from the associated ones), then it's easy to fix the query:

SELECT z.*, ps.* 
FROM Zones AS z 
 LEFT JOIN ProxyServers AS ps 
 ON ps.ProxyType = z.ProxyType 
-- WHERE <some condition to choose from some zones or just one>
ORDER BY RAND() 
LIMIT 1 ;

or even better:

SELECT z.*, ps.* 
FROM 
 ( SELECT *
 FROM Zones
 ORDER BY ZoneID DESC
 LIMIT 1
 ) AS z 
 LEFT JOIN ProxyServers AS ps 
 ON ps.ProxyType = z.ProxyType 
ORDER BY RAND() 
LIMIT 1 ;

---

If you want multiple or all the zones, each one with a randomly chosen proxy server, it's really not trivial to solve, due to the way that MySQL executes subqueries (in ON and WHERE clauses).

In short, the subqueries (the ON condition in your case) is executed for each and every combination of z and ps. Sometimes the randomly chosen ProxyID matches the ps.ProxyID, sometimes it doesn't. Therefore, for each zone, you may get 0 matches, 1, 2 or more, which is exactly what you see in the results.
See previous answers in similar issues:

• How can this return more than 1 result?
• Problem with MySQL subquery

---

Writing a query that behaves as expected - and return 1 row for each zone - it really hard because the values of RAND() are not found previously stored in a table but provided during run time.

A work around would be to abuse two other non-standard MySQL "features". First, that subqueries allow ORDER BY without LIMIT, which is normally redundant. Second, that in GROUP BY queries, it is allowed to have expressions in the SELECT list which are not aggregates and are not in the GROUP BY list. This is also against the standard and often leads to unexpected results for poorly written queries.

Combining these two ill-behaving features, we can have a monster:

SELECT g.*
FROM
 ( SELECT z.*, 
 ps.ProxyID, ps.ProxyIP, zs.ProxyPort, ps.Enabled 
 FROM Zones AS z 
 LEFT JOIN ProxyServers AS ps 
 ON ps.ProxyType = z.ProxyType 
 ORDER BY z.ZoneID, RAND() 
 ) AS g
GROUP BY ZoneID ;

It's certainly not guaranteed to work, as recent or future versions of MySQL may or do detect the ill behaviour and may either remove the redundant ORDER BY killing the wanted functionality or may throw an error due to the non-standard GROUP BY clause.

• user76664: I had a feeling you were going to put that :) The problem here, without going into huge detail, there are a LOT of other tables joining on too which need the ORDER BY clause. I don't think I can do ORDER BY col1, col2, and RAND() can I?
• ypercubeᵀᴹ: Without more details of your problem, I can't answer.