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/07/27 05:33:03 UTC
[GitHub] [shardingsphere] caol64 opened a new issue, #19594: COUNT( 1 ) Spaces ERROR with H2 database
caol64 opened a new issue, #19594:
URL: https://github.com/apache/shardingsphere/issues/19594
This issue is similar to https://github.com/apache/shardingsphere/issues/2036.
I used H2 and sharding-jdbc for unit tests. When the "COUNT SQL" has spaces between brackets, Errors will occur.
SQL example:
SELECT COUNT( 1 ) FROM table;
Error:
Can't find index: AggregationProjection(type=COUNT, innerExpression=( * ), alias=Optional.empty, databaseType=org.apache.shardingsphere.infra.database.type.dialect.MySQLDatabaseType@914adfb, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=( * ), alias=Optional.empty, databaseType=org.apache.shardingsphere.infra.database.type.dialect.MySQLDatabaseType@914adfb, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
at com.google.common.base.Preconditions.checkState(Preconditions.java:589)
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexForAggregationProjection(SelectStatementContext.java:230)
at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setIndexes(SelectStatementContext.java:222)
at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:63)
at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:78)
at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:67)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:487)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:218)
ShardingSphere JDBC Core version: 5.1.2
h2 version: 1.4.200
--
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] github-actions[bot] closed issue #19594: COUNT( 1 ) Spaces ERROR with H2 database
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #19594: COUNT( 1 ) Spaces ERROR with H2 database
URL: https://github.com/apache/shardingsphere/issues/19594
--
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] github-actions[bot] commented on issue #19594: COUNT( 1 ) Spaces ERROR with H2 database
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #19594:
URL: https://github.com/apache/shardingsphere/issues/19594#issuecomment-1272349194
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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 #19594: COUNT( 1 ) Spaces ERROR with H2 database
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #19594:
URL: https://github.com/apache/shardingsphere/issues/19594#issuecomment-1196285623
@caol64 Thank you for your feedback, I will check 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
Re: [I] COUNT( 1 ) Spaces ERROR with H2 database [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #19594:
URL: https://github.com/apache/shardingsphere/issues/19594#issuecomment-3636101130
## Understanding
- Only with H2 (1.4.200) + ShardingSphere-JDBC 5.1.2, SELECT COUNT( 1 ) (spaces inside parentheses) throws Can't find index... please add alias for aggregate selections; removing the spaces or adding an alias works.
## Root Cause
- ShardingSphere uses the raw expression as the column label for unnamed aggregates (e.g., COUNT( 1 ), with spaces). The H2 driver normalizes the label by removing the inner-space, returning COUNT(1). During result merge, ShardingSphere matches labels by exact text;
COUNT( 1 ) vs COUNT(1) mismatches, so the index isn’t found and the exception is raised. This is triggered by H2’s label normalization, not a general ShardingSphere issue.
## Analysis
- Other databases (MySQL, PostgreSQL, etc.) typically preserve the expression text, so the space difference doesn’t appear.
- H2 behavior per its functions handling can normalize names (official doc: https://h2database.com/html/advanced.html#functions). ShardingSphere’s design expects users to alias aggregate columns and doesn’t add special handling for driver-specific space normalization.
## Conclusion
- This is an H2-specific compatibility issue due to its label normalization. ShardingSphere does not plan extra adaptation for H2 here.
- Recommendations:
1. Add an alias to the aggregate, e.g., SELECT COUNT(1) AS cnt FROM t;, or
2. Remove the inner space, use COUNT(1).
- These avoid the error on H2; other databases are not affected.
--
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] COUNT( 1 ) Spaces ERROR with H2 database [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #19594: COUNT( 1 ) Spaces ERROR with H2 database
URL: https://github.com/apache/shardingsphere/issues/19594
--
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