[GitHub] [shardingsphere] zhangwangren edited a comment on issue #13067: normal table and sharding table can't join the broadcast table at the same time

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

zhangwangren edited a comment on issue #13067:
URL: https://github.com/apache/shardingsphere/issues/13067#issuecomment-945407149


   At first when i only set the config like this:
   # the broadcast-tables
   spring.shardingsphere.rules.sharding.broadcast-tables=reservoir_flood_limit,reservoir_flood_prevention,station_basic_info_data,river_flood_prevention,dict_station_belong_reservoir,dict_point_station_relation
   
   # datasoruce sharding rule
   spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.type=CLASS_BASED
   spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.props.strategy=STANDARD
   spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.props.algorithmClassName=com.sky.dam.water.config.sharding.TimeDatabaseShardingAlgorithm
   
   # table sharding rule
   spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.type=CLASS_BASED
   spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.props.strategy=STANDARD
   spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.props.algorithmClassName=com.sky.dam.water.config.sharding.TimeTableShardingAlgorithm
   
   spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=TM
   spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=db-class-based
   
   spring.shardingsphere.rules.sharding.tables.reservoir_water_data.actual-data-nodes=ds$->{0..9}.reservoir_water_data_0$->{1..9},ds$->{0..9}.reservoir_water_data_1$->{0..2}
   spring.shardingsphere.rules.sharding.tables.reservoir_water_data.table-strategy.standard.sharding-column=TM
   spring.shardingsphere.rules.sharding.tables.reservoir_water_data.table-strategy.standard.sharding-algorithm-name=tb-class-based
   
   this sql is worked:
   SELECT
   rwd.id,
   rwd.STCD,
   sbi.STNM,
   rwd.TM,
   rwd.RZ,
   rwd.INQ,
   rwd.W,
   rwd.BLRZ,
   rwd.OTQ,
   rwd.RWCHRCD,
   rwd.RWPTN,
   rwd.INQDR,
   rwd.MSQMT,
   rwd.ADDVCD
   FROM reservoir_water_data rwd
   LEFT JOIN station_basic_info_data sbi ON sbi.STCD = rwd.STCD
   WHERE 1=1
   AND rwd.STCD = '81120905'
   AND rwd.TM >= '2021-09-26 00:00:00' AND rwd.TM < '2021-09-26 23:55:00'
   
   but this sql doesn't work, and throw a Exception like this "Caused by: org.apache.shardingsphere.infra.exception.ShardingSphereException: Cannot find table rule and default data source with logic tables":
   SELECT
   rwdl.id,
   rwdl.STCD,
   bid.STNM,
   rwdl.TM,
   rwdl.RZ,
   rfl.FSLTDZ AS FRZ,
   (rwdl.RZ - rfl.FSLTDZ) AS difference,
   rfp.DDZ,
   CAST((rfp.DDZ - rwdl.RZ) AS DECIMAL(7,3)) AS DDRZ,
   rwdl.INQ,
   rwdl.W,
   CAST(rfp.TTCP AS DECIMAL(7,3)) AS TTCP,
   CAST((rwdl.W/rfp.TTCP) AS DECIMAL(7,3)) AS full_rate,
   rwdl.BLRZ,
   rwdl.OTQ,
   rwdl.RWCHRCD,
   rwdl.RWPTN,
   rwdl.ADDVCD,
   bid.STLC AS address,
   bid.LGTD,
   bid.LTTD
   FROM reservoir_water_data_latest rwdl
   LEFT JOIN station_basic_info_data bid ON bid.STCD = rwdl.STCD
   LEFT JOIN reservoir_flood_prevention rfp ON rfp.STCD = rwdl.STCD
   LEFT JOIN reservoir_flood_limit rfl ON rfl.STCD = rwdl.STCD 
   
   the table 'reservoir_water_data_latest' doesn't use sharding rule.
   
   When i set the 'actual-data-nodes' like this, other config have no change:
   spring.shardingsphere.rules.sharding.tables.reservoir_flood_limit.actual-data-nodes=ds$->{0}.reservoir_flood_limit
   spring.shardingsphere.rules.sharding.tables.reservoir_flood_prevention.actual-data-nodes=ds$->{0}.reservoir_flood_prevention
   spring.shardingsphere.rules.sharding.tables.station_basic_info_data.actual-data-nodes=ds$->{0}.station_basic_info_data
   spring.shardingsphere.rules.sharding.tables.river_flood_prevention.actual-data-nodes=ds$->{0}.river_flood_prevention
   spring.shardingsphere.rules.sharding.tables.dict_station_belong_reservoir.actual-data-nodes=ds$->{0}.dict_station_belong_reservoir
   spring.shardingsphere.rules.sharding.tables.dict_point_station_relation.actual-data-nodes=ds$->{0}.dict_point_station_relation
   
   the first sql doesn't work and my table sharing rule doesn't too, throw a Exception like this "Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.shardingsphere.sharding.rewrite.token.pojo.TableToken@138e4187 rwd"
   the second sql is run well.
   Actual SQL: ds0 ::: SELECT
       rwd.id,
       rwd.STCD,
       sbi.STNM,
       rwd.TM,
       rwd.RZ,
       rwd.INQ,
       rwd.W,
       rwd.BLRZ,
       rwd.OTQ,
       rwd.RWCHRCD,
       rwd.RWPTN,
       rwd.INQDR,
       rwd.MSQMT,
       rwd.ADDVCD
       FROM org.apache.shardingsphere.sharding.rewrite.token.pojo.TableToken@138e4187 rwd
       LEFT JOIN org.apache.shardingsphere.sharding.rewrite.token.pojo.TableToken@33c5a40e sbi ON sbi.STCD = rwd.STCD
       WHERE 1=1
         AND rwd.STCD = ?  
        
           AND rwd.TM >= ? AND rwd.TM < ?  
        
       AND rwd.id > ? LIMIT ? ::: [81120905, 2021-09-26 00:00:00.0, 2021-09-26 23:55:00.0, 0, 10]


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