query "select ..like..escape" get incorrect results #729
Description
dble version：5.6.29-dble-9.9.9.9-21b7b82-20180925034029
preconditions ：
create table test_shard (id int(11) primary key,R_bit bit(64),R_NAME varchar(50),R_COMMENT varchar(50));
insert into test_shard (id,R_bit,R_NAME,R_COMMENT) values (1,b'0001', 'a','test001'),(2,b'0010', 'a string','test002'),(3,b'0011', '1','test001'),(4,b'1010', '1','test001');
configs：
schema.xml
<table name="test_shard" dataNode="dn1,dn2,dn3,dn4" rule="mod-long"/>
rule.xml
<function name="hashmod" class="Hash">
<property name="partitionCount">4</property>
<property name="partitionLength">1</property>
</function>
- steps：
query in dble:
MySQL [testdb]> select * from test_shard where HEX(R_bit) like (select '%A%') escape (select '%');
+----+----------+----------+-----------+
| id | R_bit | R_NAME | R_COMMENT |
+----+----------+----------+-----------+
| 4 |
| 1 | test001 |
| 3 | | 1 | test001 |
| 1 | | a | test001 |
| 2 | | a string | test002 |
+----+----------+----------+-----------+
4 rows in set (0.01 sec)
MySQL [testdb]> select * from test_shard where HEX(R_bit) not like (select '%A%') escape (select '%');
Empty set (0.00 sec)
MySQL [testdb]> explain select * from test_shard where HEX(R_bit) not like (select '%A%') escape (select '%');
+--------------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | TYPE | SQL/REF |
+--------------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1_1 | BASE SQL | SELECT '%' |
| merge_1 | MERGE | dn1_1 |
| scalar_sub_query_1 | SCALAR_SUB_QUERY | merge_1 |
| dn1_0 | BASE SQL | SELECT '%' |
| merge_2 | MERGE | dn1_0 |
| scalar_sub_query_2 | SCALAR_SUB_QUERY | merge_2 |
| dn1_2 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) NOT LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| dn2_0 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) NOT LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| dn3_0 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) NOT LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| dn4_0 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) NOT LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| merge_3 | MERGE | dn1_2; dn2_0; dn3_0; dn4_0 |
| shuffle_field_1 | SHUFFLE_FIELD | merge_3 |
+--------------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
MySQL [testdb]> explain select * from test_shard where HEX(R_bit) like (select '%A%') escape (select '%');
+--------------------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | TYPE | SQL/REF |
+--------------------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1_1 | BASE SQL | SELECT '%' |
| merge_1 | MERGE | dn1_1 |
| scalar_sub_query_1 | SCALAR_SUB_QUERY | merge_1 |
| dn1_0 | BASE SQL | SELECT '%' |
| merge_2 | MERGE | dn1_0 |
| scalar_sub_query_2 | SCALAR_SUB_QUERY | merge_2 |
| dn1_2 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| dn2_0 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| dn3_0 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| dn4_0 | BASE SQL(May No Need) | scalar_sub_query_2; scalar_sub_query_1; select `test_shard`.`id`,`test_shard`.`R_bit`,`test_shard`.`R_NAME`,`test_shard`.`R_COMMENT` from `test_shard` where HEX(test_shard.R_bit) LIKE '{NEED_TO_REPLACE}' ESCAPE ( SELECT '%' ) |
| merge_3 | MERGE | dn1_2; dn2_0; dn3_0; dn4_0 |
| shuffle_field_1 | SHUFFLE_FIELD | merge_3 |
+--------------------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.01 sec)
query in mysql:
mysql> select * from test_shard where HEX(R_bit) like (select '%A%') escape (select '%');
+----+----------+--------+-----------+
| id | R_bit | R_NAME | R_COMMENT |
+----+----------+--------+-----------+
| 4 |
| 1 | test001 |
+----+----------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from test_shard where HEX(R_bit) not like (select '%A%') escape (select '%');
+----+----------+----------+-----------+
| id | R_bit | R_NAME | R_COMMENT |
+----+----------+----------+-----------+
| 1 | | a | test001 |
| 2 | | a string | test002 |
| 3 | | 1 | test001 |
+----+----------+----------+-----------+
3 rows in set (0.01 sec)
/label ~BUG