On Sunday 09 December 2001 12:26, Paul DuBois wrote:
> At 3:19 PM -0800 12/8/01, Rob@TH wrote:
> >Hmm still nothing :/
> >Any other possibilities?
>
> The suggestion offered below is a workaround that seems logical given
> the lack of ORDER BY RAND() prior to MySQL 3.23. Unfortunately, it
> doesn't work due to the behavior of the optimizer in pre-3.23 versions.
> Specifically, MySQL notices that "rand() as rnd" is a function, deduces
> (incorrectly) that the contents of the column will be constant, and thus
> optimizes away the "order by rnd" clause entirely. Result: no sorting.
>
> To deal with this, use an expression that includes RAND(), but written
> in such a way that the optimizer won't think is constant. For example,
> if you have an id column, do something like this:
>
> SELECT id*0+RAND() AS rnd, ... FROM tbl_name ORDER BY rnd LIMIT 1;
Seems to work for me using 3.23.42 -- is it a bug ;-)
mysql> select * from category;
+-------------+----------------------+
| category_id | category_description |
+-------------+----------------------+
| 0000000001 | Feng Shui |
| 0000000002 | Figure |
| 0000000003 | Magnet |
| 0000000004 | Candle |
+-------------+----------------------+
mysql> select category_description, rand() as rnd from category order by rnd;
+----------------------+-------------------+
| category_description | rnd |
+----------------------+-------------------+
| Candle | 0.467619647707436 |
| Figure | 0.581306149793143 |
| Magnet | 0.916031742390275 |
| Feng Shui | 0.996833012436361 |
+----------------------+-------------------+
4 rows in set (0.00 sec)
mysql> select category_description, rand() as rnd from category order by rnd;
+----------------------+-------------------+
| category_description | rnd |
+----------------------+-------------------+
| Feng Shui | 0.433106270090776 |
| Figure | 0.656810501270751 |
| Candle | 0.836240293305591 |
| Magnet | 0.984733726815073 |
+----------------------+-------------------+
4 rows in set (0.00 sec)
mysql> select category_description, rand() as rnd from category order by rnd;
+----------------------+-------------------+
| category_description | rnd |
+----------------------+-------------------+
| Figure | 0.200211824104387 |
| Magnet | 0.565105185438977 |
| Feng Shui | 0.811984655272201 |
| Candle | 0.953237160996755 |
+----------------------+-------------------+
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
/*
Boling's postulate:
If you're feeling good, don't worry. You'll get over it.
*/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
