lnnkai opened a new issue, #24103:
URL: https://github.com/apache/shardingsphere/issues/24103
## Bug Report
Hi, I got wrong results when query by ShardingJDBC.
### mysql version:
5.7+
### mysql topology:
* 1 master node for WRITE/READ
* 1 slave node for READ only
### JAVA Project Env:
* SpringBoot 3.0 +
* shardingJDBC 5.2.1，maven dependency like this:
`
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
</dependency>
`
### Datasource Config:
`yml
shardingsphere:
database.name: db_test
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://127.0.0.1:3306/db_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://127.0.0.2:3306/db_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123
masterslave:
load-balance-algorithm-type=round_robin: round_robin
name: ms
master-data-source-name: ds0
slave-data-source-names: ds0,ds1
props:
sql.show: true
`
### MySQL Table Definition
`sql
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sub_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COMMENT='subscribe
user table';
`
### MySQL Data For Test
`sql
INSERT into tb_user (id, sub_date)
VALUES
(1, '2023-05-10 00:00:00'),
(2, '2023-05-11 00:00:00');
`
### Excute Query
in java code:
`java
Date someDay = ... // the day is '2023-05-10'
// get begin of day, like '2023-05-10 00:00:00.0'
Date dateStart = DateUtils.addDays(someDay, 1);
start = DateUtils.setHours(start, 0);
start = DateUtils.setMinutes(start, 0);
start = DateUtils.setSeconds(start, 0);
start = DateUtils.setMilliseconds(start, 0);
// get end of day, like '2023-05-10 23:59:59.999'
Date dateEnd = DateUtils.addDays(someDay, 1);
end = DateUtils.setHours(end, 23);
end = DateUtils.setMinutes(end, 59);
end = DateUtils.setSeconds(end, 59);
end = DateUtils.setMilliseconds(end, 999);
`
`sql
select * from tb_user where sub_date between #{dateStart} and #{dateEnd};
`
### Expected behavior
One result expected, the record which id equal to `1`
### Actual behavior
Get wrong reults, which id is `1` and `2` returned.
### Reason analyze (If you can)
I found three way to resolve this problem below:
1、Modify column definition, improve precision:
`sub_date` datetime(3)
2、Remove shardingJDBC
3、Optimized query syntax like this:
`sql
select * from tb_user where sub_date >= '2023-05-10 00:00:00.0' and sub_date
< '2023-05-11 00:00:00.0';
`
### Is there any bug in ShardingJDBC?
--
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]
