Inconsistent evaluation of CAST from BINARY to DECIMAL in JOIN condition depending on JOIN type

## Bug Report

Please answer these questions before submitting your issue. Thanks!

#### I am testing the same test sample in MariaDB and the output from MariaDB is as follows:
MariaDB [database5]> SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)));-- cardinality: 6\n\n
+------------------------+
| c0 |
+------------------------+
| 0000.11197871954456351 |
| 0000.16273595084851133 |
| 0000.20812375997639154 |
| 0000.23265386506574004 |
| 00000.2681707116377101 |
| 00000.2976326296544005 |
+------------------------+
6 rows in set (0.000 sec)

MariaDB [database5]> (SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL))));-- cardinality: 0\n;
+---------------------+
| c0 |
+---------------------+
| 0.11197871954456351 |
| 0.16273595084851133 |
| 0.20812375997639154 |
| 0.23265386506574004 |
| 0.2681707116377101 |
| 0.2976326296544005 |
+---------------------+
6 rows in set (0.001 sec)

MariaDB [database5]> SELECT VERSION();
+--------------------------+
| VERSION() |
+--------------------------+
| 10.11.11-MariaDB-ubu2204 |
+--------------------------+
1 row in set (0.000 sec)

#### I tested the same sample test in MySQL and the output from MySQL is as follows:
mysql> SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)));-- cardinality: 6\n\n
+------------------------+
| c0 |
+------------------------+
| 0000.11197871954456351 |
| 0000.16273595084851133 |
| 0000.20812375997639154 |
| 0000.23265386506574004 |
| 00000.2681707116377101 |
| 00000.2976326296544005 |
+------------------------+
6 rows in set, 1 warning (0.01 sec)

mysql> (SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL))));-- cardinality: 0\n;
+---------------------+
| c0 |
+---------------------+
| 0.2976326296544005 |
| 0.2681707116377101 |
| 0.23265386506574004 |
| 0.20812375997639154 |
| 0.16273595084851133 |
| 0.11197871954456351 |
+---------------------+
6 rows in set, 2 warnings (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 9.2.0 |
+-----------+
1 row in set (0.00 sec)

### 1. Minimal reproduce step (Required)

------------------------------------------------------------
USE test;
DROP DATABASE IF EXISTS database5;
CREATE DATABASE database5;
USE database5;

CREATE TABLE t0(c0 NUMERIC UNSIGNED NOT NULL );

CREATE TABLE t1(c0 DOUBLE UNSIGNED ZEROFILL NOT NULL );

REPLACE INTO t1 VALUES (0.6510498879940168);

INSERT INTO t1(c0) VALUES (0.6147584189384411) ON DUPLICATE KEY UPDATE c0=((-1266649629)NOT REGEXP(0.6610080868590832));
INSERT IGNORE INTO t0 VALUES (246220283);

REPLACE INTO t1 VALUES (0.20812375997639154), (0.6469508527197805);
INSERT IGNORE INTO t1 VALUES (0.629643565285494);

INSERT IGNORE INTO t1(c0) VALUES (0.23265386506574004), (0.2976326296544005) ON DUPLICATE KEY UPDATE c0=t1.c0;
INSERT INTO t1 VALUES (0.11197871954456351);
UPDATE t0 SET c0=478936323 WHERE t0.c0;

INSERT IGNORE INTO t0 VALUES (299131136), (-1304184174) ON DUPLICATE KEY UPDATE c0=(CASE '{}(3c' WHEN ((NULL)OR(t0.c0)) THEN IS_IPV4_MAPPED(-1051022434) ELSE t0.c0 END );

REPLACE INTO t1 VALUES (0.5560460698581687);
UPDATE t0 SET c0=((t0.c0) IS NULL);
INSERT IGNORE INTO t0 VALUES (-349300241) ON DUPLICATE KEY UPDATE c0=2012722022;
INSERT INTO t1(c0) VALUES (0.6198722353479187) ON DUPLICATE KEY UPDATE c0=(CASE 779537112 WHEN CONNECTION_ID() THEN t1.c0 ELSE t1.c0 END );
INSERT IGNORE INTO t1 VALUES (0.8261432813413246);
REPLACE LOW_PRIORITY INTO t1(c0) VALUES (0.5633427102374985);
ALTER TABLE t1 ADD PRIMARY KEY(c0);
INSERT HIGH_PRIORITY INTO t0 VALUES (337857216);

REPLACE INTO t1(c0) VALUES (0.7209372779747613);
INSERT IGNORE INTO t0(c0) VALUES (-1611804866);
REPLACE INTO t0 VALUES (867073115), (632398856);
INSERT INTO t0(c0) VALUES (1117083163), (1289565736);
ALTER TABLE t0 ORDER BY c0 DESC;

REPLACE INTO t1 VALUES (0.8279180455107167), (0.16273595084851133);
INSERT INTO t0(c0) VALUES (1958185744) ON DUPLICATE KEY UPDATE c0=((t0.c0)NOT LIKE(t0.c0));
REPLACE INTO t0(c0) VALUES (1674436059);
INSERT IGNORE INTO t1(c0) VALUES (0.2681707116377101);

SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)));-- cardinality: 6\n\n
(SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL))));-- cardinality: 0\n;

### 2. What did you expect to see? (Required)
mysql> SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)));-- cardinality: 6\n\n
+------------------------+
| c0 |
+------------------------+
| 0000.11197871954456351 |
| 0000.16273595084851133 |
| 0000.20812375997639154 |
| 0000.23265386506574004 |
| 00000.2681707116377101 |
| 00000.2976326296544005 |
+------------------------+
6 rows in set, 1 warning (0.01 sec)

mysql> (SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL))));-- cardinality: 0\n;
+---------------------+
| c0 |
+---------------------+
| 0.2976326296544005 |
| 0.2681707116377101 |
| 0.23265386506574004 |
| 0.20812375997639154 |
| 0.16273595084851133 |
| 0.11197871954456351 |
+---------------------+
6 rows in set, 2 warnings (0.00 sec)
### 3. What did you see instead (Required)
mysql> SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)));-- cardinality: 6\n\n
+---------------------+
| c0 |
+---------------------+
| 0.2976326296544005 |
| 0.2681707116377101 |
| 0.20812375997639154 |
| 0.11197871954456351 |
| 0.23265386506574004 |
| 0.16273595084851133 |
+---------------------+
6 rows in set, 16 warnings (0.00 sec)

mysql> (SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL)))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((CAST(((t1.c0) IS NULL) AS CHAR))=(CAST((BINARY (t1.c0)) AS DECIMAL))));-- cardinality: 0\n;
Empty set (0.00 sec)

### 4. What is your TiDB version? (Required)

mysql> select version();
+--------------------+
| version() |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
I use docker images for quick deployments.
IMAGE ID: bd253ee807c6