add order by for the entire UNION result will get wrong result set

[irene-coming]

• dble version：
  5.6.29-dble-9.9.9.9-359645c-20181029082136

• preconditions ：

• configs：

schema.xml

<table name="aly_test" dataNode="dn1,dn2,dn3,dn4" rule="hash-four" />
<table name="aly_order" dataNode="dn1,dn2,dn3,dn4" rule="hash-four" />
<table name="a_manager" primaryKey="id" dataNode="dn1,dn2,dn3,dn4" rule="hash-four" />

• steps：
  step1. execute sqls to both dble and mysql

drop table if exists aly_test;
drop table if exists aly_order;
drop table if exists a_manager;
CREATE TABLE aly_test(`id` int(10) unsigned NOT NULL,`t_id` int(10) unsigned NOT NULL DEFAULT '0',`name` char(120) NOT NULL DEFAULT '',`pad` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `k_1` (`t_id`))DEFAULT CHARSET=UTF8;
CREATE TABLE aly_order(`id` int(10) unsigned NOT NULL,`o_id` int(10) unsigned NOT NULL DEFAULT '0',`name` char(120) NOT NULL DEFAULT '',`pad` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `k_1` (`o_id`))DEFAULT CHARSET=UTF8;
CREATE TABLE a_manager(`id` int(10) unsigned NOT NULL,`m_id` int(10) unsigned NOT NULL DEFAULT '0',`name` char(120) NOT NULL DEFAULT '',`pad` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `k_1` (`m_id`))DEFAULT CHARSET=UTF8;
insert into aly_test values(1,1,'test中id为1',1),(2,2,'test_2',2),(3,3,'test中id为3',4),(4,4,'$test$4',3),(5,5,'test...5',1),(6,6,'test6',6);
insert into aly_order values(1,1,'order中id为1',1),(2,2,'test_2',2),(3,3,'order中id为3',3),(4,4,'$order$4',4),(5,5,'order...5',1);
insert into a_manager values(1,1,'manager中id为1',1),(2,2,'test_2',2),(3,3,'manager中id为3',3),(4,4,'$manager$4',4),(5,5,'manager...5',6);

step2. execute sqls with mysql:

mysql> (select * from aly_test left join aly_order on aly_test.pad=aly_order.pad order by aly_test.id,aly_order.id)union (select * from aly_order left join a_manager on aly_order.pad=a_manager.pad) order by t_id, o_id;
+----+------+----------------+-----+------+------+------------------+------+
| id | t_id | name           | pad | id   | o_id | name             | pad  |
+----+------+----------------+-----+------+------+------------------+------+
|  1 |    1 | test中id为1    |   1 |    1 |    1 | order中id为1     |    1 |
|  1 |    1 | order中id为1   |   1 |    1 |    1 | manager中id为1   |    1 |
|  1 |    1 | test中id为1    |   1 |    5 |    5 | order...5        |    1 |
|  2 |    2 | test_2         |   2 |    2 |    2 | test_2           |    2 |
|  3 |    3 | order中id为3   |   3 |    3 |    3 | manager中id为3   |    3 |
|  3 |    3 | test中id为3    |   4 |    4 |    4 | $order$4         |    4 |
|  4 |    4 | $test$4        |   3 |    3 |    3 | order中id为3     |    3 |
|  4 |    4 | $order$4       |   4 |    4 |    4 | $manager$4       |    4 |
|  5 |    5 | test...5       |   1 |    1 |    1 | order中id为1     |    1 |
|  5 |    5 | order...5      |   1 |    1 |    1 | manager中id为1   |    1 |
|  5 |    5 | test...5       |   1 |    5 |    5 | order...5        |    1 |
|  6 |    6 | test6          |   6 | NULL | NULL | NULL             | NULL |
+----+------+----------------+-----+------+------+------------------+------+
12 rows in set (0.00 sec)

step3. execute sqls with dble:

mysql> (select * from aly_test left join aly_order on aly_test.pad=aly_order.pad order by aly_test.id,aly_order.id)union (select * from aly_order left join a_manager on aly_order.pad=a_manager.pad);
+----+------+----------------+-----+----+------+------------------+-----+
| id | t_id | name           | pad | id | o_id | name             | pad |
+----+------+----------------+-----+----+------+------------------+-----+
|  1 |    1 | order中id为1   |   1 |  1 |    1 | manager中id为1   |   1 |
|  5 |    5 | order...5      |   1 |  1 |    1 | manager中id为1   |   1 |
|  2 |    2 | test_2         |   2 |  2 |    2 | test_2           |   2 |
|  3 |    3 | order中id为3   |   3 |  3 |    3 | manager中id为3   |   3 |
|  4 |    4 | $order$4       |   4 |  4 |    4 | $manager$4       |   4 |
|  1 |    1 | test中id为1    |   1 |  1 |    1 | order中id为1     |   1 |
|  1 |    1 | test中id为1    |   1 |  5 |    5 | order...5        |   1 |
|  3 |    3 | test中id为3    |   4 |  4 |    4 | $order$4         |   4 |
|  4 |    4 | $test$4        |   3 |  3 |    3 | order中id为3     |   3 |
|  5 |    5 | test...5       |   1 |  1 |    1 | order中id为1     |   1 |
|  5 |    5 | test...5       |   1 |  5 |    5 | order...5        |   1 |
|  6 |    6 | test6          |   6 | NULL | NULL | NULL             | NULL |
+----+------+----------------+-----+----+------+------------------+-----+
12 rows in set (0.00 sec)

step4. execute sqls with dble:

mysql> (select * from aly_test left join aly_order on aly_test.pad=aly_order.pad order by aly_test.id,aly_order.id)union (select * from aly_order left join a_manager on aly_order.pad=a_manager.pad) order by t_id, o_id;
+----+------+------------------+-----+----+------+------------------+-----+
| id | t_id | name             | pad | id | o_id | name             | pad |
+----+------+------------------+-----+----+------+------------------+-----+
|  1 |    1 | manager中id为1   |   1 |  1 |    1 | manager中id为1   |   1 |
|  1 |    1 | order中id为1     |   1 |  1 |    1 | order中id为1     |   1 |
|  5 |    1 | order...5        |   1 |  5 |    5 | order...5        |   1 |
|  2 |    2 | test_2           |   2 |  2 |    2 | test_2           |   2 |
|  3 |    3 | manager中id为3   |   3 |  3 |    3 | manager中id为3   |   3 |
|  4 |    3 | $order$4         |   4 |  4 |    4 | $order$4         |   4 |
|  3 |    4 | order中id为3     |   3 |  3 |    3 | order中id为3     |   3 |
|  4 |    4 | $manager$4       |   4 |  4 |    4 | $manager$4       |   4 |
|  1 |    5 | manager中id为1   |   1 |  1 |    1 | manager中id为1   |   1 |
|  1 |    5 | order中id为1     |   1 |  1 |    1 | order中id为1     |   1 |
|  5 |    5 | order...5        |   1 |  5 |    5 | order...5        |   1 |
| NULL |    6 | NULL             | NULL | NULL | NULL | NULL             | NULL |
+----+------+------------------+-----+----+------+------------------+-----+
12 rows in set (0.01 sec)

• expect result：
  1. step4 sql got same result as step2
• real result：
  1. step4 result error
• supplements：
  1. step3 different from step4 at lack "order by t_id, o_id", may be key point to the issue

2. for tester:
   suggest autotest position: sqls/select/join.sql (reference line 147)

/label ~BUG

[FlyingMao]

verified version: 5.6.29-dble-9.9.9.9-475ffa1-20181121114114