MySQL Bugs: #49344: CASE with RAND() is evaluated wrongly

Bug #49344	CASE with RAND() is evaluated wrongly
Submitted:	2 Dec 2009 12:20	Modified:	2 Dec 2009 14:55
Reporter:	Urs Enke	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.84	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any
Tags:	case, Rand()

Description:
The ELSE branch in a CASE statement with RAND() conditions appears not always to only be evaluated when no other condition fit. Even worse, such a CASE that should only return a scalar will thus return a set and turn a "=" comparison of this expression into a kind of "IN".

The given example shows that the same query that should only yield exactly one of two possible values sometimes yields both and sometimes none.

The server apparently considers the following expressions to be equivalent, which they are not because RAND() is evaluated several times:
(n=CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END)
vs.
(n=1 AND RAND()>0.5) OR (n=2 AND RAND()<=0.5)

How to repeat:
mysql> CREATE TEMPORARY TABLE t (n INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT n FROM t WHERE n=CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END;
Empty set (0.00 sec)

mysql> SELECT n FROM t WHERE n=CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END;
+------+
| n    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM t WHERE n=CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END;
+------+
| n    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM t WHERE n=CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END;
Empty set (0.00 sec)

mysql> SELECT n FROM t WHERE n=CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END;
+------+
| n    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Even more, look:

mysql> SELECT n FROM t WHERE rand();
+------+
| n    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT n FROM t WHERE rand();
+------+
| n    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM t WHERE rand();
+------+
| n    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM t WHERE rand();
Empty set (0.00 sec)

But this is NOT a bug, as rand() is evalueated for each row in your case, according to the manual, http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_rand:

"RAND() in a WHERE clause is re-evaluated every time the WHERE is executed."

and query execution plan.

Even if RAND() is evaluated for every row, it does not explain how one could get no rows from the CASE query. The answer is - the CASE expression is, indeed, re-evaluated for every WHEN condition in the CASE operator. The manual doesn't say that it should.

Either the manual or the code needs to be fixed.

I see no need to take into account the way CASE works to explain the results. Look:

mysql> SELECT n, CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END as a from t;
+------+---+
| n    | a |
+------+---+
|    1 | 1 |
|    2 | 1 |
+------+---+
2 rows in set (0.00 sec)

In this case original query would return first row.

mysql> SELECT n, CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END as a from t;
+------+---+
| n    | a |
+------+---+
|    1 | 1 |
|    2 | 2 |
+------+---+
2 rows in set (0.00 sec)

In this case original query would return both rows.

mysql> SELECT n, CASE WHEN RAND()>0.5 THEN 1 ELSE 2 END as a from t;
+------+---+
| n    | a |
+------+---+
|    1 | 2 |
|    2 | 1 |
+------+---+
2 rows in set (0.00 sec)

And, finally, in this case original query would return zero rows, as CASE result is NOT equat to n value in any row.

Of course... Thanks, and sorry for the false report!