mysql limit if,没有LIMIT的MySQL 5.7 RAND（）和IF（）会导致意外结果

I have the following query

SELECT t.res, IF(t.res=0, "zero", "more than zero")

FROM (

SELECT table.*, IF (RAND()<=0.2,1, IF (RAND()<=0.4,2, IF (RAND()<=0.6,3,0))) AS res

FROM table LIMIT 20) t

which returns something like this:

That's exactly what you would expect. However, as soon as I remove the LIMIT 20 I receive highly unexpected results (there are more rows returned than 20, I cut it off to make it easier to read):

SELECT t.res, IF(t.res=0, "zero", "more than zero")

FROM (

SELECT table.*, IF (RAND()<=0.2,1, IF (RAND()<=0.4,2, IF (RAND()<=0.6,3,0))) AS res

FROM table) t

Side notes:

I'm using MySQL 5.7.18-15-log and this is a highly abstracted example (real query is much more difficult).

I'm trying to understand what is happening. I do not need answers that offer work arounds without any explanations why the original version is not working. Thank you.

Update:

Instead of using LIMIT, GROUP BY id also works in the first case.

Update 2:

As requested by zerkms, I added t.res = 0 and t.res + 1 to the second example

解决方案

The problem is caused by a change introduced in MySQL 5.7 on how derived tables in (sub)queries are treated.

Basically, in order to optimize performance, some subqueries are executed at different times and / or multiple times leading to unexpected results when your subquery returns non-deterministic results (like in my case with RAND()).

There are two easy (and likewise ugly) workarounds to get MySQL to "materialize" (aka return deterministic results) these subqueries: Use LIMIT or GROUP BY id both of which force MySQL to materialize the subquery and return the expected results.

The last option is turn off derived_merge in the optimizer_switch variable: derived_merge=off (make sure to leave all the other parameters as they are).