Tidb: select return empty when using non-primary key auto_increment column as where condition

[RevoKey]

1. What did you do?

mysql> CREATE TABLE test(id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, p int(10) UNSIGNED NOT NULL, PRIMARY KEY(p), KEY(id));     
Query OK, 0 rows affected (0.16 sec)

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `p` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`p`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test(p) value(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+----+---+
| id | p |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

mysql> select * from test where p=1;
+----+---+
| id | p |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

mysql> select * from test where id=1;
Empty set (0.00 sec)

1. What did you expect to see?

mysql> select * from test where id=1;
+----+---+
| id | p |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

1. What did you see instead?

mysql> select * from test where id=1;
Empty set (0.00 sec)

1. What version of TiDB are you using (tidb-server -V)?

Release Version: 1.0.0
Git Commit Hash: f4e08c15f41f52475184a181ea597ee1cc855c90
Git Commit Branch: release-1.0
UTC Build Time:  2017-10-16 05:32:55

Use id as primary key instead then is fine:

mysql> CREATE TABLE test(id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, p int(10) UNSIGNED NOT NULL, PRIMARY KEY(id), KEY(p));
Query OK, 0 rows affected (0.16 sec)

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `p` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `p` (`p`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test(p) value(1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+---+
| id | p |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

mysql> select * from test where id=1;
+----+---+
| id | p |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.01 sec)

mysql> select * from test where p=1;
+----+---+
| id | p |
+----+---+
|  1 | 1 |
+----+---+
1 row in set (0.00 sec)

[shenli]

@RevoKey Thanks for your feedback!
@hanfei1991 @winoros PTAL

[winoros]

@RevoKey master has fixed this problem. It'll be cherry picked to release-1.0 branch soon.