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;
