This is an automated email from the ASF dual-hosted git repository.
panjuan 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 bf14324 remove automatically added order by primary key clause
(#13402)
bf14324 is described below
commit bf143245090a3b18589402d81ccddc68eab2e5c7
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Tue Nov 2 12:26:13 2021 +0800
remove automatically added order by primary key clause (#13402)
---
.../orderby/engine/OrderByContextEngine.java | 60 +--------------
.../statement/dml/SelectStatementContext.java | 2 +-
.../orderby/engine/OrderByContextEngineTest.java | 89 +---------------------
.../cases/dql/dql-integration-test-cases.xml | 2 +-
.../cases/ral/dataset/empty_rules/preview_sql.xml | 80 +++++++++----------
.../ShardingSQLRewriterParameterizedTest.java | 2 +
.../resources/scenario/sharding/case/select.xml | 86 ++++++++++-----------
7 files changed, 93 insertions(+), 228 deletions(-)
diff --git
a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngine.java
b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngine.java
index abb16b1..ace7b9a 100644
---
a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngine.java
+++
b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngine.java
@@ -20,26 +20,16 @@ package
org.apache.shardingsphere.infra.binder.segment.select.orderby.engine;
import
org.apache.shardingsphere.infra.binder.segment.select.groupby.GroupByContext;
import
org.apache.shardingsphere.infra.binder.segment.select.orderby.OrderByContext;
import
org.apache.shardingsphere.infra.binder.segment.select.orderby.OrderByItem;
-import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
-import org.apache.shardingsphere.infra.metadata.schema.model.TableMetaData;
import org.apache.shardingsphere.sql.parser.sql.common.constant.OrderDirection;
-import org.apache.shardingsphere.sql.parser.sql.common.constant.QuoteCharacter;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.AggregationProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ColumnProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ColumnOrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.IndexOrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.OrderByItemSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
-import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
-import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLSelectStatement;
import java.util.LinkedList;
import java.util.List;
-import java.util.Optional;
/**
* Order by context engine.
@@ -49,21 +39,14 @@ public final class OrderByContextEngine {
/**
* Create order by context.
*
- * @param schema ShardingSphere schema
* @param selectStatement select statement
* @param groupByContext group by context
* @return order by context
*/
- public OrderByContext createOrderBy(final ShardingSphereSchema schema,
final SelectStatement selectStatement, final GroupByContext groupByContext) {
+ public OrderByContext createOrderBy(final SelectStatement selectStatement,
final GroupByContext groupByContext) {
if (!selectStatement.getOrderBy().isPresent() ||
selectStatement.getOrderBy().get().getOrderByItems().isEmpty()) {
- if (groupByContext.getItems().isEmpty() &&
selectStatement.getProjections().isDistinctRow()) {
- OrderByContext result =
createOrderByContextForDistinctRowWithoutGroupBy(selectStatement,
groupByContext);
- return null != result ? result :
getDefaultOrderByContextWithoutOrderBy(groupByContext);
- } else if (selectStatement instanceof MySQLSelectStatement) {
- Optional<OrderByContext> result =
createOrderByContextForMySQLSelectWithoutOrderBy(schema, selectStatement,
groupByContext);
- return result.orElseGet(() ->
getDefaultOrderByContextWithoutOrderBy(groupByContext));
- }
- return getDefaultOrderByContextWithoutOrderBy(groupByContext);
+ OrderByContext orderByItems =
createOrderByContextForDistinctRowWithoutGroupBy(selectStatement,
groupByContext);
+ return null != orderByItems ? orderByItems : new
OrderByContext(groupByContext.getItems(), !groupByContext.getItems().isEmpty());
}
List<OrderByItem> orderByItems = new LinkedList<>();
for (OrderByItemSegment each :
selectStatement.getOrderBy().get().getOrderByItems()) {
@@ -76,10 +59,6 @@ public final class OrderByContextEngine {
return new OrderByContext(orderByItems, false);
}
- private OrderByContext getDefaultOrderByContextWithoutOrderBy(final
GroupByContext groupByContext) {
- return new OrderByContext(groupByContext.getItems(),
!groupByContext.getItems().isEmpty());
- }
-
private OrderByContext
createOrderByContextForDistinctRowWithoutGroupBy(final SelectStatement
selectStatement, final GroupByContext groupByContext) {
if (groupByContext.getItems().isEmpty() &&
selectStatement.getProjections().isDistinctRow()) {
int index = 0;
@@ -99,37 +78,4 @@ public final class OrderByContextEngine {
}
return null;
}
-
- private Optional<OrderByContext>
createOrderByContextForMySQLSelectWithoutOrderBy(final ShardingSphereSchema
schema, final SelectStatement selectStatement, final GroupByContext
groupByContext) {
- if (!isNeedProcessMySQLSelectWithoutOrderBy(selectStatement,
groupByContext)) {
- return Optional.empty();
- }
- int index = 0;
- List<OrderByItem> orderByItems = new LinkedList<>();
- TableMetaData tableMetaData = schema.get(((SimpleTableSegment)
selectStatement.getFrom()).getTableName().getIdentifier().getValue());
- if (null == tableMetaData) {
- return Optional.empty();
- }
- for (String each : tableMetaData.getPrimaryKeyColumns()) {
- ColumnSegment columnSegment = new ColumnSegment(0, 0, new
IdentifierValue(each, QuoteCharacter.NONE));
- OrderByItem item = new OrderByItem(new
ColumnOrderByItemSegment(columnSegment, OrderDirection.ASC));
- item.setIndex(index++);
- orderByItems.add(item);
- }
- return orderByItems.isEmpty() ? Optional.empty() : Optional.of(new
OrderByContext(orderByItems, true));
- }
-
- private boolean isNeedProcessMySQLSelectWithoutOrderBy(final
SelectStatement selectStatement, final GroupByContext groupByContext) {
- if (!groupByContext.getItems().isEmpty()) {
- return false;
- }
- TableSegment tableSegment = selectStatement.getFrom();
- if (null == tableSegment) {
- return false;
- }
- if (!(tableSegment instanceof SimpleTableSegment)) {
- return false;
- }
- return
selectStatement.getProjections().getProjections().stream().noneMatch(each ->
each instanceof AggregationProjectionSegment);
- }
}
diff --git
a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
index 02204ee..7b19227 100644
---
a/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
+++
b/shardingsphere-infra/shardingsphere-infra-binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java
@@ -85,7 +85,7 @@ public final class SelectStatementContext extends
CommonSQLStatementContext<Sele
tablesContext = new TablesContext(getAllSimpleTableSegments());
ShardingSphereSchema schema = getSchema(metaDataMap,
defaultSchemaName);
groupByContext = new
GroupByContextEngine().createGroupByContext(sqlStatement);
- orderByContext = new OrderByContextEngine().createOrderBy(schema,
sqlStatement, groupByContext);
+ orderByContext = new
OrderByContextEngine().createOrderBy(sqlStatement, groupByContext);
projectionsContext = new ProjectionsContextEngine(schema,
getDatabaseType())
.createProjectionsContext(getSqlStatement().getFrom(),
getSqlStatement().getProjections(), groupByContext, orderByContext);
paginationContext = new
PaginationContextEngine().createPaginationContext(sqlStatement,
projectionsContext, parameters);
diff --git
a/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngineTest.java
b/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngineTest.java
index 9045c02..9da929e 100644
---
a/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngineTest.java
+++
b/shardingsphere-infra/shardingsphere-infra-binder/src/test/java/org/apache/shardingsphere/infra/binder/segment/select/orderby/engine/OrderByContextEngineTest.java
@@ -18,16 +18,10 @@
package org.apache.shardingsphere.infra.binder.segment.select.orderby.engine;
import
org.apache.shardingsphere.infra.binder.segment.select.groupby.GroupByContext;
-import
org.apache.shardingsphere.infra.binder.segment.select.groupby.engine.GroupByContextEngine;
import
org.apache.shardingsphere.infra.binder.segment.select.orderby.OrderByContext;
import
org.apache.shardingsphere.infra.binder.segment.select.orderby.OrderByItem;
-import org.apache.shardingsphere.infra.metadata.schema.ShardingSphereSchema;
-import org.apache.shardingsphere.infra.metadata.schema.model.ColumnMetaData;
-import org.apache.shardingsphere.infra.metadata.schema.model.TableMetaData;
-import
org.apache.shardingsphere.sql.parser.sql.common.constant.AggregationType;
import org.apache.shardingsphere.sql.parser.sql.common.constant.OrderDirection;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.AggregationProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ColumnProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionsSegment;
@@ -35,10 +29,6 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.OrderBy
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ColumnOrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.IndexOrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.OrderByItemSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.JoinTableSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SubqueryTableSegment;
-import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableNameSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLSelectStatement;
@@ -48,20 +38,15 @@ import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.sql92.dml.SQL9
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.dml.SQLServerSelectStatement;
import org.junit.Test;
-import java.sql.Types;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
-import java.util.Optional;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.assertTrue;
-import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
-import static org.mockito.Mockito.mock;
-import static org.mockito.Mockito.when;
public final class OrderByContextEngineTest {
@@ -95,7 +80,7 @@ public final class OrderByContextEngineTest {
OrderByItem orderByItem2 = new OrderByItem(new
IndexOrderByItemSegment(1, 2, 2, OrderDirection.ASC, OrderDirection.DESC));
Collection<OrderByItem> orderByItems = Arrays.asList(orderByItem1,
orderByItem2);
GroupByContext groupByContext = new GroupByContext(orderByItems);
- OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(new ShardingSphereSchema(),
selectStatement, groupByContext);
+ OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(selectStatement, groupByContext);
assertThat(actualOrderByContext.getItems(), is(orderByItems));
assertTrue(actualOrderByContext.isGenerated());
}
@@ -132,7 +117,7 @@ public final class OrderByContextEngineTest {
OrderBySegment orderBySegment = new OrderBySegment(0, 1,
Arrays.asList(columnOrderByItemSegment, indexOrderByItemSegment1,
indexOrderByItemSegment2));
selectStatement.setOrderBy(orderBySegment);
GroupByContext emptyGroupByContext = new
GroupByContext(Collections.emptyList());
- OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(new ShardingSphereSchema(),
selectStatement, emptyGroupByContext);
+ OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(selectStatement, emptyGroupByContext);
OrderByItem expectedOrderByItem1 = new
OrderByItem(columnOrderByItemSegment);
OrderByItem expectedOrderByItem2 = new
OrderByItem(indexOrderByItemSegment1);
expectedOrderByItem2.setIndex(2);
@@ -176,79 +161,11 @@ public final class OrderByContextEngineTest {
projectionsSegment.getProjections().addAll(list);
selectStatement.setProjections(projectionsSegment);
GroupByContext groupByContext = new
GroupByContext(Collections.emptyList());
- OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(new ShardingSphereSchema(),
selectStatement, groupByContext);
+ OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(selectStatement, groupByContext);
assertThat(actualOrderByContext.getItems().size(), is(list.size()));
List<OrderByItem> items = (List<OrderByItem>)
actualOrderByContext.getItems();
assertThat(((ColumnOrderByItemSegment)
items.get(0).getSegment()).getColumn(),
is(columnProjectionSegment1.getColumn()));
assertThat(((ColumnOrderByItemSegment)
items.get(1).getSegment()).getColumn(),
is(columnProjectionSegment2.getColumn()));
assertTrue(actualOrderByContext.isGenerated());
}
-
- @Test
- public void
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnPlainQuery() {
- SelectStatement selectStatement = mock(MySQLSelectStatement.class,
RETURNS_DEEP_STUBS);
- when(selectStatement.getFrom()).thenReturn(new SimpleTableSegment(new
TableNameSegment(0, 1, new IdentifierValue("t_order"))));
-
when(selectStatement.getProjections().getProjections()).thenReturn(Collections.emptyList());
- GroupByContext groupByContext = new
GroupByContext(Collections.emptyList());
- OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(getShardingSphereSchemaForMySQLSelectWithoutOrderBy(),
selectStatement, groupByContext);
- assertTrue(actualOrderByContext.isGenerated());
- assertThat(actualOrderByContext.getItems().size(), is(1));
- ColumnOrderByItemSegment actualItemSegment =
(ColumnOrderByItemSegment)
actualOrderByContext.getItems().iterator().next().getSegment();
- assertThat(actualItemSegment.getColumn().getIdentifier().getValue(),
is("order_id"));
- }
-
- @Test
- public void
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnOrderByQuery() {
- SelectStatement selectStatement = mock(MySQLSelectStatement.class,
RETURNS_DEEP_STUBS);
- when(selectStatement.getFrom()).thenReturn(new SimpleTableSegment(new
TableNameSegment(0, 1, new IdentifierValue("t_order"))));
- when(selectStatement.getOrderBy()).thenReturn(Optional.of(new
OrderBySegment(0, 1, Collections.singleton(new ColumnOrderByItemSegment(
- new ColumnSegment(0, 1, new IdentifierValue("order_id")),
OrderDirection.ASC)))));
- GroupByContext groupByContext = new
GroupByContext(Collections.emptyList());
- OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(getShardingSphereSchemaForMySQLSelectWithoutOrderBy(),
selectStatement, groupByContext);
- assertFalse(actualOrderByContext.isGenerated());
- assertThat(actualOrderByContext.getItems().size(), is(1));
- ColumnOrderByItemSegment actualItemSegment =
(ColumnOrderByItemSegment)
actualOrderByContext.getItems().iterator().next().getSegment();
- assertThat(actualItemSegment.getColumn().getIdentifier().getValue(),
is("order_id"));
- }
-
- @Test
- public void
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnCountQuery() {
- SelectStatement selectStatement = mock(MySQLSelectStatement.class,
RETURNS_DEEP_STUBS);
- when(selectStatement.getFrom()).thenReturn(new SimpleTableSegment(new
TableNameSegment(0, 1, new IdentifierValue("t_order"))));
- ProjectionsSegment projectionsSegment = new ProjectionsSegment(0, 1);
- projectionsSegment.setDistinctRow(false);
- projectionsSegment.getProjections().add(new
AggregationProjectionSegment(0, 1, AggregationType.COUNT, "COUNT(1)"));
- when(selectStatement.getProjections()).thenReturn(projectionsSegment);
-
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnUnsupportedQuery(selectStatement);
- }
-
- @Test
- public void
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnSubQuery() {
- SelectStatement selectStatement = mock(MySQLSelectStatement.class,
RETURNS_DEEP_STUBS);
-
when(selectStatement.getFrom()).thenReturn(mock(SubqueryTableSegment.class));
-
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnUnsupportedQuery(selectStatement);
- }
-
- @Test
- public void
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnJoinQuery() {
- SelectStatement selectStatement = mock(MySQLSelectStatement.class,
RETURNS_DEEP_STUBS);
-
when(selectStatement.getFrom()).thenReturn(mock(JoinTableSegment.class));
-
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnUnsupportedQuery(selectStatement);
- }
-
- private void
assertCreateOrderByContextForMySQLSelectWithoutOrderByOnUnsupportedQuery(final
SelectStatement selectStatement) {
- GroupByContext groupByContext = new
GroupByContextEngine().createGroupByContext(selectStatement);
- OrderByContext actualOrderByContext = new
OrderByContextEngine().createOrderBy(getShardingSphereSchemaForMySQLSelectWithoutOrderBy(),
selectStatement, groupByContext);
- assertFalse(actualOrderByContext.isGenerated());
- assertTrue(actualOrderByContext.getItems().isEmpty());
- }
-
- private ShardingSphereSchema
getShardingSphereSchemaForMySQLSelectWithoutOrderBy() {
- TableMetaData orderTable = new TableMetaData("t_order", Arrays.asList(
- new ColumnMetaData("order_id", Types.INTEGER, true, true, false),
- new ColumnMetaData("user_id", Types.INTEGER, false, false, false),
- new ColumnMetaData("status", Types.VARCHAR, false, false, false)
- ), Collections.emptyList());
- return new ShardingSphereSchema(Collections.singletonMap("t_order",
orderTable));
- }
}
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index 74f9c4f..364127e 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -232,7 +232,7 @@
</test-case>
<!-- TODO FIXED ME PostgreSQL default order by collate -->
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? OR status = ?"
db-types="H2,MySQL,Oracle,SQLServer"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? OR status = ?
ORDER BY order_id" db-types="H2,MySQL,Oracle,SQLServer"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int, init:String"
expected-data-file="select_or_with_none_sharding_columns.xml" />
</test-case>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
index e188b0f..7426d5e 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/ral/dataset/empty_rules/preview_sql.xml
@@ -20,44 +20,44 @@
<column name="data_source_name"/>
<column name="sql"/>
</metadata>
- <row values="encrypt_write_ds_0| select * from t_user_item_0 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_0| select * from t_user_item_10 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_0| select * from t_user_item_20 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_0| select * from t_user_item_30 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_1 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_11 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_21 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_1| select * from t_user_item_31 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_2 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_12 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_22 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_2| select * from t_user_item_32 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_3 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_13 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_23 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_3| select * from t_user_item_33 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_4 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_14 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_24 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_4| select * from t_user_item_34 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_5 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_15 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_25 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_5| select * from t_user_item_35 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_6 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_16 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_26 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_6| select * from t_user_item_36 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_7 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_17 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_27 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_7| select * from t_user_item_37 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_8 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_18 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_28 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_8| select * from t_user_item_38 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_9 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_19 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_29 ORDER BY
item_id ASC"/>
- <row values="encrypt_write_ds_9| select * from t_user_item_39 ORDER BY
item_id ASC"/>
+ <row values="encrypt_write_ds_0| select * from t_user_item_0"/>
+ <row values="encrypt_write_ds_0| select * from t_user_item_10"/>
+ <row values="encrypt_write_ds_0| select * from t_user_item_20"/>
+ <row values="encrypt_write_ds_0| select * from t_user_item_30"/>
+ <row values="encrypt_write_ds_1| select * from t_user_item_1"/>
+ <row values="encrypt_write_ds_1| select * from t_user_item_11"/>
+ <row values="encrypt_write_ds_1| select * from t_user_item_21"/>
+ <row values="encrypt_write_ds_1| select * from t_user_item_31"/>
+ <row values="encrypt_write_ds_2| select * from t_user_item_2"/>
+ <row values="encrypt_write_ds_2| select * from t_user_item_12"/>
+ <row values="encrypt_write_ds_2| select * from t_user_item_22"/>
+ <row values="encrypt_write_ds_2| select * from t_user_item_32"/>
+ <row values="encrypt_write_ds_3| select * from t_user_item_3"/>
+ <row values="encrypt_write_ds_3| select * from t_user_item_13"/>
+ <row values="encrypt_write_ds_3| select * from t_user_item_23"/>
+ <row values="encrypt_write_ds_3| select * from t_user_item_33"/>
+ <row values="encrypt_write_ds_4| select * from t_user_item_4"/>
+ <row values="encrypt_write_ds_4| select * from t_user_item_14"/>
+ <row values="encrypt_write_ds_4| select * from t_user_item_24"/>
+ <row values="encrypt_write_ds_4| select * from t_user_item_34"/>
+ <row values="encrypt_write_ds_5| select * from t_user_item_5"/>
+ <row values="encrypt_write_ds_5| select * from t_user_item_15"/>
+ <row values="encrypt_write_ds_5| select * from t_user_item_25"/>
+ <row values="encrypt_write_ds_5| select * from t_user_item_35"/>
+ <row values="encrypt_write_ds_6| select * from t_user_item_6"/>
+ <row values="encrypt_write_ds_6| select * from t_user_item_16"/>
+ <row values="encrypt_write_ds_6| select * from t_user_item_26"/>
+ <row values="encrypt_write_ds_6| select * from t_user_item_36"/>
+ <row values="encrypt_write_ds_7| select * from t_user_item_7"/>
+ <row values="encrypt_write_ds_7| select * from t_user_item_17"/>
+ <row values="encrypt_write_ds_7| select * from t_user_item_27"/>
+ <row values="encrypt_write_ds_7| select * from t_user_item_37"/>
+ <row values="encrypt_write_ds_8| select * from t_user_item_8"/>
+ <row values="encrypt_write_ds_8| select * from t_user_item_18"/>
+ <row values="encrypt_write_ds_8| select * from t_user_item_28"/>
+ <row values="encrypt_write_ds_8| select * from t_user_item_38"/>
+ <row values="encrypt_write_ds_9| select * from t_user_item_9"/>
+ <row values="encrypt_write_ds_9| select * from t_user_item_19"/>
+ <row values="encrypt_write_ds_9| select * from t_user_item_29"/>
+ <row values="encrypt_write_ds_9| select * from t_user_item_39"/>
</dataset>
diff --git
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/scenario/ShardingSQLRewriterParameterizedTest.java
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/scenario/ShardingSQLRewriterParameterizedTest.java
index 566fed7..64115d9 100644
---
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/scenario/ShardingSQLRewriterParameterizedTest.java
+++
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/java/org/apache/shardingsphere/sharding/rewrite/parameterized/scenario/ShardingSQLRewriterParameterizedTest.java
@@ -38,6 +38,7 @@ import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
+import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
@@ -85,6 +86,7 @@ public final class ShardingSQLRewriterParameterizedTest
extends AbstractSQLRewri
Map<String, IndexMetaData> indexMetaDataMap = new HashMap<>(1, 1);
indexMetaDataMap.put("index_name", new IndexMetaData("index_name"));
when(accountTableMetaData.getIndexes()).thenReturn(indexMetaDataMap);
+
when(accountTableMetaData.getPrimaryKeyColumns()).thenReturn(Collections.singletonList("account_id"));
when(result.containsTable("t_account")).thenReturn(true);
when(result.get("t_account")).thenReturn(accountTableMetaData);
when(result.get("t_account_detail")).thenReturn(mock(TableMetaData.class));
diff --git
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
index 4ab8b1e..bbe5b18 100644
---
a/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
+++
b/shardingsphere-test/shardingsphere-rewrite-test/src/test/resources/scenario/sharding/case/select.xml
@@ -17,22 +17,22 @@
-->
<rewrite-assertions yaml-rule="scenario/sharding/config/sharding-rule.yaml">
- <rewrite-assertion id="select_with_sharding_value_for_parameters"
db-types="SQL92">
+ <rewrite-assertion id="select_with_sharding_value_for_parameters">
<input sql="SELECT * FROM t_account WHERE account_id = ?"
parameters="100" />
<output sql="SELECT * FROM t_account_0 WHERE account_id = ?"
parameters="100" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_sharding_value_for_literals"
db-types="SQL92">
+ <rewrite-assertion id="select_with_sharding_value_for_literals">
<input sql="SELECT * FROM t_account WHERE account_id = 100" />
<output sql="SELECT * FROM t_account_0 WHERE account_id = 100" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_right_sharding_value_for_parameters"
db-types="SQL92">
+ <rewrite-assertion id="select_with_right_sharding_value_for_parameters">
<input sql="SELECT * FROM t_account WHERE ? = account_id"
parameters="100" />
<output sql="SELECT * FROM t_account_0 WHERE ? = account_id"
parameters="100" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_right_sharding_value_for_literals"
db-types="SQL92">
+ <rewrite-assertion id="select_with_right_sharding_value_for_literals">
<input sql="SELECT * FROM t_account WHERE 100 = account_id" />
<output sql="SELECT * FROM t_account_0 WHERE 100 = account_id" />
</rewrite-assertion>
@@ -42,17 +42,17 @@
<output sql="SELECT * FROM t_account_0 a WHERE not exists (select *
from t_account_detail_0 where a.account_id=account_id and account_id=1000) and
account_id = 100" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_sum_fun" db-types="SQL92">
+ <rewrite-assertion id="select_with_sum_fun">
<input sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM
t_account WHERE account_id = 100" />
<output sql="SELECT SUM(DISTINCT account_id), SUM(account_id) FROM
t_account WHERE account_id = 100" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_avg_fun" db-types="SQL92">
+ <rewrite-assertion id="select_with_avg_fun">
<input sql="SELECT AVG(DISTINCT t.account_id), account_id FROM
t_account t WHERE account_id = 100" />
<output sql="SELECT AVG(DISTINCT t.account_id), account_id FROM
t_account_0 t WHERE account_id = 100" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_having" db-types="SQL92">
+ <rewrite-assertion id="select_with_having"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,SQL92">
<input sql="SELECT COUNT(account_id) as count_alias FROM t_account
GROUP BY amount HAVING count_alias > ?" parameters="1"/>
<output sql="SELECT COUNT(account_id) as count_alias FROM t_account
GROUP BY amount HAVING count_alias > ?" parameters="1"/>
</rewrite-assertion>
@@ -62,7 +62,7 @@
<output sql="SELECT COUNT(account_id) as count_alias FROM t_account
GROUP BY amount HAVING count_alias > ? WINDOW w AS (PARTITION BY account_id)"
parameters="1"/>
</rewrite-assertion>
- <rewrite-assertion id="select_with_schema" db-types="SQL92">
+ <rewrite-assertion id="select_with_schema">
<input sql="SELECT * FROM sharding_db.t_account" />
<output sql="SELECT * FROM t_account_0"/>
<output sql="SELECT * FROM t_account_1"/>
@@ -103,13 +103,13 @@
<output sql="SELECT * FROM t_account_0 WHERE account_id = ? AND amount
BETWEEN (SELECT amount FROM t_account_0 WHERE account_id = ?) AND ?"
parameters="100, 100, 1500"/>
</rewrite-assertion>
- <rewrite-assertion id="select_without_sharding_value_for_parameters"
db-types="SQL92">
+ <rewrite-assertion id="select_without_sharding_value_for_parameters">
<input sql="SELECT * FROM sharding_db.t_account WHERE amount = ?"
parameters="1000" />
<output sql="SELECT * FROM t_account_0 WHERE amount = ?"
parameters="1000" />
<output sql="SELECT * FROM t_account_1 WHERE amount = ?"
parameters="1000" />
</rewrite-assertion>
- <rewrite-assertion id="select_without_sharding_value_for_literals"
db-types="SQL92">
+ <rewrite-assertion id="select_without_sharding_value_for_literals">
<input sql="SELECT * FROM sharding_db.t_account WHERE amount = 1000" />
<output sql="SELECT * FROM t_account_0 WHERE amount = 1000" />
<output sql="SELECT * FROM t_account_1 WHERE amount = 1000" />
@@ -133,133 +133,133 @@
<output sql="SELECT t_account_1.amount , t_account_1.account_id AS
ORDER_BY_DERIVED_0 FROM t_account_1 order by t_account_1.account_id ASC" />
</rewrite-assertion>
- <rewrite-assertion
id="select_binding_table_with_sharding_value_for_parameters" db-types="SQL92">
+ <rewrite-assertion
id="select_binding_table_with_sharding_value_for_parameters">
<input sql="SELECT * FROM t_account o, t_account_detail i WHERE
o.account_id=i.account_id AND o.account_id = ?" parameters="100" />
<output sql="SELECT * FROM t_account_0 o, t_account_detail_0 i WHERE
o.account_id=i.account_id AND o.account_id = ?" parameters="100" />
</rewrite-assertion>
- <rewrite-assertion
id="select_binding_table_with_sharding_value_for_literals" db-types="SQL92">
+ <rewrite-assertion
id="select_binding_table_with_sharding_value_for_literals">
<input sql="SELECT * FROM t_account o, t_account_detail i WHERE
o.account_id=i.account_id AND o.account_id = 100" />
<output sql="SELECT * FROM t_account_0 o, t_account_detail_0 i WHERE
o.account_id=i.account_id AND o.account_id = 100" />
</rewrite-assertion>
- <rewrite-assertion
id="select_binding_table_without_sharding_value_for_parameters"
db-types="SQL92">
+ <rewrite-assertion
id="select_binding_table_without_sharding_value_for_parameters">
<input sql="SELECT * FROM t_account JOIN t_account_detail ON
account_id WHERE t_account.amount = ?" parameters="1000" />
<output sql="SELECT * FROM t_account_0 JOIN t_account_detail_0 ON
account_id WHERE t_account_0.amount = ?" parameters="1000" />
<output sql="SELECT * FROM t_account_1 JOIN t_account_detail_1 ON
account_id WHERE t_account_1.amount = ?" parameters="1000" />
</rewrite-assertion>
- <rewrite-assertion
id="select_binding_table_without_sharding_value_for_literals" db-types="SQL92">
+ <rewrite-assertion
id="select_binding_table_without_sharding_value_for_literals">
<input sql="SELECT * FROM t_account JOIN t_account_detail ON
account_id WHERE t_account.amount = 1000" />
<output sql="SELECT * FROM t_account_0 JOIN t_account_detail_0 ON
account_id WHERE t_account_0.amount = 1000" />
<output sql="SELECT * FROM t_account_1 JOIN t_account_detail_1 ON
account_id WHERE t_account_1.amount = 1000" />
</rewrite-assertion>
- <rewrite-assertion id="select_avg_with_single_route" db-types="SQL92">
+ <rewrite-assertion id="select_avg_with_single_route">
<input sql="SELECT AVG(amount) FROM t_account WHERE account_id = ?"
parameters="100" />
<output sql="SELECT AVG(amount) FROM t_account_0 WHERE account_id = ?"
parameters="100" />
</rewrite-assertion>
- <rewrite-assertion id="select_avg_with_multiple_route" db-types="SQL92">
+ <rewrite-assertion id="select_avg_with_multiple_route">
<input sql="SELECT AVG(amount) FROM t_account WHERE amount = ?"
parameters="1000" />
<output sql="SELECT AVG(amount) , COUNT(amount) AS AVG_DERIVED_COUNT_0
, SUM(amount) AS AVG_DERIVED_SUM_0 FROM t_account_0 WHERE amount = ?"
parameters="1000" />
<output sql="SELECT AVG(amount) , COUNT(amount) AS AVG_DERIVED_COUNT_0
, SUM(amount) AS AVG_DERIVED_SUM_0 FROM t_account_1 WHERE amount = ?"
parameters="1000" />
</rewrite-assertion>
- <rewrite-assertion id="select_distinct_with_single_route" db-types="SQL92">
+ <rewrite-assertion id="select_distinct_with_single_route">
<input sql="SELECT COUNT(DISTINCT account_id) a, SUM(DISTINCT
account_id) a FROM t_account WHERE account_id = ?" parameters="100" />
<output sql="SELECT COUNT(DISTINCT account_id) a, SUM(DISTINCT
account_id) a FROM t_account_0 WHERE account_id = ?" parameters="100" />
</rewrite-assertion>
- <rewrite-assertion id="select_distinct_with_alias_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion id="select_distinct_with_alias_with_multiple_route">
<input sql="SELECT COUNT(DISTINCT account_id) a, SUM(DISTINCT
account_id) b FROM t_account" />
<output sql="SELECT DISTINCT account_id a, account_id b FROM
t_account_0" />
<output sql="SELECT DISTINCT account_id a, account_id b FROM
t_account_1" />
</rewrite-assertion>
- <rewrite-assertion id="select_distinct_without_alias_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion id="select_distinct_without_alias_with_multiple_route">
<input sql="SELECT COUNT(DISTINCT account_id), SUM(DISTINCT
account_id) FROM t_account" />
<output sql="SELECT DISTINCT account_id AS
AGGREGATION_DISTINCT_DERIVED_0, account_id AS AGGREGATION_DISTINCT_DERIVED_1
FROM t_account_0" />
<output sql="SELECT DISTINCT account_id AS
AGGREGATION_DISTINCT_DERIVED_0, account_id AS AGGREGATION_DISTINCT_DERIVED_1
FROM t_account_1" />
</rewrite-assertion>
- <rewrite-assertion id="select_account_by_with_single_route"
db-types="SQL92">
+ <rewrite-assertion id="select_account_by_with_single_route">
<input sql="SELECT account_id FROM t_account WHERE account_id = ?
ORDER BY amount" parameters="100" />
<output sql="SELECT account_id FROM t_account_0 WHERE account_id = ?
ORDER BY amount" parameters="100" />
</rewrite-assertion>
- <rewrite-assertion
id="select_account_by_with_order_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_account_by_with_order_by_projection_with_multiple_route">
<input sql="SELECT account_id FROM t_account ORDER BY account_id" />
<output sql="SELECT account_id FROM t_account_0 ORDER BY account_id" />
<output sql="SELECT account_id FROM t_account_1 ORDER BY account_id" />
</rewrite-assertion>
- <rewrite-assertion
id="select_account_by_without_account_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_account_by_without_account_by_projection_with_multiple_route">
<input sql="SELECT account_id FROM t_account ORDER BY amount" />
<output sql="SELECT account_id , amount AS ORDER_BY_DERIVED_0 FROM
t_account_0 ORDER BY amount" />
<output sql="SELECT account_id , amount AS ORDER_BY_DERIVED_0 FROM
t_account_1 ORDER BY amount" />
</rewrite-assertion>
- <rewrite-assertion
id="select_account_by_without_unqualified_shorthand_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_account_by_without_unqualified_shorthand_with_multiple_route">
<input sql="SELECT * FROM t_account ORDER BY amount" />
<output sql="SELECT * FROM t_account_0 ORDER BY amount" />
<output sql="SELECT * FROM t_account_1 ORDER BY amount" />
</rewrite-assertion>
- <rewrite-assertion
id="select_account_by_without_qualified_shorthand_contains_order_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_account_by_without_qualified_shorthand_contains_order_by_projection_with_multiple_route">
<input sql="SELECT o.* FROM t_account o JOIN t_account_detail i ON
account_id ORDER BY o.amount" />
<output sql="SELECT o.* FROM t_account_0 o JOIN t_account_detail_0 i
ON account_id ORDER BY o.amount" />
<output sql="SELECT o.* FROM t_account_1 o JOIN t_account_detail_1 i
ON account_id ORDER BY o.amount" />
</rewrite-assertion>
- <rewrite-assertion
id="select_account_by_without_qualified_shorthand_not_contains_order_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_account_by_without_qualified_shorthand_not_contains_order_by_projection_with_multiple_route">
<input sql="SELECT o.* FROM t_account o JOIN t_account_detail i ON
account_id ORDER BY i.amount" />
<output sql="SELECT o.* , i.amount AS ORDER_BY_DERIVED_0 FROM
t_account_0 o JOIN t_account_detail_0 i ON account_id ORDER BY i.amount" />
<output sql="SELECT o.* , i.amount AS ORDER_BY_DERIVED_0 FROM
t_account_1 o JOIN t_account_detail_1 i ON account_id ORDER BY i.amount" />
</rewrite-assertion>
- <rewrite-assertion id="select_group_by_with_single_route" db-types="SQL92">
+ <rewrite-assertion id="select_group_by_with_single_route">
<input sql="SELECT account_id FROM t_account WHERE account_id = ?
GROUP BY amount" parameters="100" />
<output sql="SELECT account_id FROM t_account_0 WHERE account_id = ?
GROUP BY amount" parameters="100" />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_with_group_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_with_group_by_projection_with_multiple_route">
<input sql="SELECT account_id FROM t_account GROUP BY account_id" />
<output sql="SELECT account_id FROM t_account_0 GROUP BY account_id
ORDER BY account_id ASC " />
<output sql="SELECT account_id FROM t_account_1 GROUP BY account_id
ORDER BY account_id ASC " />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_without_group_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_without_group_by_projection_with_multiple_route">
<input sql="SELECT account_id FROM t_account GROUP BY amount" />
<output sql="SELECT account_id , amount AS GROUP_BY_DERIVED_0 FROM
t_account_0 GROUP BY amount ORDER BY amount ASC " />
<output sql="SELECT account_id , amount AS GROUP_BY_DERIVED_0 FROM
t_account_1 GROUP BY amount ORDER BY amount ASC " />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_without_unqualified_shorthand_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_without_unqualified_shorthand_with_multiple_route">
<input sql="SELECT * FROM t_account GROUP BY amount" />
<output sql="SELECT * FROM t_account_0 GROUP BY amount ORDER BY amount
ASC " />
<output sql="SELECT * FROM t_account_1 GROUP BY amount ORDER BY amount
ASC " />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_without_qualified_shorthand_contains_group_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_without_qualified_shorthand_contains_group_by_projection_with_multiple_route">
<input sql="SELECT o.* FROM t_account o JOIN t_account_detail i ON
account_id GROUP BY o.amount" />
<output sql="SELECT o.* FROM t_account_0 o JOIN t_account_detail_0 i
ON account_id GROUP BY o.amount ORDER BY o.amount ASC " />
<output sql="SELECT o.* FROM t_account_1 o JOIN t_account_detail_1 i
ON account_id GROUP BY o.amount ORDER BY o.amount ASC " />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_without_qualified_shorthand_not_contains_group_by_projection_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_without_qualified_shorthand_not_contains_group_by_projection_with_multiple_route">
<input sql="SELECT o.* FROM t_account o JOIN t_account_detail i ON
account_id GROUP BY i.amount" />
<output sql="SELECT o.* , i.amount AS GROUP_BY_DERIVED_0 FROM
t_account_0 o JOIN t_account_detail_0 i ON account_id GROUP BY i.amount ORDER
BY i.amount ASC " />
<output sql="SELECT o.* , i.amount AS GROUP_BY_DERIVED_0 FROM
t_account_1 o JOIN t_account_detail_1 i ON account_id GROUP BY i.amount ORDER
BY i.amount ASC " />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_and_order_by_with_different_item_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_and_order_by_with_different_item_with_multiple_route">
<input sql="SELECT status FROM t_account GROUP BY amount ORDER BY
account_id" />
<output sql="SELECT status , amount AS GROUP_BY_DERIVED_0 , account_id
AS ORDER_BY_DERIVED_0 FROM t_account_0 GROUP BY amount ORDER BY account_id" />
<output sql="SELECT status , amount AS GROUP_BY_DERIVED_0 , account_id
AS ORDER_BY_DERIVED_0 FROM t_account_1 GROUP BY amount ORDER BY account_id" />
</rewrite-assertion>
- <rewrite-assertion
id="select_group_by_and_order_by_with_different_direction_with_multiple_route"
db-types="SQL92">
+ <rewrite-assertion
id="select_group_by_and_order_by_with_different_direction_with_multiple_route">
<input sql="SELECT status FROM t_account GROUP BY account_id ORDER BY
account_id DESC" />
<output sql="SELECT status , account_id AS GROUP_BY_DERIVED_0 FROM
t_account_0 GROUP BY account_id ORDER BY account_id DESC" />
<output sql="SELECT status , account_id AS GROUP_BY_DERIVED_0 FROM
t_account_1 GROUP BY account_id ORDER BY account_id DESC" />
@@ -461,27 +461,27 @@
<output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 0" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_table_as_qualified_name"
db-types="SQL92">
+ <rewrite-assertion id="select_with_table_as_qualified_name">
<input sql="SELECT t_account.account_id, t_account.password,
t_account.amount AS a, status AS s FROM t_account WHERE t_account.account_id =
1 AND t_account.password = 'aaa' AND t_account.amount = 1000 AND
t_account.status = 'OK'" />
<output sql="SELECT t_account_1.account_id, t_account_1.password,
t_account_1.amount AS a, status AS s FROM t_account_1 WHERE
t_account_1.account_id = 1 AND t_account_1.password = 'aaa' AND
t_account_1.amount = 1000 AND t_account_1.status = 'OK'" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_join_table_with_qualified_columns"
db-types="SQL92">
+ <rewrite-assertion id="select_with_join_table_with_qualified_columns">
<input sql="SELECT * FROM t_account, t_account_detail WHERE
t_account.account_id = 1 AND t_account.account_id =
t_account_detail.account_id" />
<output sql="SELECT * FROM t_account_1, t_account_detail_1 WHERE
t_account_1.account_id = 1 AND t_account_1.account_id =
t_account_detail_1.account_id" />
</rewrite-assertion>
- <rewrite-assertion
id="select_with_join_table_on_condition_with_qualified_columns"
db-types="SQL92">
+ <rewrite-assertion
id="select_with_join_table_on_condition_with_qualified_columns"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,SQL92">
<input sql="SELECT * FROM t_account join t_account_detail on
t_account.account_id = t_account_detail.account_id and t_account.account_id =
?" parameters="1"/>
<output sql="SELECT * FROM t_account_1 join t_account_detail_1 on
t_account_1.account_id = t_account_detail_1.account_id and
t_account_1.account_id = ?" parameters="1"/>
</rewrite-assertion>
- <rewrite-assertion
id="select_with_join_table_where_condition_with_qualified_columns"
db-types="SQL92">
+ <rewrite-assertion
id="select_with_join_table_where_condition_with_qualified_columns">
<input sql="SELECT * FROM t_account join t_account_detail on
t_account.account_id = t_account_detail.account_id where t_account.account_id =
?" parameters="1"/>
<output sql="SELECT * FROM t_account_1 join t_account_detail_1 on
t_account_1.account_id = t_account_detail_1.account_id where
t_account_1.account_id = ?" parameters="1"/>
</rewrite-assertion>
- <rewrite-assertion
id="select_with_join_table_on_in_condition_with_qualified_columns"
db-types="SQL92">
+ <rewrite-assertion
id="select_with_join_table_on_in_condition_with_qualified_columns"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,SQL92">
<input sql="SELECT * FROM t_account join t_account_detail on
t_account.account_id = t_account_detail.account_id and t_account.account_id in
( ? )" parameters="1"/>
<output sql="SELECT * FROM t_account_1 join t_account_detail_1 on
t_account_1.account_id = t_account_detail_1.account_id and
t_account_1.account_id in ( ? )" parameters="1"/>
</rewrite-assertion>
@@ -491,28 +491,28 @@
<output sql="SELECT id FROM t_single UNION SELECT id FROM
t_single_extend"/>
</rewrite-assertion>
- <rewrite-assertion
id="select_with_join_table_with_different_sharding_columns" db-types="SQL92">
+ <rewrite-assertion
id="select_with_join_table_with_different_sharding_columns">
<input sql="SELECT * FROM t_user JOIN t_user_extend ON t_user.id =
t_user_extend.user_id WHERE t_user_extend.user_id = ?" parameters="1"/>
<output sql="SELECT * FROM t_user_1 JOIN t_user_extend_1 ON
t_user_1.id = t_user_extend_1.user_id WHERE t_user_extend_1.user_id = ?"
parameters="1"/>
</rewrite-assertion>
- <rewrite-assertion id="select_with_or_and_condition" db-types="SQL92">
+ <rewrite-assertion id="select_with_or_and_condition">
<input sql="SELECT * FROM t_account WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3"/>
<output sql="SELECT * FROM t_account_0 WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3"/>
<output sql="SELECT * FROM t_account_1 WHERE amount=? OR amount=? AND
account_id=?" parameters="1, 2, 3"/>
</rewrite-assertion>
- <rewrite-assertion id="select_multi_nested_subquery_with_binding_tables"
db-types="SQL92">
+ <rewrite-assertion id="select_multi_nested_subquery_with_binding_tables"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,SQL92">
<input sql="SELECT * FROM (SELECT id, content FROM t_user WHERE id = ?
AND content IN (SELECT content FROM t_user_extend WHERE user_id = ?)) AS temp"
parameters="1, 1"/>
<output sql="SELECT * FROM (SELECT id, content FROM t_user_1 WHERE id
= ? AND content IN (SELECT content FROM t_user_extend_1 WHERE user_id = ?)) AS
temp" parameters="1, 1"/>
</rewrite-assertion>
- <rewrite-assertion id="select_with_broadcast_table_and_single_table"
db-types="SQL92">
+ <rewrite-assertion id="select_with_broadcast_table_and_single_table">
<input sql="SELECT * FROM t_single s INNER JOIN t_config c ON
s.account_id = c.account_id" />
<output sql="SELECT * FROM t_single s INNER JOIN t_config c ON
s.account_id = c.account_id" />
</rewrite-assertion>
- <rewrite-assertion id="select_binding_table_with_uppercase_table_name"
db-types="SQL92">
+ <rewrite-assertion id="select_binding_table_with_uppercase_table_name">
<input sql="SELECT * FROM T_ROLE JOIN T_ROLE_ADMIN ON T_ROLE.ROLE_ID =
T_ROLE_ADMIN.ROLE_ID WHERE T_ROLE_ADMIN.ROLE_ID = ?" parameters="1000" />
<output sql="SELECT * FROM T_ROLE JOIN T_ROLE_ADMIN ON T_ROLE.ROLE_ID
= T_ROLE_ADMIN.ROLE_ID WHERE T_ROLE_ADMIN.ROLE_ID = ?" parameters="1000" />
</rewrite-assertion>
