[GitHub] [shardingsphere] iblilife commented on issue #8605: table for unconfigured "SELECT t.*, t2.col FROM t LEFT JOIN t2" not work

Wed, 16 Dec 2020 21:35:00 -0800 


iblilife commented on issue #8605:
URL: https://github.com/apache/shardingsphere/issues/8605#issuecomment-747216576


   
   @strongduanmu 
   # case 1, FAILED
   > `card_type` `shop_employee` don't need sharding。unconfigured in yaml file。
   
   mybatis mapper.xml
   ```xml
     <select id="fetchCardTypeList" resultType="java.util.Map">
       SELECT
         ct.*,
         cemp.name as createEmpName
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = #{shopId}
   
       <!--
        // RUN FAILED
        SELECT
         ct.id,
         cemp.*
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = #{shopId}
        -->
     </select>
   ```
   console log
   ```text
   [13:28:49:830] [INFO] - 
org.apache.shardingsphere.infra.executor.sql.log.SQLLogger.log(SQLLogger.java:74)
 - Logic SQL: SELECT
         ct.*,
         cemp.name as createEmpName
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = ? LIMIT ? 
   [13:28:49:830] [INFO] - 
org.apache.shardingsphere.infra.executor.sql.log.SQLLogger.log(SQLLogger.java:74)
 - SQLStatement: 
MySQLSelectStatement(limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@5e8f0b00],
 lock=Optional.empty)
   [13:28:49:830] [INFO] - 
org.apache.shardingsphere.infra.executor.sql.log.SQLLogger.log(SQLLogger.java:74)
 - Actual SQL: princess ::: SELECT
         ct.*,
         cemp.name as createEmpName
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = ? LIMIT ?  ::: [498914881295097857, 10]
   [13:28:49:832] [DEBUG] - 
org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:143) - 
<==      Total: 7
   ```
   debug
   
![debug_img_1](https://jzray.oss-cn-shenzhen.aliyuncs.com/temp/debug_img_1.png)
   
   # case 2, OK
   mybatis mapper.xml
   ```xml
   <select id="fetchCardTypeList" resultType="java.util.Map">
       SELECT
         ct.*
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = #{shopId}
   </select>
   ```
   
![debug_img_2](https://jzray.oss-cn-shenzhen.aliyuncs.com/temp/debug_img_2.png)
   
   # case 3, OK
   mybatis mapper.xml
   ```xml
     <select id="fetchCardTypeList" resultType="java.util.Map">
       SELECT
         cemp.*
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = #{shopId}
      </select>
   ```
   
![debug_img_5](https://jzray.oss-cn-shenzhen.aliyuncs.com/temp/debug_img_5.png)
   
   # case 4, OK
   mybatis mapper.xml
   ```xml
     <select id="fetchCardTypeList" resultType="java.util.Map">
       SELECT
         ct.*,
         cemp.*
       FROM card_type ct
       left join shop_employee cemp on (cemp.id = ct.create_employee_id)
       where ct.shop_id = #{shopId}
      </select>
   ```


----------------------------------------------------------------
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:
[email protected]

Reply via email to