[GitHub] [shardingsphere] WoodZzzzz opened a new issue #9845: Binding tables doesn't work when using 'join and in ()' condition

[GitBox <gi...@apache.org>]

WoodZzzzz opened a new issue #9845:
URL: https://github.com/apache/shardingsphere/issues/9845


   Here is dependency
   <dependency>
   	<groupId>org.apache.shardingsphere</groupId>
   	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   	<version>4.1.0</version>
   </dependency>
   
   Here is the output of my configuration
   bindingTables:
   - t_order
   - t_order_item
   tables:
     t_order:
       actualDataNodes: ds0.t_order_${0..1}
       keyGenerator:
         column: order_id
         type: SNOWFLAKE
       logicTable: t_order
       tableStrategy:
         inline:
           algorithmExpression: t_order_${order_id % 2}
           shardingColumn: order_id
     t_order_item:
       actualDataNodes: ds0.t_order_item_${0..1}
       logicTable: t_order_item
       tableStrategy:
         inline:
           algorithmExpression: t_order_item_${order_id % 2}
           shardingColumn: order_id
   
   Here is part of log:
   result:
          Logic SQL: SELECT
           o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
           FROM t_order o
           JOIN t_order_item i
           on o.order_id = i.order_id
           where o.order_id in (?)
         Actual SQL: ds0 ::: SELECT
           o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
           FROM t_order_1 o
           JOIN t_order_item_1 i
           on o.order_id = i.order_id
           where o.order_id in (?) ::: [1]
   another result when I use 'and' instead of 'where'：
        Logic SQL: SELECT
               o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
               FROM t_order o
               JOIN t_order_item i
               on o.order_id = i.order_id
               and o.order_id in (?)
       Actual SQL: ds0 ::: SELECT
               o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
               FROM t_order_0 o
               JOIN t_order_item_1 i
               on o.order_id = i.order_id
               and o.order_id in (?) ::: [1]
       Actual SQL: ds0 ::: SELECT
               o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
               FROM t_order_0 o
               JOIN t_order_item_0 i
               on o.order_id = i.order_id
               and o.order_id in (?) ::: [1]
       Actual SQL: ds0 ::: SELECT
               o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
               FROM t_order_1 o
               JOIN t_order_item_1 i
               on o.order_id = i.order_id
               and o.order_id in (?) ::: [1]
       Actual SQL: ds0 ::: SELECT
               o.order_id, o.user_id, o.order_name, i.item_name, i.price, i.quantity
               FROM t_order_1 o
               JOIN t_order_item_0 i
               on o.order_id = i.order_id
               and o.order_id in (?) ::: [1]
          
   As you can see, different results happened
   
   I tried to debug, I found the name of  'org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator#getShardingConditions' . line 82.
   Maybe that's the problem when I using 'and'.  WhereClauseShardingConditionEngine is the only implementation for Sharding-JDBC. 
   Is there any necessary to add a class to support 'and in' condition?
   
   


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

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

[GitHub] [shardingsphere] huanghao495430759 commented on issue #9845: Binding tables doesn't work when using 'join and in ()' condition

[GitBox <gi...@apache.org>]

huanghao495430759 commented on issue #9845:
URL: https://github.com/apache/shardingsphere/issues/9845#issuecomment-812501857


   I think the problem is in `WhereClauseShardingConditionEngine.createShardingConditions` method, because when `SelectStatement.getFrom()` is `JoinTableSegment`, and `JoinTableSegment.getCondition()` also is a type of LogicSQL `WhereSegment`, so we should parse 
   it to `WhereSegment`  and add into ShardingConditions Result.


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

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

[GitHub] [shardingsphere] tristaZero closed issue #9845: Binding tables doesn't work when using 'join and in ()' condition

[GitBox <gi...@apache.org>]

tristaZero closed issue #9845:
URL: https://github.com/apache/shardingsphere/issues/9845


   


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

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

[GitHub] [shardingsphere] huanghao495430759 commented on issue #9845: Binding tables doesn't work when using 'join and in ()' condition

[GitBox <gi...@apache.org>]

huanghao495430759 commented on issue #9845:
URL: https://github.com/apache/shardingsphere/issues/9845#issuecomment-812486921


   this is a bug


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

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