This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 d015759 Fix #14101 Show tables statement return wrong result when
mysql is case sensitive (#14301)
d015759 is described below
commit d01575904e7ba7e060e95a2c889d652e90740093
Author: MingxingLAI <[email protected]>
AuthorDate: Fri Dec 24 19:24:24 2021 +0800
Fix #14101 Show tables statement return wrong result when mysql is case
sensitive (#14301)
* Fix invalid unit tests in ShowTablesExecutorTest
* refactor ShowTablesExecutorTest for adding more unit test
* Add unit test for ShowTablesExecutor
* Fix #14101 Show tables statement return wrong result when mysql is case
sensitive
* Fix checkstyle error
---
.../admin/mysql/executor/ShowTablesExecutor.java | 3 +-
.../mysql/executor/ShowTablesExecutorTest.java | 114 ++++++++++++++++++---
2 files changed, 101 insertions(+), 16 deletions(-)
diff --git
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutor.java
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutor.java
index 3123258..bbc7897 100644
---
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutor.java
+++
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutor.java
@@ -27,6 +27,7 @@ import
org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.ra
import
org.apache.shardingsphere.infra.executor.sql.execute.result.query.type.memory.row.MemoryQueryResultDataRow;
import org.apache.shardingsphere.infra.merge.result.MergedResult;
import
org.apache.shardingsphere.infra.merge.result.impl.transparent.TransparentMergedResult;
+import org.apache.shardingsphere.infra.metadata.schema.model.TableMetaData;
import org.apache.shardingsphere.proxy.backend.context.ProxyContext;
import org.apache.shardingsphere.proxy.backend.session.ConnectionSession;
import
org.apache.shardingsphere.proxy.backend.text.admin.executor.DatabaseAdminQueryExecutor;
@@ -76,7 +77,7 @@ public final class ShowTablesExecutor implements
DatabaseAdminQueryExecutor {
}
private Collection<String> getAllTableNames(final String schemaName) {
- Collection<String> allTableNames =
ProxyContext.getInstance().getMetaData(schemaName).getSchema().getAllTableNames();
+ Collection<String> allTableNames =
ProxyContext.getInstance().getMetaData(schemaName).getSchema().getTables().values().stream().map(TableMetaData::getName).collect(Collectors.toList());
if (showTablesStatement.getFilter().isPresent()) {
Optional<String> pattern =
showTablesStatement.getFilter().get().getLike().map(each ->
SQLUtil.convertLikePatternToRegex(each.getPattern()));
return pattern.isPresent() ? allTableNames.stream().filter(each ->
each.matches(pattern.get())).collect(Collectors.toList()) : allTableNames;
diff --git
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutorTest.java
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutorTest.java
index 4957fd6..89bb0ba 100644
---
a/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutorTest.java
+++
b/shardingsphere-proxy/shardingsphere-proxy-backend/src/test/java/org/apache/shardingsphere/proxy/backend/text/admin/mysql/executor/ShowTablesExecutorTest.java
@@ -23,23 +23,30 @@ import
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine;
import
org.apache.shardingsphere.infra.federation.optimizer.context.OptimizerContext;
import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
import
org.apache.shardingsphere.infra.metadata.rule.ShardingSphereRuleMetaData;
+import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
+import org.apache.shardingsphere.infra.metadata.schema.model.TableMetaData;
import org.apache.shardingsphere.infra.metadata.user.Grantee;
import org.apache.shardingsphere.mode.manager.ContextManager;
import org.apache.shardingsphere.mode.metadata.MetaDataContexts;
import org.apache.shardingsphere.mode.metadata.persist.MetaDataPersistService;
import org.apache.shardingsphere.proxy.backend.context.ProxyContext;
import org.apache.shardingsphere.proxy.backend.session.ConnectionSession;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dal.ShowFilterSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dal.ShowLikeSegment;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLShowTablesStatement;
import org.junit.Before;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.SQLException;
+import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
+import java.util.Optional;
import java.util.Properties;
import static org.hamcrest.CoreMatchers.is;
+import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertThat;
import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
import static org.mockito.Mockito.mock;
@@ -49,11 +56,8 @@ public final class ShowTablesExecutorTest {
private static final String SCHEMA_PATTERN = "schema_%s";
- private ShowTablesExecutor showTablesExecutor;
-
@Before
public void setUp() throws NoSuchFieldException, IllegalAccessException {
- showTablesExecutor = new ShowTablesExecutor(new
MySQLShowTablesStatement());
Map<String, ShardingSphereMetaData> metaDataMap = getMetaDataMap();
Field contextManagerField =
ProxyContext.getInstance().getClass().getDeclaredField("contextManager");
contextManagerField.setAccessible(true);
@@ -65,23 +69,103 @@ public final class ShowTablesExecutorTest {
}
private Map<String, ShardingSphereMetaData> getMetaDataMap() {
- Map<String, ShardingSphereMetaData> result = new HashMap<>(10, 1);
- for (int i = 0; i < 10; i++) {
- ShardingSphereMetaData metaData =
mock(ShardingSphereMetaData.class, RETURNS_DEEP_STUBS);
- when(metaData.isComplete()).thenReturn(false);
- when(metaData.getResource().getDatabaseType()).thenReturn(new
MySQLDatabaseType());
- result.put(String.format(SCHEMA_PATTERN, i), metaData);
- }
- return result;
+ Map<String, TableMetaData> tables = new HashMap<>(4, 1);
+ tables.put("t_account", new TableMetaData("t_account"));
+ tables.put("t_account_bak", new TableMetaData("t_account_bak"));
+ tables.put("t_account_detail", new TableMetaData("t_account_detail"));
+ tables.put("t_test", new TableMetaData("T_TEST"));
+ ShardingSphereSchema schema = new ShardingSphereSchema(tables);
+ ShardingSphereMetaData metaData = mock(ShardingSphereMetaData.class,
RETURNS_DEEP_STUBS);
+ when(metaData.getSchema()).thenReturn(schema);
+ when(metaData.isComplete()).thenReturn(true);
+ when(metaData.getResource().getDatabaseType()).thenReturn(new
MySQLDatabaseType());
+ return Collections.singletonMap(String.format(SCHEMA_PATTERN, 0),
metaData);
+ }
+
+ @Test
+ public void assertShowTablesExecutorWithoutFilter() throws SQLException {
+ ShowTablesExecutor showTablesExecutor = new ShowTablesExecutor(new
MySQLShowTablesStatement());
+ showTablesExecutor.execute(mockConnectionSession());
+
assertThat(showTablesExecutor.getQueryResultMetaData().getColumnCount(), is(2));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("T_TEST"));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account"));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account_bak"));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account_detail"));
+ assertFalse(showTablesExecutor.getMergedResult().next());
+ }
+
+ @Test
+ public void assertShowTablesExecutorWithLikeFilter() throws SQLException {
+ MySQLShowTablesStatement showTablesStatement = new
MySQLShowTablesStatement();
+ ShowFilterSegment showFilterSegment = mock(ShowFilterSegment.class);
+ when(showFilterSegment.getLike()).thenReturn(Optional.of(new
ShowLikeSegment(0, 10, "t_account%")));
+ showTablesStatement.setFilter(showFilterSegment);
+ ShowTablesExecutor showTablesExecutor = new
ShowTablesExecutor(showTablesStatement);
+ showTablesExecutor.execute(mockConnectionSession());
+
assertThat(showTablesExecutor.getQueryResultMetaData().getColumnCount(), is(2));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account"));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account_bak"));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account_detail"));
+ assertFalse(showTablesExecutor.getMergedResult().next());
+ }
+
+ @Test
+ public void assertShowTablesExecutorWithSpecificTable() throws
SQLException {
+ MySQLShowTablesStatement showTablesStatement = new
MySQLShowTablesStatement();
+ ShowFilterSegment showFilterSegment = mock(ShowFilterSegment.class);
+ when(showFilterSegment.getLike()).thenReturn(Optional.of(new
ShowLikeSegment(0, 10, "t_account")));
+ showTablesStatement.setFilter(showFilterSegment);
+ ShowTablesExecutor showTablesExecutor = new
ShowTablesExecutor(showTablesStatement);
+ showTablesExecutor.execute(mockConnectionSession());
+
assertThat(showTablesExecutor.getQueryResultMetaData().getColumnCount(), is(2));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("t_account"));
+ assertFalse(showTablesExecutor.getMergedResult().next());
+ }
+
+ @Test
+ public void assertShowTablesExecutorWithExpectedUpperCase() throws
SQLException {
+ MySQLShowTablesStatement showTablesStatement = new
MySQLShowTablesStatement();
+ ShowFilterSegment showFilterSegment = mock(ShowFilterSegment.class);
+ when(showFilterSegment.getLike()).thenReturn(Optional.of(new
ShowLikeSegment(0, 10, "T_TEST")));
+ showTablesStatement.setFilter(showFilterSegment);
+ ShowTablesExecutor showTablesExecutor = new
ShowTablesExecutor(showTablesStatement);
+ showTablesExecutor.execute(mockConnectionSession());
+
assertThat(showTablesExecutor.getQueryResultMetaData().getColumnCount(), is(2));
+ showTablesExecutor.getMergedResult().next();
+ assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is("T_TEST"));
+ assertFalse(showTablesExecutor.getMergedResult().next());
+ }
+
+ @Test
+ public void assertShowTablesExecutorWithUnexpectedLowerCase() throws
SQLException {
+ MySQLShowTablesStatement showTablesStatement = new
MySQLShowTablesStatement();
+ ShowFilterSegment showFilterSegment = mock(ShowFilterSegment.class);
+ when(showFilterSegment.getLike()).thenReturn(Optional.of(new
ShowLikeSegment(0, 10, "t_test")));
+ showTablesStatement.setFilter(showFilterSegment);
+ ShowTablesExecutor showTablesExecutor = new
ShowTablesExecutor(showTablesStatement);
+ showTablesExecutor.execute(mockConnectionSession());
+
assertThat(showTablesExecutor.getQueryResultMetaData().getColumnCount(), is(2));
+ assertFalse(showTablesExecutor.getMergedResult().next());
}
@Test
- public void assertExecute() throws SQLException {
+ public void assertShowTablesExecutorWithUnexpectedUpperCase() throws
SQLException {
+ MySQLShowTablesStatement showTablesStatement = new
MySQLShowTablesStatement();
+ ShowFilterSegment showFilterSegment = mock(ShowFilterSegment.class);
+ when(showFilterSegment.getLike()).thenReturn(Optional.of(new
ShowLikeSegment(0, 10, "T_ACCOUNT")));
+ showTablesStatement.setFilter(showFilterSegment);
+ ShowTablesExecutor showTablesExecutor = new
ShowTablesExecutor(showTablesStatement);
showTablesExecutor.execute(mockConnectionSession());
assertThat(showTablesExecutor.getQueryResultMetaData().getColumnCount(), is(2));
- while (showTablesExecutor.getMergedResult().next()) {
- assertThat(showTablesExecutor.getMergedResult().getValue(1,
Object.class), is(1));
- }
+ assertFalse(showTablesExecutor.getMergedResult().next());
}
private ConnectionSession mockConnectionSession() {
