Bug #92466 Case function error on randomly generated values
Submitted: 17 Sep 16:05 Modified: 18 Sep 7:49
Reporter: Andriy Mazur Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.22, 5.7.23, 8.0.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: case, rand

[17 Sep 16:05] Andriy Mazur
Description:
Case function works incorrectly when executing over values randomly generated in a derived table.

How to repeat:
create temporary table tmp (col1 char(1));

insert into tmp 
select 'a' union select 'b' union select 'c' union select 'd' union select 'e' union select 'f' union select 'g';

select 
    case 
	when random_id between 0 and 2 then 'random_id between 0 and 2'
        when random_id between 3 and 4 then 'random_id between 3 and 4'
        when random_id between 5 and 7 then 'random_id between 5 and 7'
        when random_id between 8 and 11 then 'random_id between 8 and 11'
	end as description,
    random_id
from
(
	select 
		round(rand() * 10) as random_id
	from tmp
) tbl;

The case conditions and values generated in random_id are matched incorrectly.
Example of the result set:
# description, random_id
'random_id between 5 and 7', '2'
'random_id between 8 and 11', '8'
'random_id between 5 and 7', '6'
NULL, '0'
'random_id between 0 and 2', '10'
NULL, '1'
'random_id between 3 and 4', '5'
[18 Sep 7:49] Umesh Shastry
Hello Andriy Mazur,

Thank you for the report and test case.
Observed the reported issue in 5.7.23 and 8.0.12.

regards,
Umesh
[18 Sep 9:14] Alexey Kopytov
I guess it's a duplicate of bug #86624.