bug: select * from t1,(...) and select * from (...),t1，The test results are inconsistent

[shangyanwen]

trafficstars

Describe the problem

create table t1 (a int not null auto_increment primary key)engine=stonedb;
insert into t1 values ();
insert into t1 values ();
insert into t1 values ();
# ,
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
+---+---+
| a | a |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+---+---+
9 rows in set (0.01 sec)

select * from t1, (t1 as t2 left join t1 as t3 using (a));
+---+---+
| a | a |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+---+---+
9 rows in set (0.00 sec)

Expected behavior

#note：Expect the results of the two SQL queries to be the same
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
select * from t1, (t1 as t2 left join t1 as t3 using (a));

#note：innodb  test results
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
+---+---+
| a | a |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+---+---+
9 rows in set (0.01 sec)

select * from (t1 as t2 left join t1 as t3 using (a)), t1;
+---+---+
| a | a |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+---+---+
9 rows in set (0.00 sec)

How To Reproduce

create table t1 (a int not null auto_increment primary key)engine=stonedb;
insert into t1 values ();
insert into t1 values ();
insert into t1 values ();
# select
select * from (t1 as t2 left join t1 as t3 using (a)), t1;
select * from t1, (t1 as t2 left join t1 as t3 using (a));

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!

[isredstar]

@shangyanwen The tianmu engine has default sorting function，while innodb not.

[shangyanwen]

This bug is not handled and is closed