You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "lnnkai (via GitHub)" <gi...@apache.org> on 2023/02/10 07:34:15 UTC
[GitHub] [shardingsphere] lnnkai opened a new issue, #24103: Get wrong results cause by time precision
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Get wrong results cause by time precision [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-2212389810
`shardingsphere-jdbc-core-spring-boot-starter` is deprecated, please try new version and use ShardingSphere Driver.
--
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] TeslaCN commented on issue #24103: Get wrong results cause by time precision
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1427260835
Have you tried connecting to MySQL directly?
The `datetime` precise default is `0`. So setting the milliseconds to `999` didn't worked.
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-syntax.html
<img width="568" alt="image" src="https://user-images.githubusercontent.com/20503072/218360470-76330364-a3c0-41da-9528-f93cd48b42b7.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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Get wrong results cause by time precision [shardingsphere]
Posted by "268029642 (via GitHub)" <gi...@apache.org>.
268029642 commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1842038987
I had the same problem
The mysql type is date, which is stored in 2023-12-06 23:59:59:999
It's listed in the database as 2023-12-07
But you don't need a shardingsphere
--
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
Re: [I] Get wrong results cause by time precision [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #24103: Get wrong results cause by time precision
URL: https://github.com/apache/shardingsphere/issues/24103
--
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
Re: [I] Get wrong results cause by time precision [shardingsphere]
Posted by "dyrnq (via GitHub)" <gi...@apache.org>.
dyrnq commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1809469920
> I think this is caused by your usage.
>
> Have you tried connecting to MySQL directly? The `datetime` precise default is `0`. So setting the milliseconds to `999` didn't worked. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-syntax.html
>
> <img alt="image" width="568" src="https://user-images.githubusercontent.com/20503072/218360470-76330364-a3c0-41da-9528-f93cd48b42b7.png">
@TeslaCN
I think this is not the same,
when tb_user.sub_date has datetime(0), the flowing select result are same
```bash
select * from tb_user where sub_date between '2023-05-10 00:00:00.0' and '2023-05-10 23:59:59.999';
select * from tb_user where sub_date between '2023-05-10 00:00:00.0' and '2023-05-10 23:59:59';
```
--
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
Re: [I] Get wrong results cause by time precision [shardingsphere]
Posted by "dyrnq (via GitHub)" <gi...@apache.org>.
dyrnq commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1800860839
@lnnkai
Same problem with 5.2.1,I test 5.3.0 the results are correct.
I don't know which pull fixed this issue specifically.
--
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
Re: [I] Get wrong results cause by time precision [shardingsphere]
Posted by "dyrnq (via GitHub)" <gi...@apache.org>.
dyrnq commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1809457789
@TeslaCN when sub_date added index, result are wrong again (with sharding-jdbc 5.4.1), use default com.zaxxer.hikari.HikariDataSource driver has no problem.
@lnnkai did you solve this problem?
when PreparedStatement SQL like sub_date between #{dateStart} and #{dateEnd}
when dateStart AND set dateEnd with java.util.Date the result wrong,
BUT
when dateStart AND set dateEnd with java.lang,String the result right
--
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] TeslaCN commented on issue #24103: Get wrong results cause by time precision
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1427398180
> Thank you so much.
> I understand what you mean, and I have already noticed this before, so I understand that improving the precise of the `datetime` can solve the current issue. What I actually wanted to ask is, if I directly connect to mysql, there is no problem, but when using `shardingjdbc`, there will be a problem. Is it because `shardingjdbc` has implicit conversion or other special handling when intercepting and processing Java `Date` types, causing the problem to occur?
ShardingSphere-JDBC just passes parameters to actual `PreparedStatement` by `setObject`. You may try debugging and finding out the root cause. Or it would be helpful if you could provide us a minimum reproducible demo.
--
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] lnnkai commented on issue #24103: Get wrong results cause by time precision
Posted by "lnnkai (via GitHub)" <gi...@apache.org>.
lnnkai commented on issue #24103:
URL: https://github.com/apache/shardingsphere/issues/24103#issuecomment-1427299845
>
Thank you so much.
I understand what you mean, and I have already noticed this before, so I understand that improving the precise of the `datetime` can solve the current issue. What I actually wanted to ask is, if I directly use mybatis to connect to mysql, there is no problem, but when using `shardingjdbc`, there will be a problem. Is it because `shardingjdbc` has implicit conversion or other special handling when intercepting and processing Java `Date` types, causing the problem to occur?
--
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