Incorrect range for CHAR BINARY column

[zz-jason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

drop table if exists t;
create table t(a char(2) binary primary key, b char(2));
insert into t values("  ", "  ");
insert into t values("a ", "b ");

-- case 1:
set @@sql_mode="";
select * from t tmp where a = "a ";

-- case 2:
set @@sql_mode="PAD_CHAR_TO_FULL_LENGTH";
select * from t tmp where a = "a  ";

2. What did you expect to see?

In MySQL the results of case 1 and case 2 are not empty:

MySQL(root@localhost:test) > -- case 1:
MySQL(root@localhost:test) > set @@sql_mode="";
Query OK, 0 rows affected (0.00 sec)

MySQL(root@localhost:test) > select * from t tmp where a = "a ";
+---+------+
| a | b    |
+---+------+
| a | b    |
+---+------+
1 row in set (0.00 sec)

MySQL(root@localhost:test) > -- case 2:
MySQL(root@localhost:test) > set @@sql_mode="PAD_CHAR_TO_FULL_LENGTH";
Query OK, 0 rows affected (0.00 sec)

MySQL(root@localhost:test) > select * from t tmp where a = "a  ";
+----+------+
| a  | b    |
+----+------+
| a  | b    |
+----+------+
1 row in set (0.00 sec)

MySQL version:

MySQL(root@localhost:test) > select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead?

In TiDB both results are empty:

TiDB(root@127.0.0.1:test) > -- case 1:
TiDB(root@127.0.0.1:test) > set @@sql_mode="";
Query OK, 0 rows affected (0.01 sec)

TiDB(root@127.0.0.1:test) > select * from t tmp where a = "a ";
Empty set (0.00 sec)

TiDB(root@127.0.0.1:test) > desc select * from t tmp where a = "a ";
+-------------------+-------+------+-----------------------------------------------------------------------+
| id                | count | task | operator info                                                         |
+-------------------+-------+------+-----------------------------------------------------------------------+
| IndexLookUp_7     | 10.00 | root |                                                                       |
| ├─IndexScan_5     | 10.00 | cop  | table:tmp, index:a, range:["a ","a "], keep order:false, stats:pseudo |
| └─TableScan_6     | 10.00 | cop  | table:t, keep order:false, stats:pseudo                               |
+-------------------+-------+------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

TiDB(root@127.0.0.1:test) > -- case 2:
TiDB(root@127.0.0.1:test) > set @@sql_mode="PAD_CHAR_TO_FULL_LENGTH";
Query OK, 0 rows affected (0.00 sec)

TiDB(root@127.0.0.1:test) > select * from t tmp where a = "a  ";
Empty set (0.00 sec)

TiDB(root@127.0.0.1:test) > desc select * from t tmp where a = "a  ";
+-------------------+-------+------+-------------------------------------------------------------------------+
| id                | count | task | operator info                                                           |
+-------------------+-------+------+-------------------------------------------------------------------------+
| IndexLookUp_7     | 10.00 | root |                                                                         |
| ├─IndexScan_5     | 10.00 | cop  | table:tmp, index:a, range:["a  ","a  "], keep order:false, stats:pseudo |
| └─TableScan_6     | 10.00 | cop  | table:t, keep order:false, stats:pseudo                                 |
+-------------------+-------+------+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

4. What version of TiDB are you using?

TiDB(root@127.0.0.1:test) > select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.0-beta.1-231-g78ecec45e
Git Commit Hash: 78ecec45e8c02d6a8747b1e95ee25373c93d3edd
Git Branch: master
UTC Build Time: 2019-05-09 01:11:02
GoVersion: go version go1.12.4 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

[zz-jason]

index lookup join may also have the same issue

[zz-jason]

Relates to #10124, which is point get.