Bug #93215 Case statement use in conditional with sub_part index regression in 8.0
Submitted: 15 Nov 2018 19:38 Modified: 4 Apr 2019 13:13
Reporter: Jon Hovland Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: case statement, INDEX, sub_part

[15 Nov 2018 19:38] Jon Hovland
Description:
In 8.0 a query that uses a case statement in a side of a conditional clause will not match the other side of the conditional if the other side is a column with a sub_part index that has a value greater in length than the sub_part value. This worked <8.0.

How to repeat:
--Have 2 tables (or alias the single table with a sub_part index)

CREATE TABLE `test_table_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `example` varchar(2000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `example_key` (`example`(255))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `test_table_two` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `example` varchar(2000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- >255 characters
insert into test_table_one (example) values (
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456');

insert into test_table_two (example) values (
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456');

-- execute the following - empty set on 8, works <8 
select tt2.id from test_table_two tt2, test_table_one tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- or the following - empty set on 8, works <8
select tt2.id from test_table_one tt2, test_table_one tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- because this table doesn't have a sub_part index on example this works
select tt2.id from test_table_two tt2, test_table_two tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- <200 characters
insert into test_table_one (example) values (
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789');

insert into test_table_two (example) values (
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789');

-- execute the following - returns both rows
select tt2.id from test_table_two tt2, test_table_one tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- or the following - returns both rows
select tt2.id from test_table_one tt2, test_table_one tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- because this table doesn't have a sub_part index on example this works
select tt2.id from test_table_two tt2, test_table_two tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- >255 characters (different value to avoid cartesian product)
insert into test_table_one (example) values (
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890012345');

insert into test_table_two (example) values (
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890012345');

-- execute the following - only returns the row with length < sub_part
select tt2.id from test_table_two tt2, test_table_one tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- execute the following - only returns the row with length < sub_part
select tt2.id from test_table_one tt2, test_table_one tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;

-- because this table doesn't have a sub_part index on example this works and returns all 3 rows
select tt2.id from test_table_two tt2, test_table_two tt1 where (case when tt2.example like 'sometext%' then '~' + tt2.example else tt2.example end) = tt1.example;
[15 Nov 2018 20:11] Jon Hovland
updating OS and architecture as they can be left "Any"
[16 Nov 2018 7:03] MySQL Verification Team
Thank you for the bug report and test case.
[4 Apr 2019 13:13] Paul DuBois
Posted by developer:
 
Fixed in 8.0.17.

CASE statement comparisons that relied on index prefix values could
produce incorrect results.