You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2022/04/26 05:20:38 UTC
[shardingsphere] branch master updated: Fix NPE caused by column's case unmatched in PostgreSQLComDescribeExecutor (#17111)
This is an automated email from the ASF dual-hosted git repository.
zhaojinchao pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 13dbedc1c5c Fix NPE caused by column's case unmatched in PostgreSQLComDescribeExecutor (#17111)
13dbedc1c5c is described below
commit 13dbedc1c5ce5b8d3174322c332d586ae179ae51
Author: 吴伟杰 <wu...@apache.org>
AuthorDate: Tue Apr 26 13:20:27 2022 +0800
Fix NPE caused by column's case unmatched in PostgreSQLComDescribeExecutor (#17111)
* Fix NPE caused by unmatched case of column name in PostgreSQLComDescribeExecutor
* Complete tests for PostgreSQLComDescribeExecutor
---
.../postgresql/constant/PostgreSQLErrorCode.java | 1 +
.../describe/PostgreSQLComDescribeExecutor.java | 24 +++++++++-
.../PostgreSQLComDescribeExecutorTest.java | 53 ++++++++++++++++++++++
3 files changed, 76 insertions(+), 2 deletions(-)
diff --git a/shardingsphere-db-protocol/shardingsphere-db-protocol-postgresql/src/main/java/org/apache/shardingsphere/db/protocol/postgresql/constant/PostgreSQLErrorCode.java b/shardingsphere-db-protocol/shardingsphere-db-protocol-postgresql/src/main/java/org/apache/shardingsphere/db/protocol/postgresql/constant/PostgreSQLErrorCode.java
index 86d64a75714..723a4893960 100644
--- a/shardingsphere-db-protocol/shardingsphere-db-protocol-postgresql/src/main/java/org/apache/shardingsphere/db/protocol/postgresql/constant/PostgreSQLErrorCode.java
+++ b/shardingsphere-db-protocol/shardingsphere-db-protocol-postgresql/src/main/java/org/apache/shardingsphere/db/protocol/postgresql/constant/PostgreSQLErrorCode.java
@@ -51,6 +51,7 @@ public enum PostgreSQLErrorCode {
INVALID_PASSWORD("28P01", "invalid_password"),
INVALID_CATALOG_NAME("3D000", "invalid_catalog_name"),
INVALID_SCHEMA_NAME("3F000", "invalid_schema_name"),
+ UNDEFINED_COLUMN("42703", "undefined_column"),
SYSTEM_ERROR("58000", "system_error");
private final String errorCode;
diff --git a/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutor.java b/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutor.java
index 7903b5f3956..9722251984d 100644
--- a/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutor.java
+++ b/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutor.java
@@ -19,6 +19,7 @@ package org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extend
import lombok.RequiredArgsConstructor;
import org.apache.shardingsphere.db.protocol.packet.DatabasePacket;
+import org.apache.shardingsphere.db.protocol.postgresql.constant.PostgreSQLErrorCode;
import org.apache.shardingsphere.db.protocol.postgresql.packet.PostgreSQLPacket;
import org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.PostgreSQLColumnDescription;
import org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.PostgreSQLNoDataPacket;
@@ -63,6 +64,7 @@ import java.util.ListIterator;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
+import java.util.TreeMap;
import java.util.stream.Collectors;
/**
@@ -111,7 +113,7 @@ public final class PostgreSQLComDescribeExecutor implements CommandExecutor {
tryDescribePreparedStatementByJDBC(preparedStatement);
}
- private void describeInsertStatementByShardingSphereMetaData(final PostgreSQLPreparedStatement preparedStatement) {
+ private void describeInsertStatementByShardingSphereMetaData(final PostgreSQLPreparedStatement preparedStatement) throws SQLException {
if (!preparedStatement.describeRows().isPresent()) {
// TODO Consider the SQL `insert into table (col) values ($1) returning id`
preparedStatement.setRowDescription(PostgreSQLNoDataPacket.getInstance());
@@ -128,6 +130,7 @@ public final class PostgreSQLComDescribeExecutor implements CommandExecutor {
String logicTableName = insertStatement.getTable().getTableName().getIdentifier().getValue();
TableMetaData tableMetaData = ProxyContext.getInstance().getMetaData(databaseName).getDefaultSchema().get(logicTableName);
Map<String, ColumnMetaData> columnMetaData = tableMetaData.getColumns();
+ Map<String, ColumnMetaData> caseInsensitiveColumnMetaData = null;
List<String> columnNames;
if (insertStatement.getColumns().isEmpty()) {
columnNames = new ArrayList<>(tableMetaData.getColumns().keySet());
@@ -149,7 +152,18 @@ public final class PostgreSQLComDescribeExecutor implements CommandExecutor {
continue;
}
String columnName = columnNames.get(columnIndex);
- PostgreSQLColumnType parameterType = PostgreSQLColumnType.valueOfJDBCType(columnMetaData.get(columnName).getDataType());
+ ColumnMetaData column = columnMetaData.get(columnName);
+ if (null == column) {
+ if (null == caseInsensitiveColumnMetaData) {
+ caseInsensitiveColumnMetaData = convertToCaseInsensitiveColumnMetaDataMap(columnMetaData);
+ }
+ column = caseInsensitiveColumnMetaData.get(columnName);
+ }
+ if (null == column) {
+ String reason = String.format("Column \"%s\" of relation \"%s\" does not exist. Please check the SQL or execute REFRESH TABLE METADATA.", columnName, logicTableName);
+ throw new SQLException(reason, PostgreSQLErrorCode.UNDEFINED_COLUMN.getErrorCode());
+ }
+ PostgreSQLColumnType parameterType = PostgreSQLColumnType.valueOfJDBCType(column.getDataType());
preparedStatement.getParameterTypes().set(parameterMarkerIndex++, parameterType);
}
}
@@ -166,6 +180,12 @@ public final class PostgreSQLComDescribeExecutor implements CommandExecutor {
return unspecifiedTypeParameterIndexes;
}
+ private Map<String, ColumnMetaData> convertToCaseInsensitiveColumnMetaDataMap(final Map<String, ColumnMetaData> columnMetaDataMap) {
+ Map<String, ColumnMetaData> result = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
+ result.putAll(columnMetaDataMap);
+ return result;
+ }
+
private void tryDescribePreparedStatementByJDBC(final PostgreSQLPreparedStatement preparedStatement) throws SQLException {
if (!(connectionSession.getBackendConnection() instanceof JDBCBackendConnection)) {
return;
diff --git a/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutorTest.java b/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutorTest.java
index 96b1abe09bc..9427f0e6b1f 100644
--- a/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutorTest.java
+++ b/shardingsphere-proxy/shardingsphere-proxy-frontend/shardingsphere-proxy-frontend-postgresql/src/test/java/org/apache/shardingsphere/proxy/frontend/postgresql/command/query/extended/describe/PostgreSQLComDescribeExecutorTest.java
@@ -19,6 +19,7 @@ package org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extend
import lombok.SneakyThrows;
import org.apache.shardingsphere.db.protocol.packet.DatabasePacket;
+import org.apache.shardingsphere.db.protocol.postgresql.constant.PostgreSQLErrorCode;
import org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.PostgreSQLColumnDescription;
import org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.PostgreSQLNoDataPacket;
import org.apache.shardingsphere.db.protocol.postgresql.packet.command.query.PostgreSQLParameterDescriptionPacket;
@@ -189,6 +190,58 @@ public final class PostgreSQLComDescribeExecutorTest {
assertThat(actualPacketsIterator.next(), is(PostgreSQLNoDataPacket.getInstance()));
}
+ @Test
+ public void assertDescribePreparedStatementInsertWithCaseInsensitiveColumns() throws SQLException {
+ when(packet.getType()).thenReturn('S');
+ final String statementId = "S_2";
+ when(packet.getName()).thenReturn(statementId);
+ final int connectionId = 1;
+ when(connectionSession.getConnectionId()).thenReturn(connectionId);
+ String sql = "insert into t_order (iD, k, c, PaD) values (1, ?, ?, ?), (?, 2, ?, '')";
+ SQLStatement sqlStatement = SQL_PARSER_ENGINE.parse(sql, false);
+ PostgreSQLPreparedStatementRegistry.getInstance().register(connectionId);
+ List<PostgreSQLColumnType> parameterTypes = new ArrayList<>(sqlStatement.getParameterCount());
+ for (int i = 0; i < sqlStatement.getParameterCount(); i++) {
+ parameterTypes.add(PostgreSQLColumnType.POSTGRESQL_TYPE_UNSPECIFIED);
+ }
+ PostgreSQLPreparedStatementRegistry.getInstance().register(connectionId, statementId, sql, sqlStatement, parameterTypes);
+ Collection<DatabasePacket<?>> actualPackets = executor.execute();
+ assertThat(actualPackets.size(), is(2));
+ Iterator<DatabasePacket<?>> actualPacketsIterator = actualPackets.iterator();
+ PostgreSQLParameterDescriptionPacket actualParameterDescription = (PostgreSQLParameterDescriptionPacket) actualPacketsIterator.next();
+ PostgreSQLPacketPayload mockPayload = mock(PostgreSQLPacketPayload.class);
+ actualParameterDescription.write(mockPayload);
+ verify(mockPayload).writeInt2(5);
+ verify(mockPayload, times(2)).writeInt4(23);
+ verify(mockPayload, times(3)).writeInt4(18);
+ assertThat(actualPacketsIterator.next(), is(PostgreSQLNoDataPacket.getInstance()));
+ }
+
+ @Test
+ public void assertDescribePreparedStatementInsertWithUndefinedColumns() {
+ when(packet.getType()).thenReturn('S');
+ final String statementId = "S_2";
+ when(packet.getName()).thenReturn(statementId);
+ final int connectionId = 1;
+ when(connectionSession.getConnectionId()).thenReturn(connectionId);
+ String sql = "insert into t_order (undefined_column, k, c, pad) values (1, ?, ?, ?), (?, 2, ?, '')";
+ SQLStatement sqlStatement = SQL_PARSER_ENGINE.parse(sql, false);
+ PostgreSQLPreparedStatementRegistry.getInstance().register(connectionId);
+ List<PostgreSQLColumnType> parameterTypes = new ArrayList<>(sqlStatement.getParameterCount());
+ for (int i = 0; i < sqlStatement.getParameterCount(); i++) {
+ parameterTypes.add(PostgreSQLColumnType.POSTGRESQL_TYPE_UNSPECIFIED);
+ }
+ PostgreSQLPreparedStatementRegistry.getInstance().register(connectionId, statementId, sql, sqlStatement, parameterTypes);
+ SQLException actual = null;
+ try {
+ executor.execute();
+ } catch (final SQLException ex) {
+ actual = ex;
+ }
+ assertThat(actual.getSQLState(), is(PostgreSQLErrorCode.UNDEFINED_COLUMN.getErrorCode()));
+ assertThat(actual.getMessage(), is("Column \"undefined_column\" of relation \"t_order\" does not exist. Please check the SQL or execute REFRESH TABLE METADATA."));
+ }
+
@Test
public void assertDescribeSelectPreparedStatement() throws SQLException {
when(packet.getType()).thenReturn('S');