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/25 07:06:39 UTC

[GitHub] [shardingsphere] seowen808 opened a new issue #7052: sql cannot be sharded using Group BY

seowen808 opened a new issue #7052:
URL: https://github.com/apache/shardingsphere/issues/7052


   SpringBoot2.3 +mybatis+mysql8 integration sharding 4.1.1,
   Use read/write separation + sharding.When there is Group BY in the SQL statement, sharding fails and cannot be routed to a specific physical table.After removing Group BY.You can route to a specific table and execute successfully.
   
   The Springboot YAML configuration is as follows：
   
   ![image](https://user-images.githubusercontent.com/46375421/91142973-59b22300-e6e4-11ea-9c20-686ad2148854.png)
   
   Some of the important code (with GRUop by) is as follows：
   
   ![image](https://user-images.githubusercontent.com/46375421/91142989-60409a80-e6e4-11ea-8da2-aaa24a7d6695.png)
   
   
   The output log is executed as follows (cannot be routed to CONSUMER_link_8 and consumer_link_9)：
   
   ![image](https://user-images.githubusercontent.com/46375421/91143022-6c2c5c80-e6e4-11ea-9f0e-07fd417d8ce4.png)
   
   
   Some of the important code (without GRUop BY) is as follows：
   ![image](https://user-images.githubusercontent.com/46375421/91143034-76e6f180-e6e4-11ea-8c6e-07d818cd1b3c.png)
   
   
   The output log is executed as follows (successfully routed to CONSUMER_link_8 and consumer_link_9)：
   ![image](https://user-images.githubusercontent.com/46375421/91143064-823a1d00-e6e4-11ea-80dd-46053a43196f.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] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   > release version
   
   @strongduanmu 
   This is not appropriate. It will be some time before the 5. X version comes out.
   Do I need to change the code logic because of this bug?


----------------------------------------------------------------
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] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   > Hi,@seowen808
   > 
   > Could you rewrite your SQL without subquery and give it a try?
   
   @tristaZero
   **without subquery it can be used.**
   
   ![image](https://user-images.githubusercontent.com/46375421/91149762-1361c180-e6ee-11ea-8e86-423c134be368.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] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   > 嗨@ seowen808
   > 
   > 您可以在不使用子查询的情况下重写SQL并尝试一下吗？
   
   without subquery it can be used.
   
   ![image](https://user-images.githubusercontent.com/46375421/91147584-0ee7d980-e6eb-11ea-80f3-edb62cf24614.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] seowen808 removed a comment on issue #7052: sql cannot be sharded using Group BY

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


   > 嗨@ seowen808
   > 
   > 您可以在不使用子查询的情况下重写SQL并尝试一下吗？
   
   without subquery it can be used.
   
   ![image](https://user-images.githubusercontent.com/46375421/91147584-0ee7d980-e6eb-11ea-80f3-edb62cf24614.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] tristaZero commented on issue #7052: sql cannot be sharded using Group BY

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


   Hi @seowen808  @strongduanmu 
   
   My impression is that we fixed this issue on `the master branch`. But I am sorry to say currently 4.x has inadequate support for `subquery` and there is no plan to release the subsequent 4.x.
   
   Therefore programming modification or waiting for 5.x are my solutions for you. Or we can expect feedback from  @strongduanmu .
   


----------------------------------------------------------------
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] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   > ```sql
   > SELECT * FROM t_order_0 WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id
   > ```
   
   @strongduanmu 
   
   You can try again with subqueries like this:
   
   select count(1) from (SELECT * FROM t_order WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id) total


----------------------------------------------------------------
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] strongduanmu commented on issue #7052: sql cannot be sharded using Group BY

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


   > > ```sql
   > > SELECT * FROM t_order_0 WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id
   > > ```
   > 
   > @strongduanmu
   > 
   > You can try again with subqueries like this:
   > 
   > select count(1) from (SELECT * FROM t_order WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id) total
   
   @seowen808 Thank you, I will test again.


----------------------------------------------------------------
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] strongduanmu commented on issue #7052: sql cannot be sharded using Group BY

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


   @seowen808 Hi, the result of this SQL routing in the master branch is correct. I think you can wait for the 5.x release version.
   
   ```sql
   [INFO ] 09:02:35.586 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: /* ApplicationName=DataGrip 2020.2 */ select count(1) from (SELECT * FROM t_order WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id) total
   [INFO ] 09:02:35.586 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1f899838, tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=74, stopIndex=80, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)])), tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=74, stopIndex=80, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]), projectionsContext=ProjectionsContext(startIndex=45, stopIndex=52, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByCont
 ext@6fb7b5f5, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@5e1431a7, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@6b45692d, containsSubquery=true)
   [INFO ] 09:02:35.586 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds ::: /* ApplicationName=DataGrip 2020.2 */ select count(1) from (SELECT * FROM t_order_0 WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id) total
   [INFO ] 09:02:35.586 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds ::: /* ApplicationName=DataGrip 2020.2 */ select count(1) from (SELECT * FROM t_order_1 WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id) total
   ```


----------------------------------------------------------------
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] strongduanmu commented on issue #7052: sql cannot be sharded using Group BY

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


   @seowen808 @tristaZero @kimmking I have used the master branch to test the `Select ... Group By` syntax, and the routing results were correct.
   
   ```sql
   [INFO ] 18:45:23.563 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: /* ApplicationName=DataGrip 2020.2 */ SELECT * FROM t_order WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id
   [INFO ] 18:45:23.563 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c764d7, tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=52, stopIndex=58, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)])), tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=52, stopIndex=58, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]), projectionsContext=ProjectionsContext(startIndex=45, stopIndex=45, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=order_id, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null,
  name=status, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@707c83c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@63c9e303, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@1caf41ce, containsSubquery=false)
   [INFO ] 18:45:23.564 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds ::: /* ApplicationName=DataGrip 2020.2 */ SELECT * FROM t_order_0 WHERE order_id IN (1, 2, 3) GROUP BY order_id ORDER BY order_id
   [INFO ] 18:45:23.564 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds ::: /* ApplicationName=DataGrip 2020.2 */ SELECT * FROM t_order_1 WHERE order_id IN (1, 2, 3) 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

[GitHub] [shardingsphere] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   @strongduanmu  thank you. I had to override the paging plug-in for Mybatis-Plus to fix this problem


----------------------------------------------------------------
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] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   thanks @strongduanmu 


----------------------------------------------------------------
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] seowen808 commented on issue #7052: sql cannot be sharded using Group BY

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


   Could you find the branch @tristaZero  


----------------------------------------------------------------
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] strongduanmu commented on issue #7052: sql cannot be sharded using Group BY

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


   @seowen808 I have tested this bug in version 4.1.1 and found that the reason for the wrong routing result is the `subquery` in the from clause, not the `group by` clause. `Subquery` is not fully supported in 4.x version, and will be improved in 5.x version.
   I'm so sorry for that there is no better solution now if you use the 4.x version. 😔
   


----------------------------------------------------------------
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] strongduanmu commented on issue #7052: sql cannot be sharded using Group BY

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


   @seowen808 I will test this bug in version 4.1.1.


----------------------------------------------------------------
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 #7052: sql cannot be sharded using Group BY

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


   hi, @seowen808 you can rewrite your sql to:
   > select count(1) FROM t_order WHERE order_id IN (1, 2, 3) 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

[GitHub] [shardingsphere] tristaZero commented on issue #7052: sql cannot be sharded using Group BY

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


   Hi,@seowen808 
   
   Could you rewrite your SQL without subquery and give it a try?


----------------------------------------------------------------
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 #7052: sql cannot be sharded using Group BY

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


   


----------------------------------------------------------------
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] strongduanmu edited a comment on issue #7052: sql cannot be sharded using Group BY

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


   @seowen808 I will test this bug in version 4.1.1 to see if there is a solution.


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