P0-[4.0 bug hunting]-[SQL SELECT Result]-2/4 rows returned

[zhangysh1995]

Bug Report

1. What did you do?

FLUSH TABLES;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a VARCHAR(255), b INT, INDEX (a,b) ) ENGINE=MyISAM;
CREATE TABLE t2 LIKE t1;

INSERT INTO t1 VALUES ('abcd ',1);
INSERT INTO t1 VALUES ('abcd',1);
INSERT INTO t2 VALUES ('abcd ',2);
INSERT INTO t2 VALUES ('abcd',2);
INSERT INTO t2 SELECT * FROM t2;

-- 2, incorrect
SELECT COUNT(*) x FROM t2 WHERE a = 'abcd' AND b = 2;

2. What did you expect to see?

Four rows returned.

3. What did you see instead?

mysql> SELECT COUNT(*) x FROM t2 WHERE a = 'abcd' AND b = 2;
+---+
| x |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE a = 'abcd' AND b = 2;
+------+------+
| a    | b    |
+------+------+
| abcd |    2 |
| abcd |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+-------+------+
| a     | b    |
+-------+------+
| abcd  |    2 |
| abcd  |    2 |
| abcd  |    2 |
| abcd  |    2 |
+-------+------+
4 rows in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

Reproducible on master branch.

commit 2efab88a59499ea3934c8c1eba7b695cf24b9d29 (HEAD -> master, origin/master, origin/HEAD)
Author: Wenxuan <hi@breeswish.org>
Date:   Fri May 15 21:53:32 2020 +0800````

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[zhangysh1995]

Furthermore:

mysql> select * from t1 where a = 'abcd';
+------+------+
| a    | b    |
+------+------+
| abcd |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t1;
+-------+------+
| a     | b    |
+-------+------+
| abcd  |    1 |
| abcd  |    1 |
+-------+------+
2 rows in set (0.00 sec)

[zhangysh1995]

And,

mysql> select * from t1 where a like 'abcd';
+------+------+
| a    | b    |
+------+------+
| abcd |    1 |
+------+------+
1 row in set (0.00 sec)

[shuke987]

/bug P0

[wwar]

I don't believe this is a bug. The two missing rows have an extra space to the right. Here is the testcase in MySQL 8.0.20 (with InnoDB):

mysql [localhost:8020] {msandbox} (test) > -- 2, incorrect
mysql [localhost:8020] {msandbox} (test) > SELECT COUNT(*) x FROM t2 WHERE a = 'abcd' AND b = 2;
+---+
| x |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

[zhangysh1995]

I don't believe this is a bug. The two missing rows have an extra space to the right.

But in your example it only have two rows, it should have four rows.

[zhangysh1995]

@wwar Please see the example here https://bugs.mysql.com/bug.php?id=24342 (the comment [16 Nov 2006 17:10] Dean Ellis). If the result you gave is correct, then there is a bug in the current version or there was a bug in the old version. I mean for MySQL.

Please make sure you ran the last line of this test case, t2 has four rows.

FLUSH TABLES;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a VARCHAR(255), b INT, INDEX (a,b) ) ENGINE=MyISAM;
CREATE TABLE t2 LIKE t1;

INSERT INTO t1 VALUES ('abcd ',1);
INSERT INTO t1 VALUES ('abcd',1);
INSERT INTO t2 VALUES ('abcd ',2);
INSERT INTO t2 VALUES ('abcd',2);
INSERT INTO t2 SELECT * FROM t2;

This is the behavior of MySQL 5.7.29:

mysql> select 'abcd' = 'abcd ';
+------------------+
| 'abcd' = 'abcd ' |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

For MySQL 8, I cannot create the table:

mysql> CREATE TABLE t1 ( a VARCHAR(255), b INT, INDEX (a,b) ) ENGINE=MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

mysql> select 'abcd' = 'abcd ';
+------------------+
| 'abcd' = 'abcd ' |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)