there is a redundant "order by" step in some case for query explain #1060
Description
-
dble version：
5.6.29-dble-9.9.9.9-6d9aec3cbe94d2e217b42646dff266ecb7e5dfdb-20190318061530 -
preconditions ：
mysql> desc company ;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| address | varchar(250) | NO | | NULL | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| owner | varchar(20) | NO | | NULL | |
| employees | int(11) | NO | | 50 | |
+-------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.01 sec)
mysql> desc hotnews;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| title | varchar(50) | NO | | NULL | |
| create_time | datetime | NO | | NULL | |
| author | varchar(20) | YES | | NULL | |
| clicks | int(11) | NO | | 0 | |
| content | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> desc customer;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| birth_date | date | YES | | NULL | |
| birth_day | time | YES | | NULL | |
| height | double | YES | | NULL | |
| weight | double | YES | | NULL | |
| contact | varchar(50) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
| hobby | varchar(250) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
- configs：
schema.xml
<schema name="testdb">
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3,dn4"/>
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4"
rule="sharding-by-mod"/>
<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-mod2">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id"/>
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id"/>
</table>
</schema>
rule.xml
<tableRule name="sharding-by-mod">
<rule>
<columns>id</columns>
<algorithm>hashmod</algorithm>
</rule>
</tableRule>
</tableRule>
<tableRule name="sharding-by-mod2">
<rule>
<columns>id</columns>
<algorithm>hashmod2</algorithm>
</rule>
</tableRule>
<function name="hashmod" class="Hash">
<property name="partitionCount">4</property>
<property name="partitionLength">1</property>
</function>
<function name="hashmod2" class="Hash">
<property name="partitionCount">2</property>
<property name="partitionLength">1</property>
</function>
- steps：
1.explain in dble
mysql> explain select b.owner,c.address, a.author, a.content from customer c, (select author, content from hotnews where clicks>1000) a, company b where c.address = b.address and c.name = a.author;
expect reslult:
1.step marked by red should not exist in explain result
real result:
step marked by red exists in explain result