Error when ` select * from sharding_2_t1 a right join sharding_4_t1 b on a.id=b.ID where a.id >0 and a.id <4`

[FlyingMao]

• dble version：
  dble-9.9.9.9-5b7c15e6b61ad4f2f50423f1bedbb2b66ffda2f8-20190423030945
• preconditions ：

drop table if exists sharding_4_t1;
CREATE TABLE sharding_4_t1(ID INT NOT NULL,FirstName VARCHAR(20),LastName VARCHAR(20),Department VARCHAR(20),Salary INT);
INSERT INTO sharding_4_t1 VALUES(1,'Mazojys','Fxoj','Finance',7800),(2,'Jozzh','Lnanyo','Finance',45800),(3,'Syllauu','Dfaafk','Finance',57000),(4,'Gecrrcc','Srlkrt','Finance',62000),(5,'Jssme','Bdnaa','Development',75000),(6,'Dnnaao','Errllov','Development',55000),(7,'Tyoysww','Osk','Development',49000);
drop table if exists sharding_2_t1;
CREATE TABLE `sharding_2_t1`(`id` int(11) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,age int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into sharding_2_t1(id,name,age) values(1,'sam',20),(2,'AA',50),(3,'BB',60),(4,'CC',135),(5,'DD',25),(6,'FF',17),(7,'EE',15);

• configs：

schema.xml

<table name="sharding_4_t1" dataNode="dn1,dn2,dn3,dn4" rule="hash-four" />
<table name="sharding_2_t1" primaryKey="id" dataNode="dn1,dn2" rule="hash-two" />

rule.xml

server.xml

• steps：
  step1.

select * from sharding_2_t1 a right join sharding_4_t1 b on a.id=b.ID where a.id >0 and a.id <4;

• expect result：
  1.

mysql> select * from sharding_2_t1 a right join sharding_4_t1 b on a.id=b.ID where a.id >0 and a.id <4;
+------+------+------+----+-----------+----------+------------+--------+
| id   | name | age  | ID | FirstName | LastName | Department | Salary |
+------+------+------+----+-----------+----------+------------+--------+
|    1 | sam  |   20 |  1 | Mazojys   | Fxoj     | Finance    |   7800 |
|    2 | AA   |   50 |  2 | Jozzh     | Lnanyo   | Finance    |  45800 |
|    3 | BB   |   60 |  3 | Syllauu   | Dfaafk   | Finance    |  57000 |
+------+------+------+----+-----------+----------+------------+--------+
3 rows in set (0.01 sec)

• real result：
  1.

mysql> select * from sharding_2_t1 a right join sharding_4_t1 b on a.id=b.ID where a.id >0 and a.id <4;
ERROR 1003 (HY000): field not found:a.id > 0

• supplements：
  1.
  explain result：

mysql> explain select * from sharding_2_t1 a right join sharding_4_t1 b on a.id=b.ID where a.id >0 and a.id <4;
+-----------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE       | TYPE          | SQL/REF                                                                                                                                                              |
+-----------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1_0           | BASE SQL      | select `b`.`ID`,`b`.`FirstName`,`b`.`LastName`,`b`.`Department`,`b`.`Salary` from  `sharding_4_t1` `b` where (`b`.`ID` > 0) AND (`b`.`ID` < 4) ORDER BY `b`.`ID` ASC |
| dn2_0           | BASE SQL      | select `b`.`ID`,`b`.`FirstName`,`b`.`LastName`,`b`.`Department`,`b`.`Salary` from  `sharding_4_t1` `b` where (`b`.`ID` > 0) AND (`b`.`ID` < 4) ORDER BY `b`.`ID` ASC |
| dn3_0           | BASE SQL      | select `b`.`ID`,`b`.`FirstName`,`b`.`LastName`,`b`.`Department`,`b`.`Salary` from  `sharding_4_t1` `b` where (`b`.`ID` > 0) AND (`b`.`ID` < 4) ORDER BY `b`.`ID` ASC |
| dn4_0           | BASE SQL      | select `b`.`ID`,`b`.`FirstName`,`b`.`LastName`,`b`.`Department`,`b`.`Salary` from  `sharding_4_t1` `b` where (`b`.`ID` > 0) AND (`b`.`ID` < 4) ORDER BY `b`.`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 `a`.`id` from  `sharding_2_t1` `a` where (`a`.`id` > 0) AND (`a`.`id` < 4) ORDER BY `a`.`id` ASC                                                              |
| dn2_1           | BASE SQL      | select `a`.`id` from  `sharding_2_t1` `a` where (`a`.`id` > 0) AND (`a`.`id` < 4) ORDER BY `a`.`id` ASC                                                              |
| merge_2         | MERGE         | dn1_1; dn2_1                                                                                                                                                         |
| shuffle_field_3 | SHUFFLE_FIELD | merge_2                                                                                                                                                              |
| join_1          | JOIN          | shuffle_field_1; shuffle_field_3                                                                                                                                     |
| where_filter_1  | WHERE_FILTER  | join_1                                                                                                                                                               |
| shuffle_field_2 | SHUFFLE_FIELD | where_filter_1                                                                                                                                                       |
+-----------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.01 sec)

dble.log

2019-04-23 06:26:28.090  INFO [complexQueryExecutor12] (com.actiontech.dble.backend.mysql.nio.MySQLConnectionHandler.handleDataError(MySQLConnectionHandler.java:237)) - MySQLConnection [id=11, lastTime=1556000788085, user=test, schema=db1, old schema=db1, borrowed=true, fromSlaveDB=false, threadId=21009,character_set_client=utf8,character_set_results=utf8,collation_connection=utf8_general_ci, txIsolation=3, autocommit=true, attachment=dn1{select `a`.`id` from  `sharding_2_t1` `a` where (`a`.`id` > 0) AND (`a`.`id` < 4) ORDER BY `a`.`id` ASC}.1, respHandler=com.actiontech.dble.backend.mysql.nio.handler.query.impl.BaseSelectHandler@96b53fe, host=10.186.65.64, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] handle data error:
com.actiontech.dble.plan.common.exception.MySQLOutPutException: field not found:a.id > 0
	at com.actiontech.dble.backend.mysql.nio.handler.util.HandlerTool.createFieldItem(HandlerTool.java:266) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.util.HandlerTool.createItem(HandlerTool.java:98) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.util.HandlerTool.createFunctionItem(HandlerTool.java:217) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.util.HandlerTool.createItem(HandlerTool.java:96) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.query.impl.WhereHandler.fieldEofResponse(WhereHandler.java:46) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.query.impl.join.JoinHandler.fieldEofResponse(JoinHandler.java:106) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.query.impl.SendMakeHandler.fieldEofResponse(SendMakeHandler.java:85) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.query.impl.MultiNodeMergeHandler.fieldEofResponse(MultiNodeMergeHandler.java:109) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.handler.query.impl.BaseSelectHandler.fieldEofResponse(BaseSelectHandler.java:109) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.MySQLConnectionHandler.handleFieldEofPacket(MySQLConnectionHandler.java:193) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.backend.mysql.nio.MySQLConnectionHandler.handleData(MySQLConnectionHandler.java:115) [dble-9.9.9.9.jar:?]
	at com.actiontech.dble.net.handler.BackendAsyncHandler.handleInnerData(BackendAsyncHandler.java:102) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.net.handler.BackendAsyncHandler.access$000(BackendAsyncHandler.java:19) ~[dble-9.9.9.9.jar:?]
	at com.actiontech.dble.net.handler.BackendAsyncHandler$1.run(BackendAsyncHandler.java:51) [dble-9.9.9.9.jar:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_131]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_131]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]

/label ~BUG

[FlyingMao]

for tester: sqls/sqls_mixed/select/join_different_rules_shardings.sql(line:27)

[yanhuqing666]

import from #1120 #1121

[FlyingMao]

There is still a problem with in(...) after where
sql: select * from sharding_2_t1 a left join sharding_4_t1 b on a.id=b.ID where b.Department in ('Finance','Development') and b.Salary >500 and b.Salary<70000 order by a.id;

except result:

mysql> select * from sharding_2_t1 a left join sharding_4_t1 b on a.id=b.ID where b.Department in ('Finance','Development') and b.Salary >500 and b.Salary<70000 order by a.id;
+------+------+------+------+-----------+----------+-------------+--------+
| id   | name | age  | ID   | FirstName | LastName | Department  | Salary |
+------+------+------+------+-----------+----------+-------------+--------+
|    1 | sam  |   20 |    1 | Mazojys   | Fxoj     | Finance     |   7800 |
|    2 | AA   |   50 |    2 | Jozzh     | Lnanyo   | Finance     |  45800 |
|    3 | BB   |   60 |    3 | Syllauu   | Dfaafk   | Finance     |  57000 |
|    4 | CC   |  135 |    4 | Gecrrcc   | Srlkrt   | Finance     |  62000 |
|    6 | FF   |   17 |    6 | Dnnaao    | Errllov  | Development |  55000 |
|    7 | EE   |   15 |    7 | Tyoysww   | Osk      | Development |  49000 |
+------+------+------+------+-----------+----------+-------------+--------+
6 rows in set (0.00 sec)

real result:

mysql> select * from sharding_2_t1 a left join sharding_4_t1 b on a.id=b.ID where b.Department in ('Finance','Development') and b.Salary >500 and b.Salary<70000 order by a.id;
+------+------+------+----+-----------+----------+------------+--------+
| id   | name | age  | ID | FirstName | LastName | Department | Salary |
+------+------+------+----+-----------+----------+------------+--------+
|    1 | sam  |   20 |  1 | Mazojys   | Fxoj     | Finance    |   7800 |
|    2 | AA   |   50 |  2 | Jozzh     | Lnanyo   | Finance    |  45800 |
|    3 | BB   |   60 |  3 | Syllauu   | Dfaafk   | Finance    |  57000 |
|    4 | CC   |  135 |  4 | Gecrrcc   | Srlkrt   | Finance    |  62000 |
+------+------+------+----+-----------+----------+------------+--------+
4 rows in set (0.03 sec)

for tester： sqls/sqls_mixed/select/join_different_rules_shardings.sql(line:171)