After I tested the SQL with label conflicts like `SELECT order_id as USER_ID, user_id,status FROM t_order_0` and looked at the native JDBC source code of the three databases, I found that native JDBC transfer labels to the indexes in a case-insensitive way, although the resultset metadata may return case-sensitive labels.
For Oracle, traverse an array of Accessor and find the first column index with the same name as the input label (ignoring case). For MySQL, the ResultSet will look up the index in the order of columnLabel, columnName, fullColumnName by creating three `TreeMap` with `String.CASE_INSENSITIVE_ORDER`. Because the key of TreeMap is case-insensitive, so the result is same with Oracle. And for postgreSQL, there is one `HashMap` to map lower label and index. When transfer label to index, PG will find native label first from map. If not found try to find lower label from map. last to find upper label from map. But in postgreSQL, there is a parameter named 'disableColumnSanitiser', if it's true, the `HashMap` will save native label and index instead of lower label and index. So the result of PG is same with MySQL and Oracle default, but different when disableColumnSanitiser is true Based on the test and research results, I think ShardingSphere should solve this problem with a case-insensitive solution, consistent with most native JDBC. Any suggestions or ideas for this issue? ------------------ Yi Yang (Sion) Apache ShardingSphere ------------------ Original ------------------ From: "????????????"<[email protected]>; Date: Mon, Apr 8, 2019 07:44 PM To: "dev"<[email protected]>; Subject: [DISCUSS] ResultSetMetaData labels case sensitivity problem for different databases Hi everybody, When I try to fix issue#2152[1], I can't find the best way to solve it. Because the case of the label will change with the database type. For my test, in MySQL, the result is defined by the case of the query field in SQL. example: select id from tb --> id select ID from tb --> ID In Oracle, whatever the field is in SQL, the result must be upper case. example: select id from tb --> ID (will cause issue#2152[1]) select ID from tb --> ID And in pg, the result is lower case if SQL without double quotes example: select id from tb --> id select ID from tb --> id (will cause issue#2152[1]) But if the field in SQL with double quotes, the result will change example: select "ID" from tb --> ID There are two solutions, 1. Cache the real labels got from JDBC, and ignore case when get index from label. 2. Do upper or lower for Cached labels according to database type. The first solution may lose field for some SQL like 'select xxx as STATUS, status from tb' . The second solution may return error result for method `QueryResultMetaData.getColumnLabel`. Any advise or new solution for this issue? [1] https://github.com/apache/incubator-shardingsphere/issues/2152 ------------------ Yi Yang (Sion) Apache ShardingSphere
