MySQL Bugs: #1353: select floor(rand() * max(fortuneId)) from quotes crashes on WinXP

Bug #1353	select floor(rand() * max(fortuneId)) from quotes crashes on WinXP
Submitted:	19 Sep 2003 14:11	Modified:	19 Sep 2003 14:41
Reporter:	Steve Brown	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0-alpha-max-nt	OS:	Windows (Windows XP SP1)
Assigned to:		CPU Architecture:	Any

Description:
Running the query 

select floor(rand() * max(fortuneId)) from quotes

where quotes is a small table with three columns, fortuneId being an int column, causes MySQL to perform an illegal operation and results in the Windows XP Send/Don't Send dialog to show. MySQL is shut down.

This query works fine in MySQL 4.0

How to repeat:
Create a table in the test database thus;
mysql> desc quotes;
+-----------+-------------+-------------------+------+-----+---------+-------+
| Field     | Type        | Collation         | Null | Key | Default | Extra |
+-----------+-------------+-------------------+------+-----+---------+-------+
| Id        | varchar(64) | latin1_swedish_ci |      | PRI |         |       |
| fortuneId | int(11)     | binary            | YES  |     | NULL    |       |
| cookie    | text        | latin1_swedish_ci | YES  |     | NULL    |       |
+-----------+-------------+-------------------+------+-----+---------+-------+

Insert the following values;
mysql> select * from quotes;
+----+-----------+---------------+
| Id | fortuneId | cookie        |
+----+-----------+---------------+
| 1  |         1 | First Cookie  |
| 2  |         2 | Second Cookie |
+----+-----------+---------------+

And then run the aforementioned query. Wait for the crash box. It happens every time.

The query below also produces some utterly bizarre results;
mysql> select cookie from quotes where fortuneId = (floor(rand() * 2) + 1);
+---------------+
| cookie        |
+---------------+
| First Cookie  |
| Second Cookie |
+---------------+

Doing a select floor(rand() * 2) + 1 always produces a 1 or a 2, and as you can see from the previous submission fortuneId is unique to both rows, so how can it be pulling both out with that one query? It happens pretty regularly too :)

Thank you for the bug report. I tested it with a server built (4 days
older) from 4.1 BK tree and the crash doesn't happens, so this
issue was already fixed.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.1-alpha-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table quotes (
    -> Id varchar(64),
    -> fortuneId int,
    -> cookie text);
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> insert into quotes values ("1",1,"First Cookie");
Query OK, 1 row affected (0.04 sec)

mysql> insert into quotes values ("2",2,"Second Cookie");
Query OK, 1 row affected (0.04 sec)

mysql> select floor(rand() * max(fortuneId)) from quotes;
+--------------------------------+
| floor(rand() * max(fortuneId)) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 4.1.1-alpha-max-debug |
+-----------------------+
1 row in set (0.00 sec)

I did again the test because I missed the primary key. The crash also
not happens:

mysql> select cookie from quotes where fortuneId = (floor(rand() * 2) +
    -> 1);
+---------------+
| cookie        |
+---------------+
| Second Cookie |
+---------------+
1 row in set (0.01 sec)

Is that server version now available from the downloads section? I haven't grabbed a new version recently because I had no idea that it was updated (if it is) because the version number is still the same?

If its not updated, is there any way I can get the 'fixed' version (source doesn't seem like an option on windows :))

Cheers for looking at this so quickly!
Steve

Ah, I can get what I need with

 select * from quotes order by rand() limit 1;

Didn't realise I could do that so I am saved!! :)