regexp_like & regexp_instr result as the params of regexp_xxx will report an error

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a varchar(100), b varchar(50), c text, d char(10));
with cte1 as (with cte2 as (select regexp_like(a, '\\d') as stra, regexp_substr(c, '\\w') strb from t) select * from cte2) select regexp_replace(stra, '\\d', 'aaaaa'), regexp_instr(strb, '\\w') from cte1;

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

+--------------------------------------+---------------------------+
| regexp_replace(stra, '\\d', 'aaaaa') | regexp_instr(strb, '\\w') |
+--------------------------------------+---------------------------+
| aaaaa                                |                         1 |
+--------------------------------------+---------------------------+

3. What did you see instead (Required)

mysql> with cte1 as (with cte2 as (select regexp_like(a, '\\d') as stra, regexp_substr(c, '\\w') strb from t) select * from cte2) select regexp_replace(stra, '\\d', 'aaaaa'), regexp_instr(strb, '\\w') from cte1;
ERROR 1139 (42000): Got error 'Not support binary collation so far' from regexp

4. What is your TiDB version? (Required)

| tidb_version()                                                                                                                                                                                                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.3.0-alpha
Edition: Community
Git Commit Hash: 29f83a0b25926390c757f71a84baa2322a289f78
Git Branch: heads/refs/tags/v6.3.0-alpha
UTC Build Time: 2022-09-18 14:25:04
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[dveeden]

With TiDB v8.0.0 this happens:

tidb> SELECT REGEXP_INSTR(NULL, NULL);
ERROR 1139 (42000): Got error 'Not support binary collation so far' from regexp

MySQL 8.3.0 returns NULL for the same query.

@aytrack , @xzhangxian1008 do you think this is the same issue?

[xzhangxian1008]

With TiDB v8.0.0 this happens:

tidb> SELECT REGEXP_INSTR(NULL, NULL);
ERROR 1139 (42000): Got error 'Not support binary collation so far' from regexp

MySQL 8.3.0 returns NULL for the same query.

@aytrack , @xzhangxian1008 do you think this is the same issue?

Yes, it's the same issue.

create table test1 (c1 varchar(10), c2 varchar(10));
insert into test1 values (null, null); 
select regexp_instr(c1, c2) from test1; # It will return null