bug: LEFT JOIN...ON....，The query result data is incomplete

[shangyanwen]

Describe the problem

CREATE TABLE t1 (name varchar(255))engine=stonedb;
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)))engine=stonedb;
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
##The error results are as follows
+------+------+------+
| name | name | n    |
+------+------+------+
| bb   | bb   |    1 |
| aa   | aa   |    2 |
| cc   | cc   |    7 |
| aa   | NULL | NULL |
| ccc  | NULL | NULL |
+------+------+------+
5 rows in set (0.00 sec)

Expected behavior

##The following is：innodb test result

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+------+-------+------+
| name | name  | n    |
+------+-------+------+
| ccc  | NULL  | NULL |
| bb   | bb    |    1 |
| bb   | bb    |    6 |
| cc   | cc    |    3 |
| cc   | cc    |    5 |
| cc   | cc    |    7 |
| aa   | aa    |    2 |
| aa   | aa    |    2 |
+------+-------+------+
8 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE t1 (name varchar(255))engine=stonedb;
CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)))engine=stonedb;
INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa  '), ('aa');
INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc   ',3);
INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

Environment

1. StoneDB for mysql5.7 (release)
2. Ubuntu 20.04.4

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[hustjieke]

ping @gougexuanli

[shangyanwen]

stonedb 5.7_v1.0.1 Regression This bug still exists

[lylth]

stonedb supports strict sql mode, inserting values that contain spaces and values that do not contain spaces are different values. When the spaces are removed, the query result is correct.

mysql> CREATE TABLE t1 (name varchar(255))engine=tianmu;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)))engine=tianmu;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc'), ('aa'), ('aa');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc',3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (concat('cc', 0x06), 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES ('cc',5), ('bb',6), ('cc',7);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+------+------+------+
| name | name | n    |
+------+------+------+
| bb   | bb   |    1 |
| aa   | aa   |    2 |
| aa   | aa   |    2 |
| cc   | cc   |    3 |
| cc   | cc   |    5 |
| bb   | bb   |    6 |
| cc   | cc   |    7 |
| ccc  | NULL | NULL |
+------+------+------+
8 rows in set (0.00 sec)