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 2022/09/17 08:18:33 UTC
[GitHub] [shardingsphere] qiudao123456 opened a new issue, #21034: SQL limit size be changed where using group by item`s count > 2 in sharding table
qiudao123456 opened a new issue, #21034:
URL: https://github.com/apache/shardingsphere/issues/21034
### Which version of ShardingSphere did you use?
5.0.0-alpha
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
`Logic SQL: SELECT count(1) AS group_data_count, student.* FROM student where class_name is not null and student_name is not null GROUP BY class_name,student_name HAVING count(1) > 1 ORDER BY id ASC LIMIT 100`
### Actual behavior
```
Actual SQL: ds0 ::: SELECT count(1) AS group_data_count, student_0.* FROM student_0 where class_name is not null and student_name is not null GROUP BY class_name,student_name HAVING count(1) > 1 ORDER BY id ASC LIMIT 2147483647；
Actual SQL: ds0 ::: SELECT count(1) AS group_data_count, student_1.* FROM student_1 where class_name is not null and student_name is not null GROUP BY class_name,student_name HAVING count(1) > 1 ORDER BY id ASC LIMIT 2147483647
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
my table ddl
```
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`school_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'school name',
`class_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'class name',
`student_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'student name',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'student' ROW_FORMAT = Dynamic;
```
mysql sharding rule
```
spring:
shardingsphere:
rules:
sharding:
tables:
student:
actual-data-nodes: ds0.student_$->{0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: table-algorithm
sharding-algorithms:
table-algorithm:
type: HASH_MOD
props:
sharding-count: "2"
```
when i exuete sql use group by and group item`s count > 2 , the sql limit size will be changed to 2147483647. even though now in the first page
this is the frame log
```
ShardingSphere-SQL : Logic SQL: SELECT count(1) AS group_data_count, student.* FROM student where class_name is not null and student_name is not null GROUP BY class_name,student_name HAVING count(1) > 1 ORDER BY id ASC LIMIT 100
ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@6a9a621e], lock=Optional.empty)
ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT count(1) AS group_data_count, student_0.* FROM student_0 where class_name is not null and student_name is not null GROUP BY class_name,student_name HAVING count(1) > 1 ORDER BY id ASC LIMIT 2147483647
ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT count(1) AS group_data_count, student_1.* FROM student_1 where class_name is not null and student_name is not null GROUP BY class_name,student_name HAVING count(1) > 1 ORDER BY id ASC LIMIT 2147483647
```
but when the group item`s count = 1 ,the limit size will be correct
this is log
```
ShardingSphere-SQL : Logic SQL: SELECT count(1) AS group_data_count, student.* FROM student where student_name is not null GROUP BY student_name HAVING count(1)> 1 ORDER BY student_name ASC LIMIT 100
ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@693bbd73], lock=Optional.empty)
ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT count(1) AS group_data_count, student_0.* FROM student_0 where student_name is not null GROUP BY student_name HAVING count(1)> 1 ORDER BY student_name ASC LIMIT 100
ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT count(1) AS group_data_count, student_1.* FROM student_1 where student_name is not null GROUP BY student_name HAVING count(1)> 1 ORDER BY student_name ASC LIMIT 100
```
### Example codes for reproduce this issue (such as a github link).
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] linghengqian commented on issue #21034: MYSQL limit size be changed to 2147483647 when i using group by and group item`s count > 2
Posted by GitBox <gi...@apache.org>.
linghengqian commented on issue #21034:
URL: https://github.com/apache/shardingsphere/issues/21034#issuecomment-1250232835
- I have the impression that this issue was fixed in an issue in the past. Can you reproduce with ShardingSphere 5.2.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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qiudao123456 commented on issue #21034: MYSQL limit size be changed to 2147483647 when i using group by and group item`s count > 2
Posted by GitBox <gi...@apache.org>.
qiudao123456 commented on issue #21034:
URL: https://github.com/apache/shardingsphere/issues/21034#issuecomment-1252107589
Thank you. I'll try later
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qiudao123456 closed issue #21034: MYSQL limit size be changed to 2147483647 when i using group by and group item`s count > 2
Posted by GitBox <gi...@apache.org>.
qiudao123456 closed issue #21034: MYSQL limit size be changed to 2147483647 when i using group by and group item`s count > 2
URL: https://github.com/apache/shardingsphere/issues/21034
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org