Merge Join executed incorrect resultset which returned NULL

[xiutangzju]

Bug Report

1. Minimal reproduce step

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

INSERT INTO t1 VALUES (1,'0'),(2,'NULL'),(3,'false'),(4,NULL),(5,NULL),(6,NULL),(7,''),(8,'0000-00-00 00:00:00'),(9,' ');

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

INSERT INTO t2 VALUES (1,''),(2,NULL),(3,NULL),(4,''),(5,NULL),(6,NULL),(7,NULL),(8,''),(9,'-1'),(10,'-0'),(11,'1'),(12,'1'),(13,' '),(14,'0000-00-00 00:00:00');

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

INSERT INTO t3 VALUES (15,'0000-00-00 00:00:00'),(16,NULL),(17,NULL),(18,'NULL'),(19,'-0'),(20,NULL),(21,''),(22,'-0');

2. What did you expect to see?

Correct Merge Join when transforming hash join to merge join.

3. What did you see instead

mysql> SELECT /+ merge_join(t1, t2, t3)/ t3.col1 FROM (t1 LEFT JOIN t2 ON t1.col1=t2.col1) LEFT JOIN t3 ON t2.col1=t3.col1;
+------+
| col1 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
11 rows in set (0.00 sec)

mysql> SELECT /+ hash_join(t1, t2, t3)/ t3.col1 FROM (t1 LEFT JOIN t2 ON t1.col1=t2..col1) LEFT JOIN t3 ON t2.col1=t3.col1;
+---------------------+
| col1 |
+---------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| |
| |
| |
| 0000-00-00 00:00:00 |
| NULL |
+---------------------+
11 rows in set (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(t1, t2, t3)*/ t3.col1 FROM (select t2.col1 from t1 LEFT JOIN t2 ON t1.col1=t2.col1) t2 LEFT JOIN t3 ON t2.col1=t3.col1;

Duplicate with #33042