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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
