REGEXP together with CONCAT(..REPLACE..) might return unexpected result

[D3Hunter]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tidb/pkg/session/bootstrap.go

Line 475 in e917aa6

AND CONCAT(',', tidb_mdl_info.table_ids, ',') REGEXP CONCAT(',', REPLACE(cluster_tidb_trx.related_table_ids, ',', '|'), ',') != 0

tidb/pkg/ddl/job_table.go

Line 219 in e917aa6

"(processing and CONCAT(',', t2.table_ids, ',') REGEXP CONCAT(',', REPLACE(t1.table_ids, ',', '|'), ',') != 0)"+

above code uses REPLACE(comma-separated-id-list, ',', '|') to generate regex pattern to match whether there are ids inside another comma-separated-id-list expr, but when the expr part contains some number prefix of comma-separated-number-list, it might return unexpected result. suppose expr is 22, comma-separated-number-list is 1,2,3:

mysql> select CONCAT(',', '22', ',') REGEXP CONCAT(',', REPLACE('1,2,3', ',', '|'), ',');
+----------------------------------------------------------------------------+
| CONCAT(',', '22', ',') REGEXP CONCAT(',', REPLACE('1,2,3', ',', '|'), ',') |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

master

[YangKeao]

How about using JSON_OVERLAPS 🤔?

mysql> select json_overlaps(concat('[', '22', ']'), concat('[', '1,2,3', ']'));
+------------------------------------------------------------------+
| json_overlaps(concat('[', '22', ']'), concat('[', '1,2,3', ']')) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_overlaps(concat('[', '22', ']'), concat('[', '1,2,3,22', ']'));
+---------------------------------------------------------------------+
| json_overlaps(concat('[', '22', ']'), concat('[', '1,2,3,22', ']')) |
+---------------------------------------------------------------------+
|                                                                   1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Let me try!