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 2021/02/03 07:22:20 UTC
[GitHub] [shardingsphere] lazyboyl opened a new issue #9291: The name of the associated query library table changes from uppercase to lowercase
lazyboyl opened a new issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291
## Bug Report
### Which version of ShardingSphere did you use?
ShardingSphere-5.0.0-alpha
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
`Actual SQL: db1 ::: select t.ORDER_ID,t.user_Id,t.create_time,toi.* from T_ORDER3 t inner join T_ORDER_ITEM3 toi on t.order_id = toi.order_id where t.order_id = ? ::: [3]`
### Actual behavior
`Actual SQL: db1 ::: select t.ORDER_ID,t.user_Id,t.create_time,toi.* from T_ORDER3 t inner join t_order_item3 toi on t.order_id = toi.order_id where t.order_id = ? ::: [3]`
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
- SQL in Dao's XML of mybatis
```
<select id="queryMyOrder" resultMap="OrderResultMap" >
select t.ORDER_ID,t.user_Id,t.create_time,toi.* from T_ORDER t inner join T_ORDER_ITEM toi on t.order_id = toi.order_id where t.order_id = #{orderId}
</select>
```
- application.yml configuration file
```
spring:
shardingsphere:
props:
sql-show: true
datasource:
common:
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
names: db0,db1
db0:
url: jdbc:mysql://127.0.0.1:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
username: root
password: '123456'
db1:
url: jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
username: root
password: '123456'
rules:
sharding:
binding-tables[0]: T_ORDER,T_ORDER_ITEM
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
sharding-algorithms:
order-item-inline:
type: INLINE
props:
algorithm-expression: T_ORDER_ITEM$->{order_id % 4}
table-inline:
type: INLINE
props:
algorithm-expression: T_ORDER$->{order_id % 4}
database-inline:
type: INLINE
props:
algorithm-expression: db$->{order_id % 2}
tables:
T_ORDER_ITEM:
actual-data-nodes: db$->{0..1}.T_ORDER_ITEM$->{0..3}
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: order-item-inline
database-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: database-inline
T_ORDER:
actual-data-nodes: db$->{0..1}.T_ORDER$->{0..3}
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
database-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: database-inline
enabled: true
```
### 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-773091647
> Hi~ @lazyboyl ！
> First of all, it is clear that the windows environment is not case sensitive.Secondly, the design of shardingsphere 5.0.0-apha is not case sensitive. Even if you are using a Linux system, it is recommended to temporarily turn off case sensitivity.
>
> About the SQL statement, your question is similar to #9270.
> In our 5.0.0-apha, if join and ‘_’ are involved at the same time, it may cause some data loss problems in the result merging.We also found this problem and fixed it in the latest version (master branch). My suggestion is to avoid using ‘_’ as much as possible, or try to install the master branch, or wait for our upcoming release version!
> Hi~ @lazyboyl ！
> First of all, it is clear that the windows environment is not case sensitive.Secondly, the design of shardingsphere 5.0.0-apha is not case sensitive. Even if you are using a Linux system, it is recommended to temporarily turn off case sensitivity.
>
> About the SQL statement, your question is similar to #9270.
> In our 5.0.0-apha, if join and ‘_’ are involved at the same time, it may cause some data loss problems in the result merging.We also found this problem and fixed it in the latest version (master branch). My suggestion is to avoid using ‘_’ as much as possible, or try to install the master branch, or wait for our upcoming release version!
It is mainly in the investigation stage that various scenarios need to be considered. Therefore, the MySQL database sensitivity is specially turned on, and this problem is found during the verification, so it is fed back to the official. Although this problem can be solved by setting the MySQL database to be case insensitive.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-773779024
Hi @lazyboyl ,
I got your point, and thanks for your feedback. Actually, it is worthy work to do but will take plenty of time to go through the whole project and unify the case sensitive issue.
BTW @tuohai666 It is verified that #9270 has the same cause as this one, isn't 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-772296892
@terrymanu When I do an associative query, T_ORDER_ITEM is changed from uppercase to lowercase, but my T_ORDER is fine.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl closed issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl closed issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-773826308
Thank you for your answer. I hope this problem can be solved in the next version.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] JiekerTime commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
JiekerTime commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-772360500
Hi~ @lazyboyl ~ Thanks for your feedback!
In order to solve the problem conveniently, I have a few questions to confirm：
What operating system are you using? Can your SQL statement run successfully?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] JiekerTime removed a comment on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
JiekerTime removed a comment on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-772318613
Hi @lazyboyl ! Thanks for your feedback!
The problem you encountered seems to be similar to #9270. We have fixed this problem in the latest version (master branch).
If you want to take advantage of this feature, you can try to install the master branch, or wait for our upcoming release version!
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-772947989
My operating system is win7. My MySQL database is case sensitive. When I use shardingsphere's Association query, I directly report an error. If I can't execute the statement, I will directly delete the T in it_ order_ Change the item into upper case, which can be executed normally in the Navicat. Please take a look. Thank you @JiekerTime
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-773003791
Hi @lazyboyl ,
> When I do an associative query, T_ORDER_ITEM is changed from uppercase to lowercase, but my T_ORDER is fine.
It looks a little strange. SS does not differentiate tables, so why do two tables have different handling?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-773779024
Hi @lazyboyl ,
I got your point, and thanks for your feedback. Actually, it is worthy work to do but will take plenty of time to go through the whole project and unify the case sensitive issue.
BTW @tuohai666 It is verified that #9270 has the same cause as this one, isn't 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl closed issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl closed issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lazyboyl commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
lazyboyl commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-773091647
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] JiekerTime commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
JiekerTime commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-772318613
Hi @lazyboyl ! Thanks for your feedback!
The problem you encountered seems to be similar to #9270. We have fixed this problem in the latest version (master branch).
If you want to take advantage of this feature, you can try to install the master branch, or wait for our upcoming release version!
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] JiekerTime commented on issue #9291: The name of the associated query library table changes from uppercase to lowercase
Posted by GitBox <gi...@apache.org>.
JiekerTime commented on issue #9291:
URL: https://github.com/apache/shardingsphere/issues/9291#issuecomment-772975841
Hi~ @lazyboyl ！
First of all, it is clear that the windows environment is not case sensitive.Secondly, the design of shardingsphere 5.0.0-apha is not case sensitive. Even if you are using a Linux system, it is recommended to temporarily turn off case sensitivity.
About the SQL statement, your question is similar to #9270.
In our 5.0.0-apha, if join and ‘*’ are involved at the same time, it may cause some data loss problems in the result merging.We also found this problem and fixed it in the latest version (master branch).
My suggestion is to avoid using ‘*’ as much as possible, or try to install the master branch, or wait for our upcoming release version!
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org