Bug #92466 Case function error on randomly generated values

• Submitted: 17 Sep 2018 16:05
• Modified: 18 Sep 2018 7:49
• Reporter: Andriy Mazur
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.22, 5.7.23, 8.0.12
• OS: Any
• CPU Architecture: Any
• Tags: case, rand

[17 Sep 2018 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 2018 7:49] MySQL Verification Team

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 2018 9:14] Alexey Kopytov

I guess it's a duplicate of bug #86624.