query in syntax like "select ... a .. join .. b .. on condition1 where ... (subquery)" fail

[irene-coming]

• dble version：
  5.6.29-dble-9.9.9.9-287a2f4-20180921052651

• preconditions ：
  no

• configs：

schema.xml

<table name="table_a" primaryKey="id" rule="hash-four" dataNode="dn1,dn2,dn3,dn4"></table>
<table name="table_b" primaryKey="id" rule="hash-four" dataNode="dn1,dn2,dn3,dn4"></table>
<table name="table_c" primaryKey="id" rule="hash-four" dataNode="dn1,dn2,dn3,dn4"></table>

rule.xml

    <tableRule name="hash-four">
        <rule>
            <columns>id</columns>
            <algorithm>four-long</algorithm>
        </rule>
    </tableRule> 
    <function class="Hash" name="four-long">
            <property name="partitionCount">4</property>
            <property name="partitionLength">1</property>
     </function>

server.xml

• steps：
  step1. login dble business port, create the configed sharding tables

CREATE TABLE `table_a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
);
CREATE TABLE `table_c` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
);
CREATE TABLE `table_c` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
);

step2. with business port, excute:

mysql> SELECT
    ->     *
    -> FROM
    ->     table_a
    -> LEFT JOIN table_b ON table_a.id = table_b. NAME
    -> where
    ->     table_a. NAME NOT IN (SELECT NAME FROM table_c);
ERROR 1003 (HY000): field not found:table_a.NAME NOT IN (SELECT NAME
	FROM table_c)

step3. with dble business port, execute:

mysql> explain SELECT
    ->     *
    -> FROM
    ->     table_a
    -> LEFT JOIN table_b ON table_a.id = table_b. NAME
    -> and
    ->     table_a. NAME NOT IN (SELECT NAME FROM table_c);
+-----------------+---------------+--------------------------------------------------------------------------------------------------+
| DATA_NODE       | TYPE          | SQL/REF                                                                                          |
+-----------------+---------------+--------------------------------------------------------------------------------------------------+
| dn1_0           | BASE SQL      | select `table_a`.`id`,`table_a`.`name` from  `table_a` ORDER BY `table_a`.`id` ASC               |
| dn2_0           | BASE SQL      | select `table_a`.`id`,`table_a`.`name` from  `table_a` ORDER BY `table_a`.`id` ASC               |
| dn3_0           | BASE SQL      | select `table_a`.`id`,`table_a`.`name` from  `table_a` ORDER BY `table_a`.`id` ASC               |
| dn4_0           | BASE SQL      | select `table_a`.`id`,`table_a`.`name` from  `table_a` ORDER BY `table_a`.`id` ASC               |
| merge_1         | MERGE         | dn1_0; dn2_0; dn3_0; dn4_0                                                                       |
| shuffle_field_1 | SHUFFLE_FIELD | merge_1                                                                                          |
| dn1_1           | BASE SQL      | select `table_b`.`id`,`table_b`.`name` from  `table_b` where 1 = 1 ORDER BY `table_b`.`name` ASC |
| dn2_1           | BASE SQL      | select `table_b`.`id`,`table_b`.`name` from  `table_b` where 1 = 1 ORDER BY `table_b`.`name` ASC |
| dn3_1           | BASE SQL      | select `table_b`.`id`,`table_b`.`name` from  `table_b` where 1 = 1 ORDER BY `table_b`.`name` ASC |
| dn4_1           | BASE SQL      | select `table_b`.`id`,`table_b`.`name` from  `table_b` where 1 = 1 ORDER BY `table_b`.`name` ASC |
| merge_2         | MERGE         | dn1_1; dn2_1; dn3_1; dn4_1                                                                       |
| shuffle_field_3 | SHUFFLE_FIELD | merge_2                                                                                          |
| join_1          | JOIN          | shuffle_field_1; shuffle_field_3                                                                 |
| shuffle_field_2 | SHUFFLE_FIELD | join_1                                                                                           |
+-----------------+---------------+--------------------------------------------------------------------------------------------------+
14 rows in set (0.01 sec)

• expect result：
  1. step2 query success
     2.step3 query plan contains part for "SELECT NAME FROM table_c" and take the constraint into join
• real result：
  1.step2 query failed
  2.step3 query plan is wrong, lack part for "SELECT NAME FROM table_c"
• supplements：
  1.

/label ~BUG

[yexiaoli88]

verified on version: 5.6.29-dble-9.9.9.9-21b7b82-20180925034029