[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue #4565: distinct returns wrong result

[GitBox]

geomonlin edited a comment on issue #4565: distinct returns wrong result
URL: 
https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593724603
 
 
   I test it with mysql, This seems to be a bug. eg: table: t_order
    `CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL 
AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY 
(order_id));`
   sharding rule: 
   ```
   schemaName: sharding_db
   
   dataSources:
     ds_0:
       url: 
jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
       username: root
       password:
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
     ds_1:
       url: 
jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
       username: root
       password:
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
   
   shardingRule:
     tables:
       t_order:
         actualDataNodes: ds_${0..1}.t_order_${0..1}
         tableStrategy:
           inline:
             shardingColumn: order_id
             algorithmExpression: t_order_${order_id % 2}
         keyGenerator:
           type: SNOWFLAKE
           column: order_id
       t_order_item:
         actualDataNodes: ds_${0..1}.t_order_item_${0..1}
         tableStrategy:
           inline:
             shardingColumn: order_id
             algorithmExpression: t_order_item_${order_id % 2}
         keyGenerator:
           type: SNOWFLAKE
           column: order_item_id
     bindingTables:
       - t_order,t_order_item
     defaultDatabaseStrategy:
       inline:
         shardingColumn: user_id
         algorithmExpression: ds_${user_id % 2}
     defaultTableStrategy:
       none:
   
   ```
   The inserted user_id  contains
   **6
   2
   2
   2
   6
   4
   2
   2
   3
   3
   1
   3
   3**
   When exec "SELECT distinct user_id from t_order", the output resutl is 
Duplicate data，
   **3
   1
   3
   4
   6
   4
   2
   6
   2**
   When exec `SELECT distinct user_id from t_order GROUP BY user_id`, the 
resutl is Correct.
   **1
   2
   3
   4
   6**
   

----------------------------------------------------------------
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:
us...@infra.apache.org


With regards,
Apache Git Services