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/11/26 18:48:27 UTC
[GitHub] [shardingsphere] linghengqian opened a new issue, #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in MySQL and H2Database
linghengqian opened a new issue, #22451:
URL: https://github.com/apache/shardingsphere/issues/22451
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
- 5.2.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
- ShardingSphere-JDBC
### Expected behavior
- The group by statement with division sign can be executed for the datatime type field of the logical table in MySQL and H2Database.
### Actual behavior
- The group by statement with division sign cannot be executed for the datatime type field of the logical table in MySQL and H2Database
### Reason analyze (If you can)
- I'm not sure if this is a common problem, it happens with both H2database and MySQL.
- `spring.shardingsphere.props.sql-federation-type` set to `ADVANCED` works for H2Database, **but** not for MySQL, I'm not sure why this is.
```shell
### Cause: java.lang.ClassCastException: class java.time.LocalDateTime cannot be cast to class java.lang.String (java.time.LocalDateTime and java.lang.String are in module java.base of loader 'bootstrap')
```
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
- In my given example git has sql statements with similar specifications, which are successfully executed through the real table,
```sql
select `id_string`,`create_time`
from t_order_20221125
where `create_time` between '2022-11-24 00:00:00.000' and '2022-11-25 00:00:00.000'
group by (unix_timestamp(`create_time`) / 3600)
order by `create_time`
limit 30
```
- but fail to be executed through the logical table.
```sql
select `id_string`,`create_time`
from t_order
where `create_time` between '2022-11-24 00:00:00.000' and '2022-11-25 00:00:00.000'
group by (unix_timestamp(`create_time`) / 3600)
order by `create_time`
limit 30
```
- Performing simpler operations on logical tables was successful.
```sql
select `id_string`,`create_time`
from t_order
where `create_time` between '2022-11-24 00:00:00.000' and '2022-11-25 00:00:00.000'
limit 30
```
- After cloning the git of https://github.com/linghengqian/group-by-shardingsphere-test , execute `./mvnw clean test` directly on the command line to see this result.
### Example codes for reproduce this issue (such as a github link).
- https://github.com/linghengqian/group-by-shardingsphere-test
--
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] strongduanmu commented on issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in H2Database
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #22451:
URL: https://github.com/apache/shardingsphere/issues/22451#issuecomment-1697230707
> * I finished verifying in [linghengqian/group-by-shardingsphere-test@033ef1b](https://github.com/linghengqian/group-by-shardingsphere-test/commit/033ef1b778a5684f7d7c5df42ca95cd1786173e6) and no longer reported an error using `unix_timestamp` and `group by` on the logical table, and this issue has been fixed in ShardingSphere JDBC 5.4.0.
Of course, If this issue has been solved, you can close it.
--
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] strongduanmu commented on issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in MySQL and H2Database
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22451:
URL: https://github.com/apache/shardingsphere/issues/22451#issuecomment-1328451815
@linghengqian Thank you for your feedback, I will investigate this issue.
--
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] linghengqian commented on issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in H2Database
Posted by "linghengqian (via GitHub)" <gi...@apache.org>.
linghengqian commented on issue #22451:
URL: https://github.com/apache/shardingsphere/issues/22451#issuecomment-1697018009
- @strongduanmu Can I close the issue?
--
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] linghengqian commented on issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in MySQL and H2Database
Posted by GitBox <gi...@apache.org>.
linghengqian commented on issue #22451:
URL: https://github.com/apache/shardingsphere/issues/22451#issuecomment-1341459758
@strongduanmu
- Late reply. I found that the problem with MySQL through TestContainer is that ShardingSphere JDBC mishandled Mybatis-specific Logger. At the same time, I saw problems with the integration of ShardingSphere JDBC and TestContainer that prevented me from continuing to explore some details. I will open two new issues later.
- The current problem is largely only related to the design of H2Database. If ShardingSphere does not actually handle SQL optimization issues in H2Database, should I close the current issue?
--
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] linghengqian commented on issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in MySQL and H2Database
Posted by GitBox <gi...@apache.org>.
linghengqian commented on issue #22451:
URL: https://github.com/apache/shardingsphere/issues/22451#issuecomment-1341495405
- If we don't consider H2Database, this issue should actually be https://github.com/apache/shardingsphere/issues/22736 , I used TestContainer to reproduce the MySQL 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] linghengqian commented on issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in H2Database
Posted by "linghengqian (via GitHub)" <gi...@apache.org>.
linghengqian commented on issue #22451:
URL: https://github.com/apache/shardingsphere/issues/22451#issuecomment-1696993331
- I finished verifying in https://github.com/linghengqian/group-by-shardingsphere-test/commit/033ef1b778a5684f7d7c5df42ca95cd1786173e6 and no longer reported an error using `unix_timestamp` and `group by` on the logical table, and this issue has been fixed in ShardingSphere JDBC 5.4.0.
```sql
select `id_string`,`create_time`
from t_order
where `create_time` between '2022-11-24 00:00:00.000' and '2022-11-25 00:00:00.000'
group by (unix_timestamp(`create_time`) / 3600)
order by `create_time`
limit 30
```
--
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] linghengqian closed issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in H2Database
Posted by "linghengqian (via GitHub)" <gi...@apache.org>.
linghengqian closed issue #22451: The group by statement with division sign cannot be executed for the datatime type field of the logical table in H2Database
URL: https://github.com/apache/shardingsphere/issues/22451
--
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