apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.71k stars 6.68k forks source link

Routing disorder of sharding of statement #24171

Open arris4w opened 1 year ago

arris4w commented 1 year ago

Caused by exist parameter before where

SQL:

select t.agent_id , sum(if(t.sjid = ? ,1,0 )) num
from t_user_address t
where t.agent_id =? and t.sjid = ?

Configuration:

spring:
  shardingsphere:
    mode:
      type: Memory
    props:
      sql-show: true
      max-connections-size-per-query: 4
      check-table-metadata-enabled: false
    datasource:
      names: master-1-0,slave-1-1
      master-1-0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/user?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
        username: root
        password: 123456
      slave-1-1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/user?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
        username: root
        password: 123456
    rules:
      sharding:
        default-data-source-name: rws-ds-1
        tables:
          t_user_address:
            actual-data-nodes: rws-ds-1.t_user_address_$->{0..3}
            table-strategy:
              standard:
                sharding-column: agent_id
                sharding-algorithm-name: sharding-table-agentid
        sharding-algorithms:
          sharding-table-agentid:
            type: INLINE
            props:
              algorithm-expression: t_user_address_$->{agent_id % 4}
      readwrite-splitting:
        data-sources:
          rws-ds-1:
            type: STATIC
            props:
              write-data-source-name: master-1-0
              read-data-source-names: slave-1-1
            load-balancer-name: round-robin-c3
        load-balancers:
          round-robin-c3:
            type: ROUND_ROBIN

Phenomenon: image Expect sharding value 1012738001 but 9538 ,that the first index of parameter list matched first condition after where.

tuichenchuxin commented 1 year ago

It seems that when parsing, the confusion caused by not parsing the first parameter.