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: | |
| 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: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.
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