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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
