Incorrect Results by REGEXP

[bajinsheng]

Bug Report

Inconsistent results by REGEXP.

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES (0.01);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;

SELECT * FROM t0 RIGHT JOIN v0 ON DEFAULT(t0.c0); -- (NULL|0}
SELECT (v0.c0)REGEXP(v0.c0 AND t0.c0) FROM t0 RIGHT JOIN v0 ON DEFAULT(t0.c0); -- {1}
SELECT * FROM t0 RIGHT JOIN v0 ON DEFAULT(t0.c0) WHERE (v0.c0)REGEXP(v0.c0 AND t0.c0); -- Empty set

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

The first SELECT returns non-empty results and the predicate (v0.c0)REGEXP(v0.c0 AND t0.c0) is evaluated TRUE in the second SELECT, but the third SELECT returns nothing.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

| Release Version: v6.4.0-alpha-12-g51a6684fb
Edition: Community
Git Commit Hash: 51a6684fb767cfd86a8312e0e6447963b927c791
Git Branch: master
UTC Build Time: 2022-10-05 12:40:23
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |

[Yisaer]

The issue has the same root cause as #38304 and fixed by #38430

Now this is fixed in master:

mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t0(c0) VALUES (0.01);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t0 RIGHT JOIN v0 ON DEFAULT(t0.c0);
+------+------+
| c0   | c0   |
+------+------+
| NULL |    0 |
+------+------+
1 row in set (0.01 sec)

mysql> SELECT (v0.c0)REGEXP(v0.c0 AND t0.c0) FROM t0 RIGHT JOIN v0 ON DEFAULT(t0.c0); 
+--------------------------------+
| (v0.c0)REGEXP(v0.c0 AND t0.c0) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t0 RIGHT JOIN v0 ON DEFAULT(t0.c0) WHERE (v0.c0)REGEXP(v0.c0 AND t0.c0);
+------+------+
| c0   | c0   |
+------+------+
| NULL |    0 |
+------+------+
1 row in set (0.00 sec)