regular expression REGEXP is case-sensitive in TiDB which is not in MySQL

[tshqin]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table t (a char(10), b varchar(10), c binary(10), d varbinary(10));
insert into t values ('text','text','text','text');

mysql> desc t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | char(10)      | YES  |     | NULL    |       |
| b     | varchar(10)   | YES  |     | NULL    |       |
| c     | binary(10)    | YES  |     | NULL    |       |
| d     | varbinary(10) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from t;
+------+------+------------+------+
| a    | b    | c          | d    |
+------+------+------------+------+
| text | text | text       | text |
+------+------+------------+------+
1 row in set (0.00 sec)

2. What did you expect to see?

MySQL

mysql> select * from t where a REGEXP 'Xt' =1;
+------+------+------------+------+
| a    | b    | c          | d    |
+------+------+------------+------+
| text | text | text       | text |
+------+------+------------+------+
1 row in set (0.00 sec)

mysql> select * from t where b REGEXP 'Xt' =1;
+------+------+------------+------+
| a    | b    | c          | d    |
+------+------+------------+------+
| text | text | text       | text |
+------+------+------------+------+
1 row in set (0.00 sec)

mysql> select * from t where c REGEXP 'Xt' =1;
Empty set (0.00 sec)

mysql> select * from t where d REGEXP 'Xt' =1;
Empty set (0.00 sec)

3. What did you see instead?

TiDB

MySQL [gin]> select * from t where a REGEXP 'Xt' =1;
Empty set (0.00 sec)

MySQL [gin]> select * from t where b REGEXP 'Xt' =1;
Empty set (0.00 sec)

MySQL [gin]> select * from t where c REGEXP 'Xt' =1;
Empty set (0.00 sec)

MySQL [gin]> select * from t where d REGEXP 'Xt' =1;
Empty set (0.00 sec)

MySQL [gin]> select * from t where a REGEXP '[Xt]' =1;
+------+------+------------+------+
| a    | b    | c          | d    |
+------+------+------------+------+
| text | text | text       | text |
+------+------+------------+------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V)?
   Git Commit Hash: 365e4ab
   Git Commit Branch: master
   UTC Build Time: 2017-08-16 01:24:27

[zz-jason]

thanks for your feed back, REGEXP is rewritten in #4331.
to #4080