You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/10 06:31:35 UTC

[GitHub] [shardingsphere] lwk595ww opened a new issue, #21114: SQL subqueries result in fully fragmented lookups and Cartesian products

lwk595ww opened a new issue, #21114:
URL: https://github.com/apache/shardingsphere/issues/21114

   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
   
   ![image](https://user-images.githubusercontent.com/64793990/191462563-54b51686-16aa-4a60-86e6-ad60ed56e0a6.png)
   
   ![image](https://user-images.githubusercontent.com/64793990/191463377-4b6dd515-898e-42bf-ad67-da7a4133ce95.png)
   
   ![image](https://user-images.githubusercontent.com/64793990/191463417-e6bdd961-a3e2-48a6-9514-ba47a62ca148.png)
   
   ![image](https://user-images.githubusercontent.com/64793990/191463580-1566c5e9-757a-48e3-81aa-7a53b298ad8c.png)
   
   ![image](https://user-images.githubusercontent.com/64793990/191463813-6b8b9eab-0430-49a9-b297-93eec80573b9.png)
   
   
   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.
   
   ![image](https://user-images.githubusercontent.com/64793990/191464449-75feed1f-c67d-4af2-850e-85561a28c8c0.png)
   
   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
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] closed issue #21114: SQL subqueries result in fully fragmented lookups and Cartesian products

Posted by GitBox <gi...@apache.org>.

github-actions[bot] closed issue #21114: SQL subqueries result in fully fragmented lookups and Cartesian products
URL: https://github.com/apache/shardingsphere/issues/21114


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] lwk595ww commented on issue #21114: SQL subqueries result in fully fragmented lookups and Cartesian products

Posted by GitBox <gi...@apache.org>.

lwk595ww commented on issue #21114:
URL: https://github.com/apache/shardingsphere/issues/21114#issuecomment-1255747981

   ![image](https://user-images.githubusercontent.com/64793990/191883416-a61f4c82-e5e2-4ffa-9a96-ed83f8cc4bca.png)
   
   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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] commented on issue #21114: SQL subqueries result in fully fragmented lookups and Cartesian products

Posted by GitBox <gi...@apache.org>.

github-actions[bot] commented on issue #21114:
URL: https://github.com/apache/shardingsphere/issues/21114#issuecomment-1272348320

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org