Merge Join executed incorrect resultset which missed rows

[xiutangzju]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t1 (
id bigint(64) NOT NULL AUTO_INCREMENT,
col1 varchar(511) DEFAULT NULL,
PRIMARY KEY (id)
);

INSERT INTO t1 VALUES (12,NULL),(13,''),(14,'0000-00-00 00:00:00'),(15,NULL),(16,NULL),(17,' '), (18,NULL),(19,NULL),(20,' '),(21,'0'),(22,'-1'),(23,'0000-00-00 00:00:00'),(24,'0'),(25,NULL),(26,'2020-02-02 02:02:00'),(27,'0000-00-00 00:00:00'),(28,NULL),(29,'-1'),(30,'1');

CREATE TABLE t2 (
id bigint(64) NOT NULL AUTO_INCREMENT,
col1 double DEFAULT NULL,
PRIMARY KEY (id)
);

INSERT INTO t2 VALUES (1,-1),(2,-1),(3,-1),(4,-1),(5,-1),(6,-1),(7,-1),(8,-1),(9,-1),(10,-1),(11,-1),(12,-1),(13,-1);

CREATE TABLE t3 (
id bigint(64) NOT NULL AUTO_INCREMENT,
col1 int(16) NOT NULL,
PRIMARY KEY (id,col1)
);

INSERT INTO t3 VALUES (2,-1),(5,0),(8,0),(10,-1),(11,-1),(12,-1),(4,1),(7,1),(13,1),(1,65535),(3,65535),(6,65535),(9,65535),(14,65535);

2. What did you expect to see?

Correct Merge Join when transforming hash join to merge join.

3. What did you see instead

SELECT /+ hash_join(t3, t1, t2)/ t1.id FROM (t1 LEFT JOIN t2 ON t1.col1=t2.col1) RIGHT JOIN t3 ON t2.col1=t3.col1;
114 rows in set, 4 warnings (0.00 sec)

SELECT /+ merge_join(t3, t1, t2)/ t1.id FROM (t1 LEFT JOIN t2 ON t1.col1=t2.col1) RIGHT JOIN t3 ON t2.col1=t3.col1;
14 rows in set, 4 warnings (0.00 sec)

4. What is your TiDB version?

mysql> SELECT tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0
Edition: Community
Git Commit Hash: 55f3b24
Git Branch: heads/refs/tags/v5.4.0
UTC Build Time: 2022-01-25 08:39:26
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[XuHuaiyu]

The following sql works fine

SELECT /*+ merge_join(t3, t1, t2)*/ t2.id FROM (select t1.id,t2.col1 from t1 LEFT JOIN t2 ON t1.col1=t2.col1 order by t2.col1) t2  RIGHT JOIN t3 ON t2.col1=t3.col1;

Duplicate with #33042