global table's query plan wrong after optimize global query plan

[irene-coming]

• dble version：
  5.6.29-dble-9.9.9.9-60c3ca8-20180912064213

• preconditions ：
  devlopers optimized global query related plan at version 2.18.09.1, at version 2.18.09.0 it works well.

• configs：

schema.xml

<table name="global_table1" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4" />
<table name="global_table2" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4" />
<table name="global_table3" primaryKey="id" type="global" dataNode="dn1,dn2" />

• steps：
  step1. execute sqls as following:

drop table if exists test_global;
drop table if exists global_table3;
drop table if exists global_table2;
CREATE TABLE test_global(`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 global_table3(`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 global_table2(`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 test_global 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 global_table3 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 global_table2 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. the following sql plan is error:

mysql> explain select count(*) from (select * from test_global where pad=(select pad from global_table3 where id=1)) a;
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | TYPE     | SQL/REF                                                                                                                                                                        |
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1_0     | BASE SQL | select COUNT(*) from (select `test_global`.`id`,`test_global`.`t_id`,`test_global`.`name`,`test_global`.`pad`,`test_global`.`_dble_op_time` from  `test_global` where 1 = 0) a |
| merge_1   | MERGE    | dn1_0                                                                                                                                                                          |
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

• expect result：

  1. query plan: "where 1=0" should be equivalent of "pad=(select pad from global_table3 where id=1)"

• real result：

  1. where 1=0

• supplements：
  1.founds from autotest sql:
  sqls/select/subquery_global.sql, line 51
  sqls/select/subquery_global_noshard.sql, line 51

/label ~BUG

[yexiaoli88]

verified on version: 5.6.29-dble-9.9.9.9-bfb1693-20180918015753

MySQL [testdb]> explain select count(*) from (select * from test_global where pad=(select pad from global_table3 where id=1)) a;
+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | TYPE     | SQL/REF                                                                                                                                                                                                                                                          |
+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1_0     | BASE SQL | select COUNT(*) from (select `test_global`.`id`,`test_global`.`t_id`,`test_global`.`name`,`test_global`.`pad` from  `test_global` where `test_global`.`pad`=(select `global_table3`.`pad` as `autoalias_scalar` from  `global_table3` where id = 1 limit 0,2)) a |
| merge_1   | MERGE    | dn1_0                                                                                                                                                                                                                                                            |
+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.14 sec)