[I] Support IN query batch split rewrite [shardingsphere]

["wy1433 (via GitHub)" <gi...@apache.org>]

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

   ## Feature Request
   ### Is your feature request related to a problem?
   
      Yes. Currently, ShardingSphere handles INSERT statements with batch value splitting - each value in an INSERT INTO ... VALUES clause is individually routed and then merged based on target data nodes.
      However, SELECT queries with IN expressions (e.g., `SELECT * FROM t_order WHERE order_id IN (1, 2, 3)`) are not handled the same way.
   
      When executing a SELECT query with IN expression on a sharding key, if different values route to different shards, the current implementation sends all IN values to all matched shards, resulting in
      unnecessary data transfer and processing.
   
      ### Describe the feature you would like.
   
      Implement IN query batch split rewrite similar to INSERT VALUES handling:
   
      1. Parse IN expression values and route each value individually
      2. Track which values route to which data nodes (like `originalDataNodes` for INSERT)
      3. During SQL rewrite, filter IN values per route unit to only include values that route to that specific shard
   
      **Key Components:**
      - **InValueContext**: Store IN expression structural information (similar to InsertValueContext)
      - **ShardingInValuesToken**: Token for rewriting IN values based on route unit
      - **ShardingInValuesTokenGenerator**: Generate tokens for IN expressions
      - **ShardingStandardRouteEngine**: Add logic to split IN query conditions
   
      **Expected Behavior:**
   
      Before (current):
      - All shards receive: `SELECT * FROM t_order WHERE order_id IN (1, 2, 3)`
   
      After (proposed):
      - ds_0 receives: `SELECT * FROM t_order_0 WHERE order_id IN (1)`
      - ds_1 receives: `SELECT * FROM t_order_1 WHERE order_id IN (2, 3)`
   
      **Benefits:**
      1. Reduced data transfer between shards
      2. More efficient query execution per shard
      3. Consistent behavior between INSERT and SELECT sharding optimizations"
   


-- 
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

Re: [I] Support IN query batch split rewrite [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu closed issue #37297: Support IN query batch split rewrite
URL: https://github.com/apache/shardingsphere/issues/37297


-- 
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

Re: [I] Support IN query batch split rewrite [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #37297:
URL: https://github.com/apache/shardingsphere/issues/37297#issuecomment-3830068800

   Since this issue is relatively complex and requires more detailed design, and it is not a bug, I have closed it for now.


-- 
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