Bug #119125 after modifying the insertion sequence of the table,the results of the same query are inconsistent.

• Submitted: 10 Oct 3:52
• Reporter: Alice Alice
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[10 Oct 3:52] Alice Alice

Description:
after modifying the insertion sequence of the table,the results of the same query are inconsistent.

query result for schene 1:
mysql> SELECT t0.* FROM  t1 RIGHT JOIN t0 ON t1.c1 IS NULL WHERE (((DEFAULT(t1.c0))<('w'))>=(((t0.c1))) IS NULL);
+----+------+-----+
| c0 | c1   | c2  |
+----+------+-----+
| 1  | NULL | 123 |
| 2  |  100 | 456 |
+----+------+-----+
2 rows in set (0.00 sec)

query result for scene 2:
mysql> SELECT t0.* FROM  t1 RIGHT JOIN t0 ON t1.c1 IS NULL WHERE (((DEFAULT(t1.c0))<('w'))>=(((t0.c1))) IS NULL);
+----+------+-----+
| c0 | c1   | c2  |
+----+------+-----+
| 1  | NULL | 123 |
+----+------+-----+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` char(1) NOT NULL,
  `c1` decimal(10,0),
  `c2` varchar(201) NOT NULL
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c0` char(1) NOT NULL DEFAULT 'x',
  `c1` decimal(10,0)
) ENGINE=InnoDB;
INSERT INTO `t1` VALUES ('a',1),('b',2),('c',3),('d',4),('e',5);

TRUNCATE TABLE t0;
INSERT INTO `t0` VALUES ('1',NULL,'123'),('2',100,'456');
SELECT t0.* FROM  t1 RIGHT JOIN t0 ON t1.c1 IS NULL WHERE (((DEFAULT(t1.c0))<('w'))>=(((t0.c1))) IS NULL);

TRUNCATE TABLE t0;
INSERT INTO `t0` VALUES ('2',100,'456'),('1',NULL,'123');
SELECT t0.* FROM  t1 RIGHT JOIN t0 ON t1.c1 IS NULL WHERE (((DEFAULT(t1.c0))<('w'))>=(((t0.c1))) IS NULL);

Suggested fix:
the query result for scene 2 is wrong. it also shall return 2 rows.