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/09/28 07:12:44 UTC

[GitHub] [shardingsphere] xlxomg opened a new issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

xlxomg opened a new issue #7643:
URL: https://github.com/apache/shardingsphere/issues/7643


   ## Bug Report
   
   **When grouping queries, if the aggregate function is not the outermost, the result is wrong.And I didn't find the description in the document (translation software)**    
   ![图片](https://user-images.githubusercontent.com/26641605/94401137-b483e200-019c-11eb-8d1d-e4fc2d187bf5.png)
   
   ### Which version of ShardingSphere did you use?
          4.1.1
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
         jdbc and proxy both have this problem
   ### Expected behavior
         
   ### Actual behavior
   
   ### Reason analyze (If you can)
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   ### Example codes for reproduce this issue (such as a github link).
   
   
   The test SQL is as follows, where S File Info is a sharded table:
   ```
   SELECT SUM(file_size) right1,
   SUM(file_size/1000/1000) right2,
   SUM(file_size)/1000/1000 error2,
   CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2
   FROM s_file_info  GROUP BY project_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

[GitHub] [shardingsphere] kimmking commented on issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   what's your meanings?


----------------------------------------------------------------
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 #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   "CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2"
   is not supported now.


----------------------------------------------------------------
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 #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   I test in master branch:
   ```
   mysql> select sum(id)/10 from test;
   ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `select sum(id)/10 from test`'
   ```


----------------------------------------------------------------
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] xlxomg commented on issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   That "SUM (file_size) / 1000/1000"? Or if the aggregate function is not in the outermost layer


----------------------------------------------------------------
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] xlxomg commented on issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   Is "SUM (file_size) / 1000/1000" also not supported?
   In addition, my tests found that in the case of grouped queries, if the sum function still had computations around it, the results would be 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] xlxomg commented on issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   ShardingProxy 4.1.1 There is no indication that it is not supported, but the data is incorrect.
   
   ![图片](https://user-images.githubusercontent.com/26641605/94413938-5233dd00-01ae-11eb-996d-60df8703d330.png)
   ![图片](https://user-images.githubusercontent.com/26641605/94414209-a474fe00-01ae-11eb-9662-1e9a0cc6b54d.png)
   
   
   
   


----------------------------------------------------------------
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] xlxomg commented on issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   @kimmking Because My English is not very good, the use of translation software, the expression is not accurate, forgive me


----------------------------------------------------------------
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 closed issue #7643: The aggregate function is not correct: SELECT SUM(file_size) right1, SUM(file_size/1000/1000) right2, SUM(file_size)/1000/1000 error2, CONCAT(IFNULL(FLOOR(SUM(file_size/1000/1000)),'0'),'MB') error2 FROM s_file_info GROUP BY project_id

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


   


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