Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
D3Hunter opened this issue Apr 19, 2024 · 1 comment · May be fixed by #52879
Open

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

D3Hunter opened this issue Apr 19, 2024 · 1 comment · May be fixed by #52879
Labels
affects-6.5 affects-7.1 affects-7.5 affects-8.1 severity/major sig/sql-infra SIG: SQL Infra type/bug This issue is a bug.

Comments

@D3Hunter
Copy link
Contributor

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
@D3Hunter D3Hunter added the type/bug This issue is a bug. label Apr 19, 2024
@YangKeao
Copy link
Member

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!

@YangKeao YangKeao linked a pull request Apr 25, 2024 that will close this issue
Open
13 tasks
@YangKeao YangKeao removed may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 labels Apr 25, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affects-6.5 affects-7.1 affects-7.5 affects-8.1 severity/major sig/sql-infra SIG: SQL Infra type/bug This issue is a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

ddl,mdl: fix the issue that the table_ids overlap regex is not correct YangKeao/tidb
3 participants
@D3Hunter @jebter @YangKeao