Bug #100028 LIKE behavior is not same
Submitted: 29 Jun 2020 8:06 Modified: 29 Jun 2020 12:48
Reporter: phoenix Zhang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.18, 5.6.48, 5.7.30,8.0.20 OS:Any
Assigned to: CPU Architecture:Any

[29 Jun 2020 8:06] phoenix Zhang
Description:
When use LIKE to query result, the behavior is different.

How to repeat:
1. first create an new test-case file as like_no_data.test in mysql-test/t, the test file below:

SET NAMES latin1;

CREATE TABLE t1 (a CHAR(10) PRIMARY KEY, b CHAR(10) NOT NULL, KEY(b)) CHARSET latin1 ENGINE=InnoDB;
CREATE TABLE t2 (a CHAR(10) PRIMARY KEY, b TINYTEXT NOT NULL, KEY(b(10))) CHARSET latin1 ENGINE=InnoDB;
INSERT INTO t1 VALUES ("hello ","hello "),("hello2 ","hello2 "); 
INSERT INTO t2 VALUES ("hello ","hello "),("hello2 ","hello2 "); 
SELECT CONCAT("-",a,"-",b,"-") FROM t1 WHERE b="hello ";
SELECT CONCAT("-",a,"-",b,"-") FROM t2 WHERE b="hello ";
SELECT CONCAT("-",a,"-",b,"-") FROM t1 WHERE b LIKE "hello ";
SELECT CONCAT("-",a,"-",b,"-") FROM t2 WHERE b LIKE "hello ";
SELECT CONCAT("-",a,"-",b,"-") FROM t1 WHERE b LIKE "hello %"; 
SELECT CONCAT("-",a,"-",b,"-") FROM t2 WHERE b LIKE "hello %"; 

DROP TABLE t1, t2; 
SET NAMES default;

2. run the test-case: ./mtr main.like_no_data --nocheck-testcase
the result will be:
SET NAMES latin1;
CREATE TABLE t1 (a CHAR(10) PRIMARY KEY, b CHAR(10) NOT NULL, KEY(b)) CHARSET latin1 ENGINE=InnoDB;
CREATE TABLE t2 (a CHAR(10) PRIMARY KEY, b TINYTEXT NOT NULL, KEY(b(10))) CHARSET latin1 ENGINE=InnoDB;
INSERT INTO t1 VALUES ("hello ","hello "),("hello2 ","hello2 ");
INSERT INTO t2 VALUES ("hello ","hello "),("hello2 ","hello2 ");
SELECT CONCAT("-",a,"-",b,"-") FROM t1 WHERE b="hello ";
CONCAT("-",a,"-",b,"-")
-hello-hello-
SELECT CONCAT("-",a,"-",b,"-") FROM t2 WHERE b="hello ";
CONCAT("-",a,"-",b,"-")
-hello-hello -
SELECT CONCAT("-",a,"-",b,"-") FROM t1 WHERE b LIKE "hello ";
CONCAT("-",a,"-",b,"-")
SELECT CONCAT("-",a,"-",b,"-") FROM t2 WHERE b LIKE "hello ";
CONCAT("-",a,"-",b,"-")
-hello-hello -
SELECT CONCAT("-",a,"-",b,"-") FROM t1 WHERE b LIKE "hello %";
CONCAT("-",a,"-",b,"-")
SELECT CONCAT("-",a,"-",b,"-") FROM t2 WHERE b LIKE "hello %";
CONCAT("-",a,"-",b,"-")
-hello-hello -
DROP TABLE t1, t2;
SET NAMES default;
[100%] main.like_no_data                         [ pass ]    117

3. From the result, table t2 can return rows when use LIKE, while t1 cannot
[29 Jun 2020 8:07] phoenix Zhang
here is the real test file

Attachment: like_no_data.test (application/octet-stream, text), 756 bytes.

[29 Jun 2020 12:48] MySQL Verification Team
Hello phoenix Zhang!

Thank you for the report.

regards,
Umesh
[7 Jul 2020 11:08] Kirill Safonov
Hi friend.
If you read more closely
https://dev.mysql.com/doc/refman/5.7/en/char.html
then you will find that.
When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.