Open
Description
-
dble version：
5.6.29-dble-2.20.04.99-997ff15fc20aaea4b1a0b5625619764dd22613a2-20200512052042 -
preconditions ：
-
configs：
schema.xml
<table name="aly_test" dataNode="dn1,dn2,dn3,dn4" rule="hash-four" />
<table name="a_manager" cacheKey="id" dataNode="dn2,dn1,dn4,dn3" rule="hash-four" />
rule.xml
server.xml
- steps：
step1. explain in dble
mysql> explain select * from aly_test a join a_manager b on a.id = b.id where a.id =1 and b.id=1;
+-----------------+---------------+-------------------------------------------------------------------------------------------------------------+
| DATA_NODE | TYPE | SQL/REF |
+-----------------+---------------+-------------------------------------------------------------------------------------------------------------+
| dn2_0 | BASE SQL | select `a`.`id`,`a`.`c` from `aly_test` `a` where (`a`.`id` = 1) AND (`a`.`id` = 1) ORDER BY `a`.`id` ASC |
| merge_1 | MERGE | dn2_0 |
| shuffle_field_1 | SHUFFLE_FIELD | merge_1 |
| dn1_0 | BASE SQL | select `b`.`id`,`b`.`c` from `a_manager` `b` where (`b`.`id` = 1) AND (`b`.`id` = 1) ORDER BY `b`.`id` ASC |
| merge_2 | MERGE | dn1_0 |
| shuffle_field_3 | SHUFFLE_FIELD | merge_2 |
| join_1 | JOIN | shuffle_field_1; shuffle_field_3 |
| shuffle_field_2 | SHUFFLE_FIELD | join_1 |
+-----------------+---------------+-------------------------------------------------------------------------------------------------------------+
8 rows in set (0.02 sec)
- expect result：
1.the where condition may be optimized, maybe optimized as where (a.id= 1) , the current is:
where (a.id= 1) AND (a.id= 1)
where (b.id= 1) AND (b.id= 1) - real result：
1. - supplements：
1.related [Improve]Optimize Query Plan which can be sent to one datanode in fact. #752