You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "kaid-89757 (via GitHub)" <gi...@apache.org> on 2023/06/29 08:09:36 UTC
[GitHub] [shardingsphere] kaid-89757 opened a new issue, #26688: Duplicate groups due to
kaid-89757 opened a new issue, #26688:
URL: https://github.com/apache/shardingsphere/issues/26688
## Bug Report
Duplicate groups founded, due to the incorrect merge.
### Which version of ShardingSphere did you use?
4.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
from table_A order by group_name:
| group_name | count |
| --- | --- |
| A | 1 |
| B | 2 |
| C | 3 |
| D | 4 |
from table_B order by group_name:
| group_name | count |
| --- | --- |
| A | 1 |
| b | 2 |
| C | 3 |
| D | 4 |
the result should be:
| group_name | count |
| --- | --- |
| A | 2 |
| B | 2 |
| C | 6 |
| D | 8 |
| b | 2 |
### Actual behavior
| group_name | count |
| --- | --- |
| A | 2 |
| B | 2 |
| C | 3 |
| D | 4 |
| b | 2 |
| C | 3 |
| D | 4 |
### Reason analyze (If you can)
the comparison result of the priority queue in class [OrderByStreamMergedResult](https://github.com/apache/shardingsphere/blob/8fd2507f4a7e18831f001687b118ad55ed57c307/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/orderby/OrderByStreamMergedResult.java#L42) is different from the result of postgreSQL, as the [getOrderValuesCaseSensitiveFromTables](https://github.com/apache/shardingsphere/blob/96375f420074a8a4cba5307f1a308f3a87b3c7bf/sharding-core/sharding-core-merge/src/main/java/org/apache/shardingsphere/sharding/merge/dql/orderby/OrderByValue.java#L70) in OrderByValue always returns true, it should be false as 'b' > 'C' according to the resultSet of PG, e.g.
`select * from (values ('A'),('b'),('C')) as a(test) order by test`
| test |
| --- |
| A |
| b |
| C |
I solved this issue by forcing the `order by` case-sensitive, is there any other way? thx
`select * from (values ('A'),('b'),('C')) as a(test) order by test collate "C"`
| test |
| --- |
| A |
| C |
| b |
--
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] RaigorJiang commented on issue #26688: Duplicate groups due to
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #26688:
URL: https://github.com/apache/shardingsphere/issues/26688#issuecomment-1617749549
Hi @kaid-89757
Version 4.1.1 is too old, please try the latest version 5.3.2
--
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] terrymanu commented on issue #26688: Duplicate groups due to
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #26688:
URL: https://github.com/apache/shardingsphere/issues/26688#issuecomment-1622200522
The version 4.x is out of service, please try 5.x
--
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] terrymanu closed issue #26688: Duplicate groups due to
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #26688: Duplicate groups due to
URL: https://github.com/apache/shardingsphere/issues/26688
--
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] kaid-89757 commented on issue #26688: Duplicate groups due to
Posted by "kaid-89757 (via GitHub)" <gi...@apache.org>.
kaid-89757 commented on issue #26688:
URL: https://github.com/apache/shardingsphere/issues/26688#issuecomment-1619828124
> Hi @kaid-89757 Version 4.1.1 is too old, please try the latest version 5.3.2
Thanks for your reply, I tried the 5.3.2, but the issue still exists.
Here's my test code, you can reproduce this issue using an UTF8 encoded PG table.
```sql
CREATE TABLE sample_1
(
"name" varchar NULL
);
CREATE TABLE sample_2
(
"name" varchar NULL
);
INSERT INTO sample_1 ("name")
VALUES ('A');
INSERT INTO sample_2 ("name")
VALUES ('a'),
('A');
```
```java
public class GroupSample {
private static DataSource buildDatasource() throws SQLException {
ModeConfiguration modeConfig = new ModeConfiguration("Standalone", new StandalonePersistRepositoryConfiguration("JDBC", new Properties()));
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setJdbcUrl("jdbc:postgresql://localhost:5432/crt?currentSchema=public&useUnicode=true&characterEncoding=utf-8");
hikariConfig.setUsername("postgres");
hikariConfig.setPassword("1qaz@WSX");
Map<String, DataSource> dataSourceMap = Collections.singletonMap("ds", new HikariDataSource(hikariConfig));
ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
shardingRuleConfiguration.setTables(Collections.singletonList(new ShardingTableRuleConfiguration("sample", "ds.sample_1,ds.sample_2")));
return ShardingSphereDataSourceFactory.createDataSource(modeConfig, dataSourceMap, Collections.singletonList(shardingRuleConfiguration), new Properties());
}
public static void main(String[] args) throws SQLException {
DataSource dataSource = buildDatasource();
try (
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()
) {
ResultSet resultSet = statement.executeQuery("select name, count(*) as cnt from sample group by name");
while (resultSet.next()) {
System.out.printf("%s -> %s%n", resultSet.getString("name"), resultSet.getString("cnt"));
}
}
}
}
```
Here are the results:
```
A -> 1
a -> 1
A -> 1
```
--
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