You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Afsalmc (via GitHub)" <gi...@apache.org> on 2024/01/24 17:28:15 UTC
[I] Wrong output when query contains both limit and group by [shardingsphere]
Afsalmc opened a new issue, #29841:
URL: https://github.com/apache/shardingsphere/issues/29841
## Bug Report
### Which version of ShardingSphere did you use?
5.4.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Query returns accurate results.
### Actual behavior
Actual Results, not number of rows returned depends on the limit specified after group by keyword.
### Reason analyze (If you can)
If the user entered query is :
`SELECT COUNT(distinct Id2) C1 FROM ACCOUNT GROUP BY Id1 LIMIT 10`
before querying in individual shards, the query gets converted into
`SELECT distinct Id2 C1 FROM ACCOUNT LIMIT 10`
hence the combined output depends on the limit value, limit value no longer has just the control over the number of rows returned but it is influencing the actual data.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
More than one Shards to reproduce 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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Wrong output when query contains limit, group by and distinct combination [shardingsphere]
Posted by "Afsalmc (via GitHub)" <gi...@apache.org>.
Afsalmc commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-1958425446
@strongduanmu Forgot to mention that I am using java API to add default sharding strategy.
` shardingRuleConfig.setDefaultDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("Accountid", "shardingLogic"));`
shardingLogic is a custom sharding Logic
```
public final class ShardingLogic implements StandardShardingAlgorithm<Integer> {
private Map<Integer,String> shardMap = new HashMap<>();
private shardMapLoader shardMapLoader;
private AbstractConfiguration config;
public ShardingLogic() throws ConfigurationException {
config = Main.getConfig();
var shardConnection = config.getString("shardMapJdbcUrl");
var shardUser = config.getString("shardMapUserName");
var shardPass = config.getString("shardMapPass");
shardMapLoader = new shardMapLoader(shardConnection,shardUser,shardPass);
shardMap = shardMapLoader.LoadtoJavaMap();
}
@Override
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) {
for (String each : availableTargetNames) {
if(!shardMap.containsKey(shardingValue.getValue()))
break;
if(availableTargetNames.size() ==1){
return each;
}
var dbSource = shardMap.get(shardingValue.getValue());
if(!each.contains(">")){
for (var dataSources: each.split("\\|")) {
if(dbSource.equals(dataSources)){
return each;
}
}
}
var shardingSchema = shardingValue.getLogicTableName().split("__",2)[0];
var targetSchemaSet = each.split(">")[0];
String[] targetSchemas = null != config.getString(targetSchemaSet+"Schema") ? config.getString(targetSchemaSet+"Schema").split(",") : new String[]{""};
var isMatchingSchema = (null !=shardingSchema) && Arrays.asList(targetSchemas).contains(shardingSchema);
if(isMatchingSchema){
var targetSources = each.split(">")[1].split("\\|");
for(var targetSource:targetSources){
if(dbSource.equals(targetSource))
return each;
}
}
}
return null;
}
@Override
public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Integer> shardingValue) {
return availableTargetNames;
}
}
```
--
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] Wrong output when query contains limit, group by and distinct combination [shardingsphere]
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-1958414870
> @strongduanmu I am still learning how it works. I can reproduce this with MySQL schemas also.
@Afsalmc Thank you for your feedback. In addition, I found that your sharding rules do not have a sharding strategy configured, which may cause problems, can you add the sharding strategy and try again?
--
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] Wrong output when query contains both limit and group by [shardingsphere]
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-1911214051
Hi @Afsalmc, thank you for your feedback. Can you provide your configuration and table init sql to help us reproduce this bug?
--
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] Wrong output when query contains both limit and group by [shardingsphere]
Posted by "Afsalmc (via GitHub)" <gi...@apache.org>.
Afsalmc commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-1912391998
@strongduanmu
```
dataSources:
db0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.microsoft.sqlserver
jdbcUrl: jdbc:sqlserver://db1.database.windows.net;databaseName=db1;loginTimeout=300;encrypt=false;
username: adm
db1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.microsoft.sqlserver
jdbcUrl: jdbc:sqlserver://db11.database.windows.net;databaseName=db11;loginTimeout=300;encrypt=false;
username: adm
rules:
- !SQL_FEDERATION
sqlFederationEnabled: true
executionPlanCache:
initialCapacity: 1000
maximumSize: 4000
- !SHARDING
tables:
ACCOUNT:
actualDataNodes: dbo.Account,db1.Account
```
I haven't used any init SQL. The schema was already defined and I just connected using shardingsphere jdbc. I also noticed that I'm only getting this weird behavior when the query contains Distinct keyword :
`select count(DISTINCT accountid)
as c,Stamp from dbo.Account
group by stamp`
Not when
`select count(accountid)
as c,Stamp from Account
group by stamp`
--
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] Wrong output when query contains limit, group by and distinct combination [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-2016590121
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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] Wrong output when query contains limit, group by and distinct combination [shardingsphere]
Posted by "Afsalmc (via GitHub)" <gi...@apache.org>.
Afsalmc commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-1957762563
@strongduanmu I am still learning how it works. I can reproduce this with MySQL schemas also.
--
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] Wrong output when query contains limit, group by and distinct combination [shardingsphere]
Posted by "Afsalmc (via GitHub)" <gi...@apache.org>.
Afsalmc commented on issue #29841:
URL: https://github.com/apache/shardingsphere/issues/29841#issuecomment-1925296183
@strongduanmu Do you want any other info from me ?
--
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