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