When useJoinStrategy, join with limit and use nest loop in fact, the Explain result lost rows

[yanhuqing666]

• dble version： 2.18.09.0-release/all
• 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

<property name="useJoinStrategy">true</property>

• steps：
  step1. mysql> explain select * from sharding_two_node a left join sharding_two_node2 b on a.c_flag=b.c_flag where a.c_decimal >5.0 limit 1;
• expect 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` where a.c_decimal > 5.0 ORDER BY `a`.`c_flag` ASC                                                       |
| dn2_0           | BASE SQL              | select `a`.`id`,`a`.`c_flag`,`a`.`c_decimal` from  `sharding_two_node` `a` where a.c_decimal > 5.0 ORDER BY `a`.`c_flag` ASC                                                       |
| merge_1         | MERGE                 | dn1_0; dn2_0                                                                                                                                                                       |
| shuffle_field_1 | SHUFFLE_FIELD         | merge_1                                                                                                                                                                            |
| nest_loop_1     | NEST_LOOP             | shuffle_field_1                                                                                                                                                                    |
| dn1_1           | BASE SQL(May No Need) | nest_loop_1's RESULTS; select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal`,`b`.`OUTFILE` from  `sharding_two_node2` `b` where b.c_flag IN ('{NEED_TO_REPLACE}') ORDER BY `b`.`c_flag` ASC |
| dn2_1           | BASE SQL(May No Need) | nest_loop_1's RESULTS; select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal`,`b`.`OUTFILE` from  `sharding_two_node2` `b` where b.c_flag IN ('{NEED_TO_REPLACE}') ORDER BY `b`.`c_flag` ASC |
| merge_2         | MERGE                 | dn1_1; dn2_1                                                                                                                                                                       |
| shuffle_field_2 | SHUFFLE_FIELD         | merge_2                                                                                                                                                                            |
| join_1          | JOIN                  | nest_loop_1; shuffle_field_2                                                                                                                                                       |
| limit_1         | LIMIT                 | join_1                                                                                                                                                                             |
| shuffle_field_3 | SHUFFLE_FIELD         | limit_1                                                                                                                                                                            |
+-----------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.29 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` where a.c_decimal > 5.0 ORDER BY `a`.`c_flag` ASC                                                       |
| dn2_0           | BASE SQL              | select `a`.`id`,`a`.`c_flag`,`a`.`c_decimal` from  `sharding_two_node` `a` where a.c_decimal > 5.0 ORDER BY `a`.`c_flag` ASC                                                       |
| merge_1         | MERGE                 | dn1_0; dn2_0                                                                                                                                                                       |
| shuffle_field_1 | SHUFFLE_FIELD         | merge_1                                                                                                                                                                            |
| nest_loop_1     | NEST_LOOP             | shuffle_field_1                                                                                                                                                                    |
| dn1_1           | BASE SQL(May No Need) | nest_loop_1's RESULTS; select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal`,`b`.`OUTFILE` from  `sharding_two_node2` `b` where b.c_flag IN ('{NEED_TO_REPLACE}') ORDER BY `b`.`c_flag` ASC |
| dn2_1           | BASE SQL(May No Need) | nest_loop_1's RESULTS; select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal`,`b`.`OUTFILE` from  `sharding_two_node2` `b` where b.c_flag IN ('{NEED_TO_REPLACE}') ORDER BY `b`.`c_flag` ASC |
| merge_2         | MERGE                 | dn1_1; dn2_1                                                                                                                                                                       |
| shuffle_field_2 | SHUFFLE_FIELD         | merge_2                                                                                                                                                                            |
| join_1          | JOIN                  | nest_loop_1; shuffle_field_2                                                                                                                                                       |
+-----------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.39 sec)

• supplements：
  1.

/label ~BUG

[irene-coming]

verified on dble version: 5.6.29-dble-9.9.9.9-3058b53-20181023033515
append to autotest plan 11