You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/08/06 03:31:54 UTC

[GitHub] [shardingsphere] tuohai666 opened a new issue #6658: SELECT DISTINCT test cases error

tuohai666 opened a new issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658


   ## Bug Report
   
   **For English only**, other languages will not accept.
   
   Before report a bug, make sure you have:
   
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore and we cannot reproduce it on current information, we will **close it**.
   
   Please answer these questions before submitting your issue. Thanks!
   
   ### Which version of ShardingSphere did you use?
   
   5.0.0-RC1-SNAPSHOT
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   
   Both
   
   ### Expected behavior
   
   SELECT DISTINCT test cases pass.
   
   ### Actual behavior
   
   When execute the integration test with MySQL, some of the SELECT DISTINCT case failed.
   
   ### Reason analyze (If you can)
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   1. env.properties:
   databases=MySQL
   
   2. hosts: (suppose your MySQL is on localhost)
   add 127.0.0.1 db.mysql
   
   3. mvn clean install
   
   ### Example codes for reproduce this issue (such as a github link).
   
   Refer to #6603


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

[GitHub] [shardingsphere] tuohai666 closed issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658


   


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

[GitHub] [shardingsphere] tuohai666 commented on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-684943141


   According to the docs https://shardingsphere.apache.org/document/current/en/features/sharding/use-norms/sql/ , this is an unsupported SQL. I'll remove it from the integration test cases.


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

[GitHub] [shardingsphere] kimmking commented on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-680671377


   Case 1: shardingsphere-integration-test/shardingsphere-test-suite/src/test/resources/integrate/cases/dql/dataset/tbl/select_distinct_with_avg.xml
   
   The original sql
   ```
   SELECT AVG(DISTINCT order_id) FROM t_order WHERE order_id < 1100
   ```
   
   convert to:
   ```
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_1 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100
   ```
   
   Rewriting result is wrong
   


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

[GitHub] [shardingsphere] tuohai666 removed a comment on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
tuohai666 removed a comment on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-684943141


   According to the docs https://shardingsphere.apache.org/document/current/en/features/sharding/use-norms/sql/ , this is an unsupported SQL. I'll remove it from the integration test cases.


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

[GitHub] [shardingsphere] kimmking edited a comment on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-680671377


   Case 1: shardingsphere-integration-test/shardingsphere-test-suite/src/test/resources/integrate/cases/dql/dataset/tbl/select_distinct_with_avg.xml
   
   The original sql
   ```
   SELECT AVG(DISTINCT order_id) FROM t_order WHERE order_id < 1100
   ```
   
   convert to:
   ```
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_1 WHERE order_id < 1100
   ......
   ```
   
   Rewriting result is wrong
   


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

[GitHub] [shardingsphere] kimmking edited a comment on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-680671377


   Case 1: shardingsphere-integration-test/shardingsphere-test-suite/src/test/resources/integrate/cases/dql/dataset/tbl/select_distinct_with_avg.xml
   
   The original sql
   ```
   SELECT AVG(DISTINCT order_id) FROM t_order WHERE order_id < 1100
   ```
   
   convert to:
   ```
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_1 WHERE order_id < 1100
   ......
   ```
   
   Rewriting result is wrong, it should be:
   ```
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , COUNT(order_id) AS AVG_DERIVED_COUNT_0 , SUM(order_id) AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100 GROUP BY order_id ORDER BY order_id;
   ```
   
   There are two wrong rewriting:
   1. Missing GROUP-BY and ORDER-BY
   2. Missing COUNT and SUM
   
   


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

[GitHub] [shardingsphere] kimmking commented on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-680702297


   With debug, the GroupBy generated  return empty. 
   ```
       public GroupByContext createGroupByContext(final SelectStatement selectStatement) {
           if (!selectStatement.getGroupBy().isPresent()) {
               return new GroupByContext(new LinkedList<>(), 0);
           }
   ```
   


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

[GitHub] [shardingsphere] kimmking edited a comment on issue #6658: SELECT DISTINCT test cases error

Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #6658:
URL: https://github.com/apache/shardingsphere/issues/6658#issuecomment-680671377


   Case 1: shardingsphere-integration-test/shardingsphere-test-suite/src/test/resources/integrate/cases/dql/dataset/tbl/select_distinct_with_avg.xml
   
   The original sql
   ```
   SELECT AVG(DISTINCT order_id) FROM t_order WHERE order_id < 1100
   ```
   
   convert to:
   ```
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , order_id AS AVG_DERIVED_COUNT_0 , order_id AS AVG_DERIVED_SUM_0 FROM t_order_1 WHERE order_id < 1100
   ......
   ```
   
   Rewriting result is wrong, it should be:
   ```
   SELECT DISTINCT order_id AS AGGREGATION_DISTINCT_DERIVED_0 , count(order_id) AS AVG_DERIVED_COUNT_0 , sum(order_id) AS AVG_DERIVED_SUM_0 FROM t_order_0 WHERE order_id < 1100 GROUP BY order_id ORDER BY order_id;
   ```
   
   


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