zhfeng opened a new issue, #24780: URL: https://github.com/apache/shardingsphere/issues/24780
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? `5.3.2-SNAPSHOT` ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? `ShardingSphere-JDBC` ### Expected behavior It should return the correct ResultSet with `ORDER BY`. ### Actual behavior It throws NPE ``` Caused by: java.sql.SQLException: Unknown exception: Cannot invoke "org.apache.shardingsphere.infra.metadata.database.schema.model.ShardingSphereTable.getColumns()" because "table" is null at org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62) at org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:62) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:248) at io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ... 48 more Caused by: java.lang.NullPointerException: Cannot invoke "org.apache.shardingsphere.infra.metadata.database.schema.model.ShardingSphereTable.getColumns()" because "table" is null at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitiveFromTables(OrderByValue.java:77) at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitive(OrderByValue.java:68) at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.<init>(OrderByValue.java:62) at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.orderResultSetsToQueue(OrderByStreamMergedResult.java:56) at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByStreamMergedResult.<init>(OrderByStreamMergedResult.java:50) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.build(ShardingDQLResultMerger.java:100) at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:71) at org.apache.shardingsphere.infra.merge.MergeEngine.executeMerge(MergeEngine.java:81) at org.apache.shardingsphere.infra.merge.MergeEngine.merge(MergeEngine.java:71) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.mergeQuery(ShardingSpherePreparedStatement.java:577) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:239) ... 50 more ``` ### Reason analyze (If you can) At last I find the root case is that shardingsphere does not get the column names during loading meta data from H2 database. The reason is related to `case sensitive` refer to H2 [document](http://www.h2database.com/html/grammar.html) #### Name With default settings unquoted names are converted to upper case. The maximum name length is 256 characters. Identifiers in H2 are case sensitive by default. Because unquoted names are converted to upper case, they can be written in any case anyway. When both quoted and unquoted names are used for the same identifier the quoted names must be written in upper case. Identifiers with lowercase characters can be written only as a quoted name, they aren't accessible with unquoted names. If **DATABASE_TO_UPPER** setting is set to **FALSE** the unquoted names aren't converted to upper case. If **DATABASE_TO_LOWER** setting is set to **TRUE** the unquoted names are converted to lower case instead. If **CASE_INSENSITIVE_IDENTIFIERS** setting is set to **TRUE** all identifiers are case insensitive. I think we need to investigate the codes in `H2SchemaMetaDataLoader` https://github.com/apache/shardingsphere/blob/a77642415d1fbb9e1e19484f1611f0299fa595ae/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoader.java#L140-L147 The possible fix is to compare with **UPPER** table name. ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. Create a table with ```sql create table t_account (account_id integer not null, status varchar(255), user_id integer not null, primary key (account_id))` ``` but the `TABLE_NAME` in `INFORMATION_SCHEMA` are all **UPPER**, so loading column meta data returns empty. ### Example codes for reproduce this issue (such as a github link). The current workaround is - setting `DATABASE_TO_UPPER` to `FALSE` in connection url, just like `jdbc:h2:tcp://localhost:9092/~/ds_0;DATABASE_TO_UPPER=FALSE` - quoate table name with `"` in create table sql -- 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]
