Bug #77938 Incorrect results returned with like operator and escape characters strings

• Submitted: 4 Aug 2015 22:13
• Modified: 10 Aug 2015 11:18
• Reporter: Serge Shakhov
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.24, 5.6.26, 5.7.9, 5.5.46
• OS: CentOS
• CPU Architecture: Any
• Tags: Nullable like escape

[4 Aug 2015 22:13] Serge Shakhov

Description:
Incorrect results returned with like operator and escape characters strings.

1.
If string field contains escape characters it is excluded from resultset if 
`field` LIKE CASE...THEN `field`... is used

2.
If string field contains escape characters it is excluded from resultset if 
column is nullable and `field` LIKE `field` is used
 

How to repeat:

DROP TABLE IF EXISTS `tmp`;
CREATE TABLE IF NOT EXISTS `tmp` (
  `id` int,
  `NN` varchar(1000) NOT NULL, 
  `N` varchar(1000) NULL);

INSERT INTO `tmp` (`id`, `NN`, `N`) VALUES	
(1, 'One //1//', 'One //1//'),	(2, 'Two \\2\\', 'Two \\2\\');

Select * FROM tmp where `NN` LIKE '%'; -- All results as expected
Select * FROM tmp where `NN` LIKE `NN` ; -- All results as expected
Select * FROM tmp where `NN` LIKE CASE WHEN 1=1 THEN `NN` ELSE '%' END;  -- without string containing escape characters

Select * FROM tmp where `N` LIKE '%'; -- All results as expected
Select * FROM tmp where `N` LIKE `N` ; -- without string containing escape characters
Select * FROM tmp where `N` LIKE CASE WHEN 1=1 THEN `N` ELSE '%' END;  -- without string containing escape characters

[10 Aug 2015 11:18] MySQL Verification Team

Hello Serge,

Thank you for the report and test case.
Observed this with 5.5.46, 5.6.26, 5.7.9 builds.

Thanks,
Umesh