开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜