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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.