[GitHub] [shardingsphere] kimmking commented on issue #7201: Unexpected SELECT DISTINCT result

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

kimmking commented on issue #7201:
URL: https://github.com/apache/shardingsphere/issues/7201#issuecomment-686957573


   With the config rule and test:
   ```
   schemaName: testproxy
   
   dataSources:
     test0:
       url: jdbc:mysql://127.0.0.1:3306/test0?serverTimezone=UTC&useSSL=false
       username: root
       password: 
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
     test1:
       url: jdbc:mysql://127.0.0.1:3306/test1?serverTimezone=UTC&useSSL=false
       username: root
       password: 
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
   
   rules:
   - !SHARDING
     tables:
       test:
         actualDataNodes: test${0..1}.test0${0..3}
         tableStrategy:
           standard:
             shardingColumn: id
             shardingAlgorithmName: test_inline
         keyGenerateStrategy:
             column: id
             keyGeneratorName: snowflake
     defaultDatabaseStrategy:
       standard:
         shardingColumn: id
         shardingAlgorithmName: database_inline
     defaultTableStrategy:
       none:
   
     shardingAlgorithms:
       database_inline:
         type: INLINE
         props:
           algorithm.expression: test${id % 2}
       test_inline:
         type: INLINE
         props:
           algorithm.expression: test0${id % 4}
   
     keyGenerators:
         snowflake:
           type: SNOWFLAKE
           props:
               worker.id: 123
   
   ```
   
   And the test case:
   
   ```
   mysql> select * from test order by id;
   +----+------+---------------------+
   | id | name | created             |
   +----+------+---------------------+
   |  1 | KK01 | 2020-09-04 14:58:03 |
   |  2 | KK02 | 2020-09-04 14:58:03 |
   |  3 | KK03 | 2020-09-04 14:58:03 |
   |  4 | KK04 | 2020-09-04 14:58:03 |
   |  5 | KK05 | 2020-09-04 14:58:03 |
   |  6 | KK06 | 2020-09-04 14:58:03 |
   |  7 | KK07 | 2020-09-04 14:58:03 |
   |  8 | KK08 | 2020-09-04 14:58:03 |
   +----+------+---------------------+
   8 rows in set (0.01 sec)
   
   mysql> select * from test order by id;mysql> Ctrl-C -- exit!
   
   mysql> show tables;
   +-----------------+
   | Tables_in_test1 |
   +-----------------+
   | test            |
   +-----------------+
   1 row in set (0.02 sec)
   
   mysql> select distinct id,name from test;
   +----+------+
   | id | name |
   +----+------+
   |  1 | KK01 |
   |  2 | KK02 |
   |  3 | KK03 |
   |  4 | KK04 |
   |  5 | KK05 |
   |  6 | KK06 |
   |  7 | KK07 |
   |  8 | KK08 |
   +----+------+
   8 rows in set (0.07 sec)
   
   mysql> select sum(id) from test;
   +---------+
   | sum(id) |
   +---------+
   |      36 |
   +---------+
   1 row in set (0.04 sec)
   
   mysql> select avg(id) from test;
   +---------+
   | avg(id) |
   +---------+
   |  4.5000 |
   +---------+
   1 row in set (0.01 sec)
   
   mysql> select avg(distinct id) from test;
   +--------------------------------+
   | AGGREGATION_DISTINCT_DERIVED_0 |
   +--------------------------------+
   |                         1.0000 |
   +--------------------------------+
   1 row in set (0.11 sec)
   ```
   
   It show the case of distinct with multiple columns works well.


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