generated wrong TableDual operator

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists IDT_MULTI15860STROBJSTROBJ;
CREATE TABLE `IDT_MULTI15860STROBJSTROBJ` (
  `COL1` enum('aa') DEFAULT NULL,
  `COL2` int(41) DEFAULT NULL,
  `COL3` year(4) DEFAULT NULL,
  KEY `U_M_COL4` (`COL1`,`COL2`),
  KEY `U_M_COL5` (`COL3`,`COL2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into IDT_MULTI15860STROBJSTROBJ  values("aa", 1013610488, 1982);
SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null;
SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null and t2.col1 = "aa";
prepare stmt from "SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null and t2.col1 = ?";
set @a="aa";
execute stmt using @a;

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

mysql> SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null;
Empty set (0.04 sec)

mysql> SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null and t2.col1 = "aa";
Empty set (0.04 sec)

mysql> prepare stmt from "SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null and t2.col1 = ?";
Query OK, 0 rows affected (0.06 sec)
Statement prepared

mysql> set @a="aa";
Query OK, 0 rows affected (0.29 sec)

mysql> execute stmt using @a;
Empty set (0.26 sec)

3. What did you see instead (Required)

MySQL [test]> SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null;
Empty set (0.00 sec)

MySQL [test]> SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null and t2.col1 = "aa";
Empty set (0.00 sec)

MySQL [test]> prepare stmt from "SELECT * FROM IDT_MULTI15860STROBJSTROBJ t1 RIGHT JOIN IDT_MULTI15860STROBJSTROBJ t2 ON t1.col1 <=> t2.col1 where t1.col1 is null and t2.col1 = ?";
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> set @a="aa";
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt using @a;
+------+------+------+------+------------+------+
| COL1 | COL2 | COL3 | COL1 | COL2       | COL3 |
+------+------+------+------+------------+------+
| NULL | NULL | NULL | aa   | 1013610488 | 1982 |
+------+------+------+------+------------+------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.5.0-alpha-279-gdb3e69e
Edition: Community
Git Commit Hash: db3e69eb68de9acfcebd69909e7309caed64914c
Git Branch: master
UTC Build Time: 2022-02-17 01:02:11
GoVersion: go1.17.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[qw4990]

Compared with the correct plan, the join-condition is missed:

[qw4990]

This issue is actually caused by wrong ranges, here is a minimal reproducible case:

drop table t1;
create table `t1` (`a` enum('aa') DEFAULT NULL, KEY `k` (`a`));
insert into t1 values("aa");
select a from t1 where a<=>'aa';