Derived subquery should be optimized if it is an ER/Global JOIN

[yanhuqing666]

• **dble version：2.18.09.0-release **
• preconditions ：
  CREATE TABLE sharding_two_node (
  id int(11) NOT NULL,
  c_flag char(255) DEFAULT NULL,
  c_decimal decimal(16,4) DEFAULT NULL,
  PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE sharding_two_node2 (
id int(11) NOT NULL,
c_flag char(255) DEFAULT NULL,
c_decimal decimal(16,4) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• configs：

schema.xml

<table name="sharding_two_node" dataNode="dn1,dn2" rule="two_node_hash"/>
<table name="sharding_two_node2" dataNode="dn1,dn2" rule="two_node_hash"/>

rule.xml

<tableRule name="two_node_hash">
		<rule>
			<columns>id</columns>
			<algorithm>two_node_hash</algorithm>
		</rule>
	</tableRule>
<function name="two_node_hash" class="Hash">
		<property name="partitionCount">2</property>
		<property name="partitionLength">512</property>
	</function>

server.xml

...

• steps：
  step1.

explain select count(*) from ( select * from sharding_two_node a, sharding_two_node2 b on a.id =b.id) x;

• expect result：
  1.

+----------------------------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE                  | TYPE                     | SQL/REF                                                                                                                                                                   |
+----------------------------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1_0                      | BASE SQL                 | select `a`.`id`,`a`.`c_flag`,`a`.`c_decimal`,`b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_two_node` `a` join  `sharding_two_node2` `b` on a.id = b.id where 1=1  |
| dn2_0                      | BASE SQL                 | select `a`.`id`,`a`.`c_flag`,`a`.`c_decimal`,`b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_two_node` `a` join  `sharding_two_node2` `b` on a.id = b.id where 1=1  |
| merge_1                    | MERGE                    | dn1_0; dn2_0                                                                                                                                                              |
| shuffle_field_1            | SHUFFLE_FIELD            | merge_1                                                                                                                                                                   |
| rename_derived_sub_query_1 | RENAME_DERIVED_SUB_QUERY | shuffle_field_1                                                                                                                                                           |
| ordered_group_1            | ORDERED_GROUP            | rename_derived_sub_query_1                                                                                                                                                |
| shuffle_field_2            | SHUFFLE_FIELD            | ordered_group_1                                                                                                                                                           |
+----------------------------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.35 sec)

• real result：
  1.

+----------------------------+--------------------------+---------------------------------------------------------------------------------------------------+
| DATA_NODE                  | TYPE                     | SQL/REF                                                                                           |
+----------------------------+--------------------------+---------------------------------------------------------------------------------------------------+
| dn1_0                      | BASE SQL                 | select `a`.`id`,`a`.`c_flag`,`a`.`c_decimal` from  `sharding_two_node` `a` ORDER BY `a`.`id` ASC  |
| dn2_0                      | BASE SQL                 | select `a`.`id`,`a`.`c_flag`,`a`.`c_decimal` from  `sharding_two_node` `a` ORDER BY `a`.`id` ASC  |
| merge_1                    | MERGE                    | dn1_0; dn2_0                                                                                      |
| shuffle_field_1            | SHUFFLE_FIELD            | merge_1                                                                                           |
| dn1_1                      | BASE SQL                 | select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_two_node2` `b` ORDER BY `b`.`id` ASC |
| dn2_1                      | BASE SQL                 | select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_two_node2` `b` ORDER BY `b`.`id` ASC |
| merge_2                    | MERGE                    | dn1_1; dn2_1                                                                                      |
| shuffle_field_4            | SHUFFLE_FIELD            | merge_2                                                                                           |
| join_1                     | JOIN                     | shuffle_field_1; shuffle_field_4                                                                  |
| shuffle_field_2            | SHUFFLE_FIELD            | join_1                                                                                            |
| rename_derived_sub_query_1 | RENAME_DERIVED_SUB_QUERY | shuffle_field_2                                                                                   |
| ordered_group_1            | ORDERED_GROUP            | rename_derived_sub_query_1                                                                        |
| shuffle_field_3            | SHUFFLE_FIELD            | ordered_group_1                                                                                   |
+----------------------------+--------------------------+---------------------------------------------------------------------------------------------------+

• supplements：
  1.

/label ~BUG

[yanhuqing666]

fix with f1f0547

[yanhuqing666]

#419 #463 #624 #463

[irene-coming]

verified on version: 5.6.29-dble-2.18.09.1-2b298cd-20180904021034