Unexpected Results by NATURE RIGHT JOIN

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT ZEROFILL);
CREATE TABLE t1(c0 INT ZEROFILL);
INSERT IGNORE INTO t0 VALUES (-1);
INSERT IGNORE INTO t1 VALUES (-2);

SELECT t1.c0 FROM  t1 NATURAL RIGHT JOIN t0; -- {0}
SELECT t1.c0 FROM  t1 NATURAL RIGHT JOIN t0 WHERE IS_IPV4(t0.c0) REGEXP CONCAT_WS('a', FALSE, t1.c0); -- {NULL}

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

The second query should return a subset of the results of the first query.

3. What did you see instead (Required)

The second query returns a NULL.

4. What is your TiDB version? (Required)

| Release Version: v7.6.0-alpha-347-g598ccada40
Edition: Community
Git Commit Hash: 598ccada4065e4871bc5bce53a039cbb598a6d82
Git Branch: master
UTC Build Time: 2024-03-04 06:43:19
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore |

[bajinsheng]

/label fuzz/sqlancer

[winoros]

mysql> explain SELECT t1.c0 FROM  t1 RIGHT JOIN t0 on t1.c0=t0.c0 WHERE IS_IPV4(t0.c0) REGEXP CONCAT_WS('a', FALSE, t1.c0); -- {NULL}
+------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows  | task      | access object | operator info                                                                                                                  |
+------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------+
| Projection_7                       | 8000.00  | root      |               | test.t1.c0                                                                                                                     |
| └─Selection_8                      | 8000.00  | root      |               | regexp(cast(is_ipv4(cast(test.t0.c0, var_string(20))), var_string(20)), concat_ws("a", "0", cast(test.t1.c0, var_string(20)))) |
|   └─HashJoin_10                    | 10000.00 | root      |               | right outer join, equal:[eq(test.t1.c0, test.t0.c0)]                                                                           |
|     ├─Selection_14(Build)          | 7992.00  | root      |               | regexp(cast(is_ipv4(cast(test.t1.c0, var_string(20))), var_string(20)), concat_ws("a", "0", cast(test.t1.c0, var_string(20)))) |
|     │ └─TableReader_13             | 9990.00  | root      |               | data:Selection_12                                                                                                              |
|     │   └─Selection_12             | 9990.00  | cop[tikv] |               | not(isnull(test.t1.c0))                                                                                                        |
|     │     └─TableFullScan_11       | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                 |
|     └─TableReader_16(Probe)        | 10000.00 | root      |               | data:TableFullScan_15                                                                                                          |
|       └─TableFullScan_15           | 10000.00 | cop[tikv] | table:t0      | keep order:false, stats:pseudo                                                                                                 |
+------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)

Seems the filter is wrongly pushed via the propagation by the join keyt0.c0=t1.c0.