bug: insert ... select distinct,The correct test result was not detected

[shangyanwen]

Describe the problem

create table t1 (email varchar(50))engine=tianmu;
insert into t1 values ('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]');
create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2))engine=tianmu;
insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
select * from t2;
#Incorrect test results
Empty set (0.00 sec)

Expected behavior

# Correct test results（note:Innodb test verification, test results are as follows）
 select * from t2;
+----+-------------+
| id | t2          |
+----+-------------+
|  3 | aol.com     |
|  2 | hotmail.com |
|  1 | mysql.com   |
+----+-------------+

How To Reproduce

create table t1 (email varchar(50))engine=tianmu;
insert into t1 values ('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]');
create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2))engine=tianmu;
insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
select * from t2;

Environment

[root@dev bin]# ./mysqld --version
./mysqld  Ver 5.7.36_v1.0.0_beta-StoneDB for Linux on x86_64 (build-)
build information as follow: 
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7
        Branch name: stonedb-5.7
        Last commit ID: eed32f6
        Last commit time: Date:   Wed Aug 3 11:19:48 2022 +0800
        Build time: Date: Wed Aug  3 13:01:31 CST 2022

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

• Yes, I will!

[adofsauron]

mysql> 
mysql>     create table t1 (email varchar(50))engine=tianmu;
Query OK, 0 rows affected (0.01 sec)

mysql>     insert into t1 values ('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> select distinct substring(email, locate('@', email)+1) from t1;
+----------------------------------------+
| substring(email, locate('@', email)+1) |
+----------------------------------------+
| mysql.com                              |
| hotmail.com                            |
| aol.com                                |
+----------------------------------------+
3 rows in set (0.00 sec)

[adofsauron]

The reason is that the insertion delay is set

mysql>  show variables like '%insert_delay%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| max_insert_delayed_threads | 20    |
| tianmu_insert_delayed      | ON    |
+----------------------------+-------+
2 rows in set (0.01 sec)

mysql>     create table t1 (email varchar(50))engine=tianmu;
Query OK, 0 rows affected (0.00 sec)

mysql>     insert into t1 values ('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>     select * from t1;
Empty set (0.00 sec)

mysql> select * from t1;
+-----------------+
| email           |
+-----------------+
| sasha@mysql.com |
| monty@mysql.com |
| foo@hotmail.com |
| foo@aol.com     |
| bar@aol.com     |
+-----------------+
5 rows in set (0.00 sec)

mysql>     create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2))engine=tianmu;
Query OK, 0 rows affected (0.01 sec)

mysql>     insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql>     select * from t2;
Empty set (0.00 sec)

mysql>     select * from t2;
+----+-------------+
| id | t2          |
+----+-------------+
|  1 | mysql.com   |
|  2 | hotmail.com |
|  3 | aol.com     |
+----+-------------+
3 rows in set (0.00 sec)

[adofsauron]

If the tianmu_insert_delayed parameter is disabled, the result is normal

mysql> 
mysql> show variables like '%insert_delay%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| max_insert_delayed_threads | 20    |
| tianmu_insert_delayed      | OFF   |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql>     create table t1 (email varchar(50))engine=tianmu;
Query OK, 0 rows affected (0.00 sec)

mysql>     insert into t1 values ('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]'),('[email protected]');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>     select * from t1;
+-----------------+
| email           |
+-----------------+
| sasha@mysql.com |
| monty@mysql.com |
| foo@hotmail.com |
| foo@aol.com     |
| bar@aol.com     |
+-----------------+
5 rows in set (0.00 sec)

mysql> 
mysql>     create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2))engine=tianmu;
Query OK, 0 rows affected (0.01 sec)

mysql>     insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql>     select * from t2;
+----+-------------+
| id | t2          |
+----+-------------+
|  1 | mysql.com   |
|  2 | hotmail.com |
|  3 | aol.com     |
+----+-------------+
3 rows in set (0.00 sec)