Merge Join executed incorrect resultset which missed -0

[xiutangzju]

Bug Report

1. Minimal reproduce step

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

CREATE TABLE t2 (
id bigint(64) NOT NULL AUTO_INCREMENT,
col1 int(16) NOT NULL,
col2 double DEFAULT NULL,
col3 varchar(511) DEFAULT NULL,
PRIMARY KEY (id,col1)
);

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

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

INSERT INTO t1 VALUES (39,65535,'-0'),(40,65535,NULL),(35,1,'NULL'),(37,1,NULL),(42,1,'1'),(46,1,'0000-00-00 00:00:00'),(36,-1,'2020-02-02 02:02:00'),(38,0,''),(41,0,'-0'),(43,-1,'-0'),(44,-1,'true'),(45,0,'1'),(47,0,'0'),(48,-1,'2020-02-02 02:02:00');

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

INSERT INTO t3 VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL),(5,NULL),(6,NULL),(7,NULL),(8,NULL),(9,NULL),(10,NULL),(11,NULL),(12,NULL);

INSERT INTO t4 VALUES (1,'-0'),(2,'NULL'),(3,NULL),(4,'-0'),(5,'-1'),(6,'true'),(7,NULL),(8,'true'),(9,NULL),(10,'0'),(11,NULL);

SELECT /+ hash_join(t4, t3)/ t4.col1
FROM ((t1 RIGHT JOIN t2 ON t1.col2=t2.col3) LEFT JOIN t3 ON t2.col1=t3.id) LEFT JOIN t4 ON t3.id=t4.id;

SELECT /+ merge_join(t4, t3)/ t4.col1
FROM ((t1 RIGHT JOIN t2 ON t1.col2=t2.col3) LEFT JOIN t3 ON t2.col1=t3.id) LEFT JOIN t4 ON t3.id=t4.id;

2. What did you expect to see?

Correct Merge Join when transforming hash join to merge join.

3. What did you see instead (Required)

mysql> SELECT /+ hash_join(t4, t3)/ t4.col1 FROM ((t1 RIGHT JOIN t2 ON t1.col2=t2.col3) LEFT JOIN t3 ON t2.col1=t3.id) LEFT JOIN t4 ON t3.id=t4.id;
+------+
| col1 |
+------+
| -0 |
| -0 |
| -0 |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
19 rows in set (0.00 sec)

mysql> SELECT /+ merge_join(t4, t3)/ t4.col1 FROM ((t1 RIGHT JOIN t2 ON t1.col2=t2.col3) LEFT JOIN t3 ON t2.col1=t3.id) LEFT JOIN t4 ON t3.id=t4.id;
+------+
| col1 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
19 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

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]

duplicate with #33042, close this one