Mixing RAND() and UNION gives random rowcount?
I was testing out a query to get a random integer in MySQL, and I noticed this behavior:
mysql> SELECT FLOOR(0 + (RAND() * 5)) UNION SELECT FLOOR(0 + (RAND() * 5)) UNION SELECT FLOOR(0 + (RAND() * 5));
+-------------------------+
| FLOOR(0 + (RAND() * 5)) |
+-------------------------+
| 1 |
| 2 |
| 4 |
+-------------------------+
3 rows in set (0.00 sec)
mysql> SELECT FLOOR(0 + (RAND() * 5)) UNION SELECT FLOOR(0 + (RAND() * 5)) UNION SELECT FLOOR(0 + (RAND() * 5));
+-------------------------+
| FLOOR(0 + (RAND() * 5)) |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT FLOOR(0 + (RAND() * 5)) UNION SELECT FLOOR(0 + (RAND() * 5)) UNION SELECT FLOOR(0 + (RAND() * 5));
+-------------------------+
| FLOOR(0 + (RAND() * 5)) |
+-------------------------+
| 1 |
| 4 |
| 0 |
+-------------------------+
3 rows in set (0.00 sec)
I seem to be getting a random number of rows in the result set! There are 3 UNION
ed SELECT
s in there. What is going on? I was thinking that maybe one of the results from the rand statement was no tuple because of the RAND()
, but that doesn't make sense -- there should be some result, a 开发者_如何学CNULL
at least!
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.0.51a-24+lenny4 |
+-------------------+
1 row in set (0.00 sec)
UNION
eliminates duplicate results because it's a set-wise operator. If you want duplicates retained, use UNION ALL
. Maybe I've misunderstood your question, but this seems like exactly the type of results I'd expect for this query.
精彩评论