Merge with hint

[cloudfirst]

I. What Changed: when route with hint, there are two sql statement: one is hint sql, the other is real sql. first, get the route from hint sql, and save it into current session instance. second, get the route from real sql, and check if real sql is complex query, then run executeMultiSelect(), and filter the real SQL's RRS nodes with hint RRS node before buildMergeHandler() is called if real sql is simple query, then run executeOther()

II. Why this change was make When quering with hint, the default logic of dble will run query on hint-routed nodes and simply concatenate the result from each node, which is not acceptable for my customer. the requirement is that hint-routed complex query also need to be merged.

[cloudfirst]

limit not implemented mysql> /!dble:sql=select 1 from sid_asjc_re where sid=8001/ select eid, year from author_affiliation where name = "Bailly, C." and year between 1999 and 2010 order by year limit 10; +-------------+------+ | eid | year | +-------------+------+ | 34602772 | 2000 | | 38949176358 | 2008 | | 58149215920 | 2009 | +-------------+------+ 3 rows in set (30.30 sec)

mysql> /!dble:sql=select 1 from sid_asjc_re where sid=8012/ select eid, year from author_affiliation where name = "Bailly, C." and year between 1999 and 2010 order by year limit 10; +-------------+------+ | eid | year | +-------------+------+ | 34298842 | 2000 | | 34602772 | 2000 | | 31344433984 | 2006 | | 31344433984 | 2006 | +-------------+------+ 4 rows in set (30.96 sec)

mysql> /!dble:sql=select 1 from sid_asjc_re where sid in (8001, 8012)/ select eid, year from author_affiliation where name = "Bailly, C." and year between 1999 and 2010 order by year limit 10; +-------------+------+ | eid | year | +-------------+------+ | 34298842 | 2000 | | 34602772 | 2000 | | 34602772 | 2000 | | 31344433984 | 2006 | | 31344433984 | 2006 | | 38949176358 | 2008 | | 58149215920 | 2009 | +-------------+------+ 7 rows in set (30.21 sec)

mysql> /!dble:sql=select 1 from sid_asjc_re where sid in (8001, 8012)/ select eid, year from author_affiliation where name = "Bailly, C." and year between 1999 and 2010 order by year limit 6; +-------------+------+ | eid | year | +-------------+------+ | 34298842 | 2000 | | 34602772 | 2000 | | 34602772 | 2000 | | 31344433984 | 2006 | | 31344433984 | 2006 | | 38949176358 | 2008 | +-------------+------+ 6 rows in set (31.44 sec)

这是limit查询的结果。 结果显示limit起作用了。是否有什么我没有考虑到的情况？

[LUAgam]

@cloudfirst try to remove order by, only limit

[cloudfirst]

after testing ,there was still no real merge of the result set.

can you help to show the test result ?

[wenyh1]

can you help to show the test result ?

test result: MySQL [schema1]> /!dble:sql=select 1 from testDD/ select * from testDD order by id limit 1; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.01 sec)

MySQL [schema1]> /!dble:sql=select 1 from testDD/ select * from testDD limit 1; +------+ | id | +------+ | 3 | | 2 | +------+ 2 rows in set (0.02 sec)

[cloudfirst]

limit issue is fixed.

mysql> /!dble:sql=select 1 from sid_asjc_re where sid in (8001,8012)/ select eid, year from author_affiliation where name = "Bailly, C." and year between 1999 and 2010 limit 1; +-------------+------+ | eid | year | +-------------+------+ | 31344433984 | 2006 | +-------------+------+ 1 row in set (0.43 sec)

[wenyh1]

But, there are new problem outputs: hang while executing (looks like executeMultiSelectEx() caused the problem), for example： #insert sql insert into testDD values(8); #sql that specifies the library name update schema1.sharding_4_t1 set name = '66'; delete from schema1.sharding_4_t1;

[cloudfirst]

well, I will try to verify the problem of insert and update. by the way, can you help to share the regression test cases before a new feature is introduced ?

But, there are new problem outputs: hang while executing (looks like executeMultiSelectEx() caused the problem), for example： #insert sql insert into testDD values(8); #sql that specifies the library name update schema1.sharding_4_t1 set name = '66'; delete from schema1.sharding_4_t1;

[wenyh1]

well, I will try to verify the problem of insert and update. by the way, can you help to share the regression test cases before a new feature is introduced ?

I found the problem by accident when I inserted the data, you can try executing a few more sql tests on your own and see if you can spot other problems.

[cloudfirst]

well, I will try to verify the problem of insert and update. by the way, can you help to share the regression test cases before a new feature is introduced ?

I found the problem by accident when I inserted the data, you can try executing a few more sql tests on your own and see if you can spot other problems.

Now executeMultiSelectEx() only work for SELECT query with hint, others will go to executeOthers().

[CLAassistant]

Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our Contributor License Agreement before we can accept your contribution.

---

luhya seems not to be a GitHub user. You need a GitHub account to be able to sign the CLA. If you have already a GitHub account, please add the email address used for this commit to your account.
_{You have signed the CLA already but the status is still pending? Let us recheck it.}