SQL subqueries result in fully fragmented lookups and Cartesian products

[lwk595ww]

Condition: I am using compound sharding and I am using a custom sharding strategy.

The SQL statement: select t10.* from user t10 where t10.id in (select t11.user_id from school t11 where t11.id = 1571685907277340673 and t11.tenantId = 'caba6901') and t10.tenantId = 'caba6901'

Test conclusion: This SQL removes the custom policy, but returns two result sets in the source code. One is the table name returned by the custom sharding policy, and one is the table name returned by the full sharding, which results in a Cartesian product between the primary table and the child table

The above figure shows that under the same logical table, the conditions of two logical tables are cycled, causing the logical table cannot match the logical table name of the conditions, and the framework returns full sharding

The last figure shows that after the SQL is decomposed by the framework, each decomposed SQL matches all the sharding conditions, resulting in each logical table returning two sets of real tables, which, when merged, becomes a full sharding lookup.

Can you determine whether different conditions belong to different logical tables at this point in the source code, to avoid conditions that do not belong to this logical table also walk the logic, resulting in full fragmentation

[lwk595ww]

Modify the source code, the sub-query mixture condition isolation, respectively routing can solve the problem of nested sub-query both inside and outside layers have fragmentation and full routing, I hope the official can have a look, test, I hope the official can release a version

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.